473,729 Members | 2,176 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

why is dataAdapter.Upd ateCommand not updating?

Dim da As New SqlDataAdapter( "Select * from tbl1", conn)
dim tblx As New DataTable
da.Fill(tblx) '--works OK up to this point

da.UpdateComman d = New SqlCommand
da.UpdateComman d.Connection = conn
da.UpdateComman d.CommandText = "Update tbl1 set fld1 = 'test' where ID = 1"

da.Update(tblx) '--tblx/tbl1 not getting updated here.

But if I do this---then it works OK (not using dataAdapter though)

Dim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.Tex t
cmd.CommandText = "Update tbl1 set fld1 = 'test' where ID = 1"
conn.Open
cmd.ExecuteNonQ uery
cmd.Close

This does update tbl1 correctly. But I need to use the dataAdapter. What
do I need to do to the dataAdapter.Upd ateCommand code to make that update
correctly?

Thanks,
Rich
Jun 5 '06 #1
6 13998
I think I figured this out (because I finally got it to work). What I did
was to assign a datarow object to the row I wanted to update and Parameters
to the UpdateCommand and modified the commandtext to use parameters. Then I
updated the items in the datarow that I wanted to change. Then I updated the
datatable. Now I can see the update in tblx(datatable) tbl1 (sql server
table).
Dim da As New SqlDataAdapter( "Select * from tbl1", conn)
dim tblx As New DataTable
da.Fill(tblx)

da.UpdateComman d = New SqlCommand
da.UpdateComman d.Connection = conn
da.UpdateComman d.CommandText = "Update tbl1 set fld1 = @p1 where ID =@p0"

