By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,365 Members | 3,095 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,365 IT Pros & Developers. It's quick & easy.

howto use params with insertCmd for DataAdapter?

P: n/a
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
Nov 20 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
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

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.

Nov 20 '05 #2

P: n/a
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

-----Original Message-----
Hi Rich,

In the adonet group are some guys who hate this.

But to start just do this (forget the insert, the update and thedeletecommand 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 withda1

I hope this helps?

Cor

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.

.

Nov 20 '05 #3

P: n/a
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

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

-----Original Message-----
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

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.

.

Nov 20 '05 #4

P: n/a
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.

-----Original Message-----
Hi Rich,

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

If you want it sorted have a look at the dataview, that you can put betweenyour 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

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

>-----Original Message-----
>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
>>
>> 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.
>>
>
>
>.
>

.

Nov 20 '05 #5

P: n/a
Cor
Hi Rich,

Yes Imports System.OleDb

Yes or just place OleDb. before it.

Cor
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.

Nov 20 '05 #6

P: n/a
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

-----Original Message-----
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 rightnow.

-----Original Message-----
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

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 Iwant to insert in the new record?

Thanks again,
Rich
>-----Original Message-----
>Hi Rich,
>
>In the adonet group are some guys who hate this.
>
>But to start just do this (forget the insert, theupdate 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 cangive errors.
>
>You can probably forgot that da and replace it in the
commandbuilder with
>da1
>
>I hope this helps?
>
>Cor
>>
>> Just starting out with .net. I have a vb.net appwhere I
>> connect to an Access mdb. I use a connectioncomponent >> from the tool box (conn1), a data adapter componentfrom >> the toolbox (da1) and create a typed dataset (ds1)and >> populate a grid on a form (grd1). This all worksfine 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 havethe >> value in txt1, parm2 has value for txt2, parm3 fortxt3. >> 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 Iclick to >> carry out the insert operation. I am not clear onthe >> 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.
>>
>
>
>.
>

.

.

Nov 20 '05 #7

P: n/a
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

-----Original Message-----
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 andI can see data from the Access mdb in the grid. But now Iwant 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 anda "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
.

Nov 20 '05 #8

P: n/a
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

-----Original Message-----
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

-----Original Message-----
Greetings,

Just starting out with .net. I have a vb.net app where Iconnect 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" forparm1? 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
.

.

Nov 20 '05 #9

P: n/a
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

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

Nov 20 '05 #10

P: n/a
Did you ever get this to work? If so how? I am having a similiar problem.
Nov 20 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.