howto use params with insertCmd for DataAdapter?

[Rich]

Greetings,

Just starting out with .net. I have a vb.net app where I
connect to an Access mdb. I use a connection component
from the tool box (conn1), a data adapter component from
the toolbox (da1) and create a typed dataset (ds1) and
populate a grid on a form (grd1). This all works fine and
I can see data from the Access mdb in the grid. But now I
want to add a record to the mdb.

In the data adapter da1 I see in the property sheet a
select command (selcmd1), insert command (insCmd1) and
delete command. For insCmd1 I set the connection to
conn1. Then I want to set the parameters. I have 3
fields in the Access table, fld1 (text), fld2 (number),
fld3 (date). In the Paramters dialog box of da1 for
insCmd1 I see a "Source Column" field, a "Value" field and
a "Parameter Name" field. On my vb form I have 3
textboxes, txt1, txt2, txt3. I want parm1 to have the
value in txt1, parm2 has value for txt2, parm3 for txt3.
Question: what do I put in the "Source Column field" for
parm1? How do I tell it to retrieve the value of
txt1.text? and so on.

Then I have a button (btn1) on the form which I click to
carry out the insert operation. I am not clear on the
syntax for carrying this out. Do I still have to say
insCmd1.Paramters.Add(txt1)
....
da1.SelectCmd = insCmd1
da1.InsertCommand = insCmd1.something

Well, this is what I tried without any success. Any
advice how to do this would be greatly appreciated.

Thanks,
Rich

 

[Cor]

Hi Rich,

In the adonet group are some guys who hate this.

But to start just do this (forget the insert, the update and the
deletecommand a while)

DirectCast(BindingContext(ds.tables(0)), CurrencyManager).EndCurrentEdit()
Dim da As New OleDbDataAdapter("OriginalSelectString",connection)
Dim cb As New OleDbCommandBuilder(da)
If ds.HasChanges Then
da.Update(ds)
End If

With difficult select strings the commandbuilder can give errors.

You can probably forgot that da and replace it in the commandbuilder with
da1

I hope this helps?

Cor

 

[Rich]

Hi Cor,

Thanks for your reply. Finally got vs.net2003 loaded on
my computer.

Question: My data is displayed in the grid control. The
textboxes on the form are unbound textboxes. Should I
bind the textboxes to the dataset? If not, how do I
reference the textboxes, as they are the parameters I want
to insert in the new record?

Thanks again,
Rich

 

[Cor]

Hi Rich,

Normaly you do not have to do nothing to insert a new row, just start typing
at the bottom.

If you want it sorted have a look at the dataview, that you can put between
your dataset and the datagrid.

Something as dirty typed of course.
\\\
dim dv as new dataview(ds.tables(0))
dv.sort = "mycolumname"
datagrid.datasource = dv
///
Cor

 

[Rich]

OK. I try that. But I have one other problem. When I
type

Dim cb As New OleDBCommandBuilder(...)

it says that OleDBCommandbuilder is not defined. Do I
need an Imports statement? I am checking help files right
now.

 

[Cor]

Hi Rich,

Yes Imports System.OleDb

Yes or just place OleDb. before it.

Cor

 

[Rich]

OK. I figured this one out so far

Imports System.Data.OleDB

Man, I feel like such a helpless child right now (hate it).
Well, now I will try your code.

Rich

 

[Rich]

Hello,

Cor, if you are still reading this thread, I decided to
try something simpler because I could not understand your
last explanation.

Private Sub btn1_click(...)
Dim cmd As New OleDBCommand("Insert Into tbl1(fld1) Values
('test')")
da1.InsertCommand = cmd
da1.Update(ds1)
End Sub

But this does not work either. Nothing happens. No
error, nothing. May I ask if you could explain how to
make the OleDBCommand object perform the insert operation?

Thanks,
Rich

 

[Rich]

OK. I figured this out (sort of)

Private Sub btn1(...)
Dim cb As New OleDbCommandBuilder(da1)
Dim dRow As DataRow = ds1.Tables(0).NewRow
dRow("fld1") = "test"
ds1.Tables(0).Rows.Add(dRow)
da1.Update(ds1)
End Sub

 

[Cor]

Hi Rich,

This should work, but this is direct in the dataset, not using the grid,
My thougth is take the first sample again and do not forget that
endcurentedit before the update.
(Did you check your datagrid is not read only or something, normaly you can
edit direct with the you code and the code I did supply you)

That "has changes" is a nice command.
1. to debug you see if all worked fine
2. it does only a trip to your database if there are changes (and access is
not the fastest)

Cor

 

[Guest]

Did you ever get this to work? If so how? I am having a similiar problem.