da.UpdateComman d.Parameters.Ad d(New SqlParameter("@ p0, SqlDBtype.Int, 4, "ID")
da.UpdateComman d.Parameters.Ad d(New SqlParameter("@ p1, SqlDBtype.varch ar,
10, "fld1")
Dim dr As DataRow = tblx.Rows(0)
dr("fld1") = "test"

da.Update(tblx) '--now I can see the update in tblx/tbl1
the example using cmd was only updating tbl1 on the sql server. But when I
was trying to use the Adapter - I was not updating tblx which is what updates
tbl1 on the sql server. Now I can see the update in the client app as well
as the sql server.
"Rich" wrote:
Dim da As New SqlDataAdapter( "Select * from tbl1", conn)
dim tblx As New DataTable
da.Fill(tblx) '--works OK up to this point

da.UpdateComman d = New SqlCommand
da.UpdateComman d.Connection = conn
da.UpdateComman d.CommandText = "Update tbl1 set fld1 = 'test' where ID = 1"

da.Update(tblx) '--tblx/tbl1 not getting updated here.

But if I do this---then it works OK (not using dataAdapter though)

Dim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.Tex t
cmd.CommandText = "Update tbl1 set fld1 = 'test' where ID = 1"
conn.Open
cmd.ExecuteNonQ uery
cmd.Close

This does update tbl1 correctly. But I need to use the dataAdapter. What
do I need to do to the dataAdapter.Upd ateCommand code to make that update
correctly?

Thanks,
Rich

Jun 5 '06 #2
Rich,

Your update command is incomplete, with a dataadapter you normally test if
there is no problem with the optimistic concurrency. It is done using the
SQL command. Have a look at this most simple sample on our website.

http://www.vb-tips.com/default.aspx?...2-d7c12bbb3726

If you have a simple update as this mostly the commandbuilder makes your
life much easier.

All the insert, update and delete handling for a table are made dynamicly
while using the dataadapter after doing this command.

dim cmb as new SQLCommandBuild er(da)

I hope this helps,

Cor

"Rich" <Ri**@discussio ns.microsoft.co m> schreef in bericht
news:31******** *************** ***********@mic rosoft.com...
I think I figured this out (because I finally got it to work). What I did
was to assign a datarow object to the row I wanted to update and
Parameters
to the UpdateCommand and modified the commandtext to use parameters. Then
I
updated the items in the datarow that I wanted to change. Then I updated
the
datatable. Now I can see the update in tblx(datatable) tbl1 (sql server
table).
Dim da As New SqlDataAdapter( "Select * from tbl1", conn)
dim tblx As New DataTable
da.Fill(tblx)

da.UpdateComman d = New SqlCommand
da.UpdateComman d.Connection = conn
da.UpdateComman d.CommandText = "Update tbl1 set fld1 = @p1 where ID =@p0"

da.UpdateComman d.Parameters.Ad d(New SqlParameter("@ p0, SqlDBtype.Int, 4,
"ID")
da.UpdateComman d.Parameters.Ad d(New SqlParameter("@ p1, SqlDBtype.varch ar,
10, "fld1")
Dim dr As DataRow = tblx.Rows(0)
dr("fld1") = "test"

da.Update(tblx) '--now I can see the update in tblx/tbl1
the example using cmd was only updating tbl1 on the sql server. But when
I
was trying to use the Adapter - I was not updating tblx which is what
updates
tbl1 on the sql server. Now I can see the update in the client app as
well
as the sql server.
"Rich" wrote:
Dim da As New SqlDataAdapter( "Select * from tbl1", conn)
dim tblx As New DataTable
da.Fill(tblx) '--works OK up to this point

da.UpdateComman d = New SqlCommand
da.UpdateComman d.Connection = conn
da.UpdateComman d.CommandText = "Update tbl1 set fld1 = 'test' where ID =
1"

da.Update(tblx) '--tblx/tbl1 not getting updated here.

But if I do this---then it works OK (not using dataAdapter though)

Dim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.Tex t
cmd.CommandText = "Update tbl1 set fld1 = 'test' where ID = 1"
conn.Open
cmd.ExecuteNonQ uery
cmd.Close

This does update tbl1 correctly. But I need to use the dataAdapter.
What
do I need to do to the dataAdapter.Upd ateCommand code to make that update
correctly?

Thanks,
Rich

Jun 6 '06 #3
Hi Cor,

Thank you for your reply. I looked at your website, but I came up with
another method using dataRow.BeginEd it and dataRow.EndEdit . The
UpdateCommand on my DataAdapter seems to work now.

I have been reading several columns/articles that advise against using the
commandBuilder. One of my goals for using the DataAdpater is specifically
for checking for optimistic concurrency. May I ask how this is done? I
admit that I had a little bit of trouble following the example on your site
(due to my lack of experience).

Thanks,
Rich
"Cor Ligthert [MVP]" wrote:
Rich,

Your update command is incomplete, with a dataadapter you normally test if
there is no problem with the optimistic concurrency. It is done using the
SQL command. Have a look at this most simple sample on our website.

http://www.vb-tips.com/default.aspx?...2-d7c12bbb3726

If you have a simple update as this mostly the commandbuilder makes your
life much easier.

All the insert, update and delete handling for a table are made dynamicly
while using the dataadapter after doing this command.

dim cmb as new SQLCommandBuild er(da)

I hope this helps,

Cor

"Rich" <Ri**@discussio ns.microsoft.co m> schreef in bericht
news:31******** *************** ***********@mic rosoft.com...
I think I figured this out (because I finally got it to work). What I did
was to assign a datarow object to the row I wanted to update and
Parameters
to the UpdateCommand and modified the commandtext to use parameters. Then
I
updated the items in the datarow that I wanted to change. Then I updated
the
datatable. Now I can see the update in tblx(datatable) tbl1 (sql server
table).
Dim da As New SqlDataAdapter( "Select * from tbl1", conn)
dim tblx As New DataTable
da.Fill(tblx)

da.UpdateComman d = New SqlCommand
da.UpdateComman d.Connection = conn
da.UpdateComman d.CommandText = "Update tbl1 set fld1 = @p1 where ID =@p0"

da.UpdateComman d.Parameters.Ad d(New SqlParameter("@ p0, SqlDBtype.Int, 4,
"ID")
da.UpdateComman d.Parameters.Ad d(New SqlParameter("@ p1, SqlDBtype.varch ar,
10, "fld1")
Dim dr As DataRow = tblx.Rows(0)
dr("fld1") = "test"

da.Update(tblx) '--now I can see the update in tblx/tbl1
the example using cmd was only updating tbl1 on the sql server. But when
I
was trying to use the Adapter - I was not updating tblx which is what
updates
tbl1 on the sql server. Now I can see the update in the client app as
well
as the sql server.
"Rich" wrote:
Dim da As New SqlDataAdapter( "Select * from tbl1", conn)
dim tblx As New DataTable
da.Fill(tblx) '--works OK up to this point

da.UpdateComman d = New SqlCommand
da.UpdateComman d.Connection = conn
da.UpdateComman d.CommandText = "Update tbl1 set fld1 = 'test' where ID =
1"

da.Update(tblx) '--tblx/tbl1 not getting updated here.

But if I do this---then it works OK (not using dataAdapter though)

Dim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.Tex t
cmd.CommandText = "Update tbl1 set fld1 = 'test' where ID = 1"
conn.Open
cmd.ExecuteNonQ uery
cmd.Close

This does update tbl1 correctly. But I need to use the dataAdapter.
What
do I need to do to the dataAdapter.Upd ateCommand code to make that update
correctly?

Thanks,
Rich


Jun 6 '06 #4
Rich,

I have seen those messages about the commandbuilder as well. I have seen
too, that some of those who wrote that in past are now advising the
commandbuilder. In my idea is there nothing wrong with the commandbuilder
for simple updates of one table. Like the designer it can only use one not
joined table with a primary key and hold a maximum of 100 fields. I have
never seen in this newsgroups or by myself a real wrong behaviour of the
commandbuilder. This is not a guarantee of course that there cannot be
something wrong.

But for the code about concurrency checking focus your eyes only on that
update command that is in the sample on our website. It does the same with
the delete and the insert by the way.

'cmdUpdate
cmdUpdate.Comma ndText = "UPDATE Sample SET WhatEver = @WhatEver
WHERE (AutoId = @Original_AutoI d) " & _
"AND (WhatEver = @Original_WhatE ver OR @Original_WhatE ver IS NULL
AND WhatEver IS NULL); " & _
"SELECT AutoId, WhatEver FROM Sample WHERE (AutoId = @AutoId)"
cmdUpdate.Conne ction = Conn
cmdUpdate.Param eters.Add(New SqlParameter("@ AutoId", SqlDbType.Int,
4, "AutoId"))
cmdUpdate.Param eters.Add(New SqlParameter("@ WhatEver",
SqlDbType.NVarC har, 50, "WhatEver") )
cmdUpdate.Param eters.Add(New SqlParameter("@ Original_AutoId ",
SqlDbType.Int, 4, _
ParameterDirect ion.Input, False, nb, nb, _
"AutoId", DataRowVersion. Original, Nothing))
cmdUpdate.Param eters.Add(New SqlParameter("@ Original_WhatEv er", _
SqlDbType.NVarC har, 50, ParameterDirect ion.Input, False, nb, nb, _
"WhatEver", DataRowVersion. Original, Nothing))
'
What you see is that there is a Update and a Select in it.

The Select reads the database confirm the keys as are in your dataset.
The dataadapter checks if everything is still the same to the original rows
which are still in your dataset before you do acceptchanges or that the
dataadapter does that acceptchanges.
If not than there is a concurrency error.
Simple is it not?

You can see those original rows by writing an dataset with changes to disk
using

ds.writeXML("pa th", xmlwritemode.di ffgram)

I hope to makes it something more clear.

Cor
"Rich" <Ri**@discussio ns.microsoft.co m> schreef in bericht
news:D9******** *************** ***********@mic rosoft.com...
Hi Cor,

Thank you for your reply. I looked at your website, but I came up with
another method using dataRow.BeginEd it and dataRow.EndEdit . The
UpdateCommand on my DataAdapter seems to work now.

I have been reading several columns/articles that advise against using the
commandBuilder. One of my goals for using the DataAdpater is specifically
for checking for optimistic concurrency. May I ask how this is done? I
admit that I had a little bit of trouble following the example on your
site
(due to my lack of experience).

Thanks,
Rich
"Cor Ligthert [MVP]" wrote:
Rich,

Your update command is incomplete, with a dataadapter you normally test
if
there is no problem with the optimistic concurrency. It is done using the
SQL command. Have a look at this most simple sample on our website.

http://www.vb-tips.com/default.aspx?...2-d7c12bbb3726

If you have a simple update as this mostly the commandbuilder makes your
life much easier.

All the insert, update and delete handling for a table are made dynamicly
while using the dataadapter after doing this command.

dim cmb as new SQLCommandBuild er(da)

I hope this helps,

Cor

"Rich" <Ri**@discussio ns.microsoft.co m> schreef in bericht
news:31******** *************** ***********@mic rosoft.com...
>I think I figured this out (because I finally got it to work). What I
>did
> was to assign a datarow object to the row I wanted to update and
> Parameters
> to the UpdateCommand and modified the commandtext to use parameters.
> Then
> I
> updated the items in the datarow that I wanted to change. Then I
> updated
> the
> datatable. Now I can see the update in tblx(datatable) tbl1 (sql
> server
> table).
>
>
> Dim da As New SqlDataAdapter( "Select * from tbl1", conn)
> dim tblx As New DataTable
> da.Fill(tblx)
>
> da.UpdateComman d = New SqlCommand
> da.UpdateComman d.Connection = conn
> da.UpdateComman d.CommandText = "Update tbl1 set fld1 = @p1 where ID
> =@p0"
>
> da.UpdateComman d.Parameters.Ad d(New SqlParameter("@ p0, SqlDBtype.Int,
> 4,
> "ID")
> da.UpdateComman d.Parameters.Ad d(New SqlParameter("@ p1,
> SqlDBtype.varch ar,
> 10, "fld1")
> Dim dr As DataRow = tblx.Rows(0)
> dr("fld1") = "test"
>
> da.Update(tblx) '--now I can see the update in tblx/tbl1
>
>
> the example using cmd was only updating tbl1 on the sql server. But
> when
> I
> was trying to use the Adapter - I was not updating tblx which is what
> updates
> tbl1 on the sql server. Now I can see the update in the client app as
> well
> as the sql server.
>
>
> "Rich" wrote:
>
>> Dim da As New SqlDataAdapter( "Select * from tbl1", conn)
>> dim tblx As New DataTable
>> da.Fill(tblx) '--works OK up to this point
>>
>> da.UpdateComman d = New SqlCommand
>> da.UpdateComman d.Connection = conn
>> da.UpdateComman d.CommandText = "Update tbl1 set fld1 = 'test' where ID
>> =
>> 1"
>>
>> da.Update(tblx) '--tblx/tbl1 not getting updated here.
>>
>> But if I do this---then it works OK (not using dataAdapter though)
>>
>> Dim cmd As New SqlCommand
>> cmd.Connection = conn
>> cmd.CommandType = CommandType.Tex t
>> cmd.CommandText = "Update tbl1 set fld1 = 'test' where ID = 1"
>> conn.Open
>> cmd.ExecuteNonQ uery
>> cmd.Close
>>
>> This does update tbl1 correctly. But I need to use the dataAdapter.
>> What
>> do I need to do to the dataAdapter.Upd ateCommand code to make that
>> update
>> correctly?
>>
>> Thanks,
>> Rich


Jun 6 '06 #5
Thank you. This is a little bit more clear. It appears that the concurrency
check comes from the commandtext

cmdUpdate.Comma ndText = "UPDATE Sample SET WhatEver = @WhatEver
WHERE (AutoId = @Original_AutoI d) AND (WhatEver = @Original_WhatE ver OR
@Original_WhatE ver IS NULL AND WhatEver IS NULL); " & _
"SELECT AutoId, WhatEver FROM Sample WHERE (AutoId = @AutoId)"
I use a Select statement in the InsertCommand in order to retrieve
@@Identity from a sql table. May I ask what the Select statement in the
UpdateCommand performs?
"Cor Ligthert [MVP]" wrote:
Rich,

I have seen those messages about the commandbuilder as well. I have seen
too, that some of those who wrote that in past are now advising the
commandbuilder. In my idea is there nothing wrong with the commandbuilder
for simple updates of one table. Like the designer it can only use one not
joined table with a primary key and hold a maximum of 100 fields. I have
never seen in this newsgroups or by myself a real wrong behaviour of the
commandbuilder. This is not a guarantee of course that there cannot be
something wrong.

But for the code about concurrency checking focus your eyes only on that
update command that is in the sample on our website. It does the same with
the delete and the insert by the way.

'cmdUpdate
cmdUpdate.Comma ndText = "UPDATE Sample SET WhatEver = @WhatEver
WHERE (AutoId = @Original_AutoI d) " & _
"AND (WhatEver = @Original_WhatE ver OR @Original_WhatE ver IS NULL
AND WhatEver IS NULL); " & _
"SELECT AutoId, WhatEver FROM Sample WHERE (AutoId = @AutoId)"
cmdUpdate.Conne ction = Conn
cmdUpdate.Param eters.Add(New SqlParameter("@ AutoId", SqlDbType.Int,
4, "AutoId"))
cmdUpdate.Param eters.Add(New SqlParameter("@ WhatEver",
SqlDbType.NVarC har, 50, "WhatEver") )
cmdUpdate.Param eters.Add(New SqlParameter("@ Original_AutoId ",
SqlDbType.Int, 4, _
ParameterDirect ion.Input, False, nb, nb, _
"AutoId", DataRowVersion. Original, Nothing))
cmdUpdate.Param eters.Add(New SqlParameter("@ Original_WhatEv er", _
SqlDbType.NVarC har, 50, ParameterDirect ion.Input, False, nb, nb, _
"WhatEver", DataRowVersion. Original, Nothing))
'
What you see is that there is a Update and a Select in it.

The Select reads the database confirm the keys as are in your dataset.
The dataadapter checks if everything is still the same to the original rows
which are still in your dataset before you do acceptchanges or that the
dataadapter does that acceptchanges.
If not than there is a concurrency error.
Simple is it not?

You can see those original rows by writing an dataset with changes to disk
using

ds.writeXML("pa th", xmlwritemode.di ffgram)

I hope to makes it something more clear.

Cor
"Rich" <Ri**@discussio ns.microsoft.co m> schreef in bericht
news:D9******** *************** ***********@mic rosoft.com...
Hi Cor,

Thank you for your reply. I looked at your website, but I came up with
another method using dataRow.BeginEd it and dataRow.EndEdit . The
UpdateCommand on my DataAdapter seems to work now.

I have been reading several columns/articles that advise against using the
commandBuilder. One of my goals for using the DataAdpater is specifically
for checking for optimistic concurrency. May I ask how this is done? I
admit that I had a little bit of trouble following the example on your
site
(due to my lack of experience).

Thanks,
Rich
"Cor Ligthert [MVP]" wrote:
Rich,

Your update command is incomplete, with a dataadapter you normally test
if
there is no problem with the optimistic concurrency. It is done using the
SQL command. Have a look at this most simple sample on our website.

http://www.vb-tips.com/default.aspx?...2-d7c12bbb3726

If you have a simple update as this mostly the commandbuilder makes your
life much easier.

All the insert, update and delete handling for a table are made dynamicly
while using the dataadapter after doing this command.

dim cmb as new SQLCommandBuild er(da)

I hope this helps,

Cor

"Rich" <Ri**@discussio ns.microsoft.co m> schreef in bericht
news:31******** *************** ***********@mic rosoft.com...
>I think I figured this out (because I finally got it to work). What I
>did
> was to assign a datarow object to the row I wanted to update and
> Parameters
> to the UpdateCommand and modified the commandtext to use parameters.
> Then
> I
> updated the items in the datarow that I wanted to change. Then I
> updated
> the
> datatable. Now I can see the update in tblx(datatable) tbl1 (sql
> server
> table).
>
>
> Dim da As New SqlDataAdapter( "Select * from tbl1", conn)
> dim tblx As New DataTable
> da.Fill(tblx)
>
> da.UpdateComman d = New SqlCommand
> da.UpdateComman d.Connection = conn
> da.UpdateComman d.CommandText = "Update tbl1 set fld1 = @p1 where ID
> =@p0"
>
> da.UpdateComman d.Parameters.Ad d(New SqlParameter("@ p0, SqlDBtype.Int,
> 4,
> "ID")
> da.UpdateComman d.Parameters.Ad d(New SqlParameter("@ p1,
> SqlDBtype.varch ar,
> 10, "fld1")
> Dim dr As DataRow = tblx.Rows(0)
> dr("fld1") = "test"
>
> da.Update(tblx) '--now I can see the update in tblx/tbl1
>
>
> the example using cmd was only updating tbl1 on the sql server. But
> when
> I
> was trying to use the Adapter - I was not updating tblx which is what
> updates
> tbl1 on the sql server. Now I can see the update in the client app as
> well
> as the sql server.
>
>
> "Rich" wrote:
>
>> Dim da As New SqlDataAdapter( "Select * from tbl1", conn)
>> dim tblx As New DataTable
>> da.Fill(tblx) '--works OK up to this point
>>
>> da.UpdateComman d = New SqlCommand
>> da.UpdateComman d.Connection = conn
>> da.UpdateComman d.CommandText = "Update tbl1 set fld1 = 'test' where ID
>> =
>> 1"
>>
>> da.Update(tblx) '--tblx/tbl1 not getting updated here.
>>
>> But if I do this---then it works OK (not using dataAdapter though)
>>
>> Dim cmd As New SqlCommand
>> cmd.Connection = conn
>> cmd.CommandType = CommandType.Tex t
>> cmd.CommandText = "Update tbl1 set fld1 = 'test' where ID = 1"
>> conn.Open
>> cmd.ExecuteNonQ uery
>> cmd.Close
>>
>> This does update tbl1 correctly. But I need to use the dataAdapter.
>> What
>> do I need to do to the dataAdapter.Upd ateCommand code to make that
>> update
>> correctly?
>>
>> Thanks,
>> Rich


Jun 6 '06 #6
Rich,

In SQLClient with autokey the key is changed in the Dataset, what is not
done by OleDb.

May I ask what the Select statement in the UpdateCommand performs?

Get the values to check for the concurency checking

Cor

"Rich" <Ri**@discussio ns.microsoft.co m> schreef in bericht
news:42******** *************** ***********@mic rosoft.com...
Thank you. This is a little bit more clear. It appears that the
concurrency
check comes from the commandtext

cmdUpdate.Comma ndText = "UPDATE Sample SET WhatEver = @WhatEver
WHERE (AutoId = @Original_AutoI d) AND (WhatEver = @Original_WhatE ver OR
@Original_WhatE ver IS NULL AND WhatEver IS NULL); " & _
"SELECT AutoId, WhatEver FROM Sample WHERE (AutoId = @AutoId)"
I use a Select statement in the InsertCommand in order to retrieve
@@Identity from a sql table. May I ask what the Select statement in the
UpdateCommand performs?
"Cor Ligthert [MVP]" wrote:
Rich,

I have seen those messages about the commandbuilder as well. I have seen
too, that some of those who wrote that in past are now advising the
commandbuilder. In my idea is there nothing wrong with the commandbuilder
for simple updates of one table. Like the designer it can only use one
not
joined table with a primary key and hold a maximum of 100 fields. I have
never seen in this newsgroups or by myself a real wrong behaviour of the
commandbuilder. This is not a guarantee of course that there cannot be
something wrong.

But for the code about concurrency checking focus your eyes only on that
update command that is in the sample on our website. It does the same
with
the delete and the insert by the way.

'cmdUpdate
cmdUpdate.Comma ndText = "UPDATE Sample SET WhatEver = @WhatEver
WHERE (AutoId = @Original_AutoI d) " & _
"AND (WhatEver = @Original_WhatE ver OR @Original_WhatE ver IS NULL
AND WhatEver IS NULL); " & _
"SELECT AutoId, WhatEver FROM Sample WHERE (AutoId = @AutoId)"
cmdUpdate.Conne ction = Conn
cmdUpdate.Param eters.Add(New SqlParameter("@ AutoId",
SqlDbType.Int,
4, "AutoId"))
cmdUpdate.Param eters.Add(New SqlParameter("@ WhatEver",
SqlDbType.NVarC har, 50, "WhatEver") )
cmdUpdate.Param eters.Add(New SqlParameter("@ Original_AutoId ",
SqlDbType.Int, 4, _
ParameterDirect ion.Input, False, nb, nb, _
"AutoId", DataRowVersion. Original, Nothing))
cmdUpdate.Param eters.Add(New SqlParameter("@ Original_WhatEv er", _
SqlDbType.NVarC har, 50, ParameterDirect ion.Input, False, nb, nb,
_
"WhatEver", DataRowVersion. Original, Nothing))
'
What you see is that there is a Update and a Select in it.

The Select reads the database confirm the keys as are in your dataset.
The dataadapter checks if everything is still the same to the original
rows
which are still in your dataset before you do acceptchanges or that the
dataadapter does that acceptchanges.
If not than there is a concurrency error.
Simple is it not?

You can see those original rows by writing an dataset with changes to
disk
using

ds.writeXML("pa th", xmlwritemode.di ffgram)

I hope to makes it something more clear.

Cor
"Rich" <Ri**@discussio ns.microsoft.co m> schreef in bericht
news:D9******** *************** ***********@mic rosoft.com...
> Hi Cor,
>
> Thank you for your reply. I looked at your website, but I came up with
> another method using dataRow.BeginEd it and dataRow.EndEdit . The
> UpdateCommand on my DataAdapter seems to work now.
>
> I have been reading several columns/articles that advise against using
> the
> commandBuilder. One of my goals for using the DataAdpater is
> specifically
> for checking for optimistic concurrency. May I ask how this is done?
> I
> admit that I had a little bit of trouble following the example on your
> site
> (due to my lack of experience).
>
> Thanks,
> Rich
>
>
> "Cor Ligthert [MVP]" wrote:
>
>> Rich,
>>
>> Your update command is incomplete, with a dataadapter you normally
>> test
>> if
>> there is no problem with the optimistic concurrency. It is done using
>> the
>> SQL command. Have a look at this most simple sample on our website.
>>
>> http://www.vb-tips.com/default.aspx?...2-d7c12bbb3726
>>
>> If you have a simple update as this mostly the commandbuilder makes
>> your
>> life much easier.
>>
>> All the insert, update and delete handling for a table are made
>> dynamicly
>> while using the dataadapter after doing this command.
>>
>> dim cmb as new SQLCommandBuild er(da)
>>
>> I hope this helps,
>>
>> Cor
>>
>> "Rich" <Ri**@discussio ns.microsoft.co m> schreef in bericht
>> news:31******** *************** ***********@mic rosoft.com...
>> >I think I figured this out (because I finally got it to work). What
>> >I
>> >did
>> > was to assign a datarow object to the row I wanted to update and
>> > Parameters
>> > to the UpdateCommand and modified the commandtext to use parameters.
>> > Then
>> > I
>> > updated the items in the datarow that I wanted to change. Then I
>> > updated
>> > the
>> > datatable. Now I can see the update in tblx(datatable) tbl1 (sql
>> > server
>> > table).
>> >
>> >
>> > Dim da As New SqlDataAdapter( "Select * from tbl1", conn)
>> > dim tblx As New DataTable
>> > da.Fill(tblx)
>> >
>> > da.UpdateComman d = New SqlCommand
>> > da.UpdateComman d.Connection = conn
>> > da.UpdateComman d.CommandText = "Update tbl1 set fld1 = @p1 where ID
>> > =@p0"
>> >
>> > da.UpdateComman d.Parameters.Ad d(New SqlParameter("@ p0,
>> > SqlDBtype.Int,
>> > 4,
>> > "ID")
>> > da.UpdateComman d.Parameters.Ad d(New SqlParameter("@ p1,
>> > SqlDBtype.varch ar,
>> > 10, "fld1")
>> > Dim dr As DataRow = tblx.Rows(0)
>> > dr("fld1") = "test"
>> >
>> > da.Update(tblx) '--now I can see the update in tblx/tbl1
>> >
>> >
>> > the example using cmd was only updating tbl1 on the sql server. But
>> > when
>> > I
>> > was trying to use the Adapter - I was not updating tblx which is
>> > what
>> > updates
>> > tbl1 on the sql server. Now I can see the update in the client app
>> > as
>> > well
>> > as the sql server.
>> >
>> >
>> > "Rich" wrote:
>> >
>> >> Dim da As New SqlDataAdapter( "Select * from tbl1", conn)
>> >> dim tblx As New DataTable
>> >> da.Fill(tblx) '--works OK up to this point
>> >>
>> >> da.UpdateComman d = New SqlCommand
>> >> da.UpdateComman d.Connection = conn
>> >> da.UpdateComman d.CommandText = "Update tbl1 set fld1 = 'test' where
>> >> ID
>> >> =
>> >> 1"
>> >>
>> >> da.Update(tblx) '--tblx/tbl1 not getting updated here.
>> >>
>> >> But if I do this---then it works OK (not using dataAdapter though)
>> >>
>> >> Dim cmd As New SqlCommand
>> >> cmd.Connection = conn
>> >> cmd.CommandType = CommandType.Tex t
>> >> cmd.CommandText = "Update tbl1 set fld1 = 'test' where ID = 1"
>> >> conn.Open
>> >> cmd.ExecuteNonQ uery
>> >> cmd.Close
>> >>
>> >> This does update tbl1 correctly. But I need to use the
>> >> dataAdapter.
>> >> What
>> >> do I need to do to the dataAdapter.Upd ateCommand code to make that
>> >> update
>> >> correctly?
>> >>
>> >> Thanks,
>> >> Rich
>>
>>
>>


Jun 7 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1551
by: D Witherspoon | last post by:
When sending a dataset back to a dataadapter with an update command (SQL Stored Proc) i get an error saying missing paremeter for paramters that have their sourcefield a value of null. This will only happen in the following case If a field in a row in the datatable was null and was not changed, but another field was changed. The update command will run for the dataadapter and spit out an error. Why
2
1368
by: Rich | last post by:
Hello, On a form I have 3 textboxes, txt0, txt1, txt2 which contain all integers. I have also placed a connection component (conn), dataadapter component (da1) and dataset component (ds1) from the toolbox on to the form. I established a connection to an Access mdb file and can display data correctly on the form in the textboxes. The textboxes are bound to columns in the dataset. So the only code I have to populate the form is on...
13
2089
by: Doug Bell | last post by:
Hi, I thought I had this sorted this morning but it is still a problem. My application has a DataAccess Class. When it starts, it: Connects to a DB (OLE DB) If it connects it uses an OleDbCommand with an SQL String and the connection it has a DataAdapter with the command then it fills the DataSet's DataTable with the streamed data.
3
1697
by: GatorBait | last post by:
Hi All, I have a dataset that has more than one table in it so I am unable to use the CommandBuilder to automatically generate an update command so I have to do it by hand. I am a little confused on how to write such a command and was hoping someone could help me out. Basically, I have 2 tables: 'Customers' and 'Letters' that are joined by a 'Customer_ID' field. There is a field 'Printed' inside the Letters table. In my...
2
13968
by: susan.f.barrett | last post by:
Hi, Despite me being able to type the following in to SQL Server and it updating 1 row: > updatestockcategory 1093, 839 In my code, it is not updating any rows. dataSet = new DataSet();
1
4268
by: Rich | last post by:
Hello, I can update a dataset from my client app using a dataAdapter.Updatecommand when I add parameter values outside of the param declaration. But If I add the param values inline with the param delcaration, then I have to invoke the update operation twice - by leaving the updated row - returning to the row and re-invoking the update procedure. Is there something else I need to do to add param values inline with the param...
2
2896
by: BobLewiston | last post by:
I can read in an SQL table ("Person.Contact") from AdventureWorks and step through it one row at a time, but when I try to save a record, either one I'm inserting or one I'm editting, I get the following exception: Incorrect syntax near ','. Must declare scalar variable "@ContactID". Here's the code: private void btnSave_Click (object sender, EventArgs e) { DataRow row = dataTable.Rows ; ...
0
8925
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8763
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9288
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9206
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9154
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8156
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6722
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
3240
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2692
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.