|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
why is dataAdapter.UpdateCommand not updating?dim tblx As New DataTable da.Fill(tblx) '--works OK up to this point da.UpdateCommand = New SqlCommand da.UpdateCommand.Connection = conn da.UpdateCommand.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.Text cmd.CommandText = "Update tbl1 set fld1 = 'test' where ID = 1" conn.Open cmd.ExecuteNonQuery cmd.Close This does update tbl1 correctly. But I need to use the dataAdapter. What do I need to do to the dataAdapter.UpdateCommand code to make that update correctly? Thanks, Rich 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.UpdateCommand = New SqlCommand da.UpdateCommand.Connection = conn da.UpdateCommand.CommandText = "Update tbl1 set fld1 = @p1 where ID =@p0" da.UpdateCommand.Parameters.Add(New SqlParameter("@p0, SqlDBtype.Int, 4, "ID") da.UpdateCommand.Parameters.Add(New SqlParameter("@p1, SqlDBtype.varchar, 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. Show quoteHide quote "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.UpdateCommand = New SqlCommand > da.UpdateCommand.Connection = conn > da.UpdateCommand.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.Text > cmd.CommandText = "Update tbl1 set fld1 = 'test' where ID = 1" > conn.Open > cmd.ExecuteNonQuery > cmd.Close > > This does update tbl1 correctly. But I need to use the dataAdapter. What > do I need to do to the dataAdapter.UpdateCommand code to make that update > correctly? > > Thanks, > Rich 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?ID=3405596d-4556-4aa8-be12-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 SQLCommandBuilder(da) I hope this helps, Cor Show quoteHide quote "Rich" <R***@discussions.microsoft.com> schreef in bericht news:311942CD-A9FF-4E84-B17D-17A44A467F70@microsoft.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.UpdateCommand = New SqlCommand > da.UpdateCommand.Connection = conn > da.UpdateCommand.CommandText = "Update tbl1 set fld1 = @p1 where ID =@p0" > > da.UpdateCommand.Parameters.Add(New SqlParameter("@p0, SqlDBtype.Int, 4, > "ID") > da.UpdateCommand.Parameters.Add(New SqlParameter("@p1, SqlDBtype.varchar, > 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.UpdateCommand = New SqlCommand >> da.UpdateCommand.Connection = conn >> da.UpdateCommand.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.Text >> cmd.CommandText = "Update tbl1 set fld1 = 'test' where ID = 1" >> conn.Open >> cmd.ExecuteNonQuery >> cmd.Close >> >> This does update tbl1 correctly. But I need to use the dataAdapter. >> What >> do I need to do to the dataAdapter.UpdateCommand code to make that update >> correctly? >> >> Thanks, >> Rich Hi Cor,
Thank you for your reply. I looked at your website, but I came up with another method using dataRow.BeginEdit 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 Show quoteHide quote "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?ID=3405596d-4556-4aa8-be12-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 SQLCommandBuilder(da) > > I hope this helps, > > Cor > > "Rich" <R***@discussions.microsoft.com> schreef in bericht > news:311942CD-A9FF-4E84-B17D-17A44A467F70@microsoft.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.UpdateCommand = New SqlCommand > > da.UpdateCommand.Connection = conn > > da.UpdateCommand.CommandText = "Update tbl1 set fld1 = @p1 where ID =@p0" > > > > da.UpdateCommand.Parameters.Add(New SqlParameter("@p0, SqlDBtype.Int, 4, > > "ID") > > da.UpdateCommand.Parameters.Add(New SqlParameter("@p1, SqlDBtype.varchar, > > 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.UpdateCommand = New SqlCommand > >> da.UpdateCommand.Connection = conn > >> da.UpdateCommand.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.Text > >> cmd.CommandText = "Update tbl1 set fld1 = 'test' where ID = 1" > >> conn.Open > >> cmd.ExecuteNonQuery > >> cmd.Close > >> > >> This does update tbl1 correctly. But I need to use the dataAdapter. > >> What > >> do I need to do to the dataAdapter.UpdateCommand code to make that update > >> correctly? > >> > >> Thanks, > >> Rich > > > 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.CommandText = "UPDATE Sample SET WhatEver = @WhatEver WHERE (AutoId = @Original_AutoId) " & _ "AND (WhatEver = @Original_WhatEver OR @Original_WhatEver IS NULL AND WhatEver IS NULL); " & _ "SELECT AutoId, WhatEver FROM Sample WHERE (AutoId = @AutoId)" cmdUpdate.Connection = Conn cmdUpdate.Parameters.Add(New SqlParameter("@AutoId", SqlDbType.Int, 4, "AutoId")) cmdUpdate.Parameters.Add(New SqlParameter("@WhatEver", SqlDbType.NVarChar, 50, "WhatEver")) cmdUpdate.Parameters.Add(New SqlParameter("@Original_AutoId", SqlDbType.Int, 4, _ ParameterDirection.Input, False, nb, nb, _ "AutoId", DataRowVersion.Original, Nothing)) cmdUpdate.Parameters.Add(New SqlParameter("@Original_WhatEver", _ SqlDbType.NVarChar, 50, ParameterDirection.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("path", xmlwritemode.diffgram) I hope to makes it something more clear. Cor Show quoteHide quote "Rich" <R***@discussions.microsoft.com> schreef in bericht news:D9A668F4-418B-48F2-9EB9-4D0FD7D5ACDC@microsoft.com... > Hi Cor, > > Thank you for your reply. I looked at your website, but I came up with > another method using dataRow.BeginEdit 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?ID=3405596d-4556-4aa8-be12-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 SQLCommandBuilder(da) >> >> I hope this helps, >> >> Cor >> >> "Rich" <R***@discussions.microsoft.com> schreef in bericht >> news:311942CD-A9FF-4E84-B17D-17A44A467F70@microsoft.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.UpdateCommand = New SqlCommand >> > da.UpdateCommand.Connection = conn >> > da.UpdateCommand.CommandText = "Update tbl1 set fld1 = @p1 where ID >> > =@p0" >> > >> > da.UpdateCommand.Parameters.Add(New SqlParameter("@p0, SqlDBtype.Int, >> > 4, >> > "ID") >> > da.UpdateCommand.Parameters.Add(New SqlParameter("@p1, >> > SqlDBtype.varchar, >> > 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.UpdateCommand = New SqlCommand >> >> da.UpdateCommand.Connection = conn >> >> da.UpdateCommand.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.Text >> >> cmd.CommandText = "Update tbl1 set fld1 = 'test' where ID = 1" >> >> conn.Open >> >> cmd.ExecuteNonQuery >> >> cmd.Close >> >> >> >> This does update tbl1 correctly. But I need to use the dataAdapter. >> >> What >> >> do I need to do to the dataAdapter.UpdateCommand code to make that >> >> update >> >> correctly? >> >> >> >> Thanks, >> >> Rich >> >> >> Thank you. This is a little bit more clear. It appears that the concurrency
check comes from the commandtext cmdUpdate.CommandText = "UPDATE Sample SET WhatEver = @WhatEver WHERE (AutoId = @Original_AutoId) AND (WhatEver = @Original_WhatEver OR @Original_WhatEver 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? Show quoteHide quote "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.CommandText = "UPDATE Sample SET WhatEver = @WhatEver > WHERE (AutoId = @Original_AutoId) " & _ > "AND (WhatEver = @Original_WhatEver OR @Original_WhatEver IS NULL > AND WhatEver IS NULL); " & _ > "SELECT AutoId, WhatEver FROM Sample WHERE (AutoId = @AutoId)" > cmdUpdate.Connection = Conn > cmdUpdate.Parameters.Add(New SqlParameter("@AutoId", SqlDbType.Int, > 4, "AutoId")) > cmdUpdate.Parameters.Add(New SqlParameter("@WhatEver", > SqlDbType.NVarChar, 50, "WhatEver")) > cmdUpdate.Parameters.Add(New SqlParameter("@Original_AutoId", > SqlDbType.Int, 4, _ > ParameterDirection.Input, False, nb, nb, _ > "AutoId", DataRowVersion.Original, Nothing)) > cmdUpdate.Parameters.Add(New SqlParameter("@Original_WhatEver", _ > SqlDbType.NVarChar, 50, ParameterDirection.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("path", xmlwritemode.diffgram) > > I hope to makes it something more clear. > > Cor > > > "Rich" <R***@discussions.microsoft.com> schreef in bericht > news:D9A668F4-418B-48F2-9EB9-4D0FD7D5ACDC@microsoft.com... > > Hi Cor, > > > > Thank you for your reply. I looked at your website, but I came up with > > another method using dataRow.BeginEdit 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?ID=3405596d-4556-4aa8-be12-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 SQLCommandBuilder(da) > >> > >> I hope this helps, > >> > >> Cor > >> > >> "Rich" <R***@discussions.microsoft.com> schreef in bericht > >> news:311942CD-A9FF-4E84-B17D-17A44A467F70@microsoft.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.UpdateCommand = New SqlCommand > >> > da.UpdateCommand.Connection = conn > >> > da.UpdateCommand.CommandText = "Update tbl1 set fld1 = @p1 where ID > >> > =@p0" > >> > > >> > da.UpdateCommand.Parameters.Add(New SqlParameter("@p0, SqlDBtype.Int, > >> > 4, > >> > "ID") > >> > da.UpdateCommand.Parameters.Add(New SqlParameter("@p1, > >> > SqlDBtype.varchar, > >> > 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.UpdateCommand = New SqlCommand > >> >> da.UpdateCommand.Connection = conn > >> >> da.UpdateCommand.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.Text > >> >> cmd.CommandText = "Update tbl1 set fld1 = 'test' where ID = 1" > >> >> conn.Open > >> >> cmd.ExecuteNonQuery > >> >> cmd.Close > >> >> > >> >> This does update tbl1 correctly. But I need to use the dataAdapter. > >> >> What > >> >> do I need to do to the dataAdapter.UpdateCommand code to make that > >> >> update > >> >> correctly? > >> >> > >> >> Thanks, > >> >> Rich > >> > >> > >> > > > 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 Show quoteHide quote "Rich" <R***@discussions.microsoft.com> schreef in bericht news:42373845-A52C-4ADB-8C20-8143C1DECC87@microsoft.com... > Thank you. This is a little bit more clear. It appears that the > concurrency > check comes from the commandtext > > cmdUpdate.CommandText = "UPDATE Sample SET WhatEver = @WhatEver > WHERE (AutoId = @Original_AutoId) AND (WhatEver = @Original_WhatEver OR > @Original_WhatEver 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.CommandText = "UPDATE Sample SET WhatEver = @WhatEver >> WHERE (AutoId = @Original_AutoId) " & _ >> "AND (WhatEver = @Original_WhatEver OR @Original_WhatEver IS NULL >> AND WhatEver IS NULL); " & _ >> "SELECT AutoId, WhatEver FROM Sample WHERE (AutoId = @AutoId)" >> cmdUpdate.Connection = Conn >> cmdUpdate.Parameters.Add(New SqlParameter("@AutoId", >> SqlDbType.Int, >> 4, "AutoId")) >> cmdUpdate.Parameters.Add(New SqlParameter("@WhatEver", >> SqlDbType.NVarChar, 50, "WhatEver")) >> cmdUpdate.Parameters.Add(New SqlParameter("@Original_AutoId", >> SqlDbType.Int, 4, _ >> ParameterDirection.Input, False, nb, nb, _ >> "AutoId", DataRowVersion.Original, Nothing)) >> cmdUpdate.Parameters.Add(New SqlParameter("@Original_WhatEver", _ >> SqlDbType.NVarChar, 50, ParameterDirection.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("path", xmlwritemode.diffgram) >> >> I hope to makes it something more clear. >> >> Cor >> >> >> "Rich" <R***@discussions.microsoft.com> schreef in bericht >> news:D9A668F4-418B-48F2-9EB9-4D0FD7D5ACDC@microsoft.com... >> > Hi Cor, >> > >> > Thank you for your reply. I looked at your website, but I came up with >> > another method using dataRow.BeginEdit 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?ID=3405596d-4556-4aa8-be12-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 SQLCommandBuilder(da) >> >> >> >> I hope this helps, >> >> >> >> Cor >> >> >> >> "Rich" <R***@discussions.microsoft.com> schreef in bericht >> >> news:311942CD-A9FF-4E84-B17D-17A44A467F70@microsoft.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.UpdateCommand = New SqlCommand >> >> > da.UpdateCommand.Connection = conn >> >> > da.UpdateCommand.CommandText = "Update tbl1 set fld1 = @p1 where ID >> >> > =@p0" >> >> > >> >> > da.UpdateCommand.Parameters.Add(New SqlParameter("@p0, >> >> > SqlDBtype.Int, >> >> > 4, >> >> > "ID") >> >> > da.UpdateCommand.Parameters.Add(New SqlParameter("@p1, >> >> > SqlDBtype.varchar, >> >> > 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.UpdateCommand = New SqlCommand >> >> >> da.UpdateCommand.Connection = conn >> >> >> da.UpdateCommand.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.Text >> >> >> cmd.CommandText = "Update tbl1 set fld1 = 'test' where ID = 1" >> >> >> conn.Open >> >> >> cmd.ExecuteNonQuery >> >> >> cmd.Close >> >> >> >> >> >> This does update tbl1 correctly. But I need to use the >> >> >> dataAdapter. >> >> >> What >> >> >> do I need to do to the dataAdapter.UpdateCommand code to make that >> >> >> update >> >> >> correctly? >> >> >> >> >> >> Thanks, >> >> >> Rich >> >> >> >> >> >> >> >> >> 
Other interesting topics
Close form if no user action including mouse move over form.
Can't create AutoFiltered Excel worksheet using VB.NET Accessing inherited variables Textbox Lines Limit How to call BeginInvoke for a sub that has a parameter ? Define an object??? Use a System.Type in Generics? Newbie needs help with datagrid Read value of all columns from datatable together Generate Microsoft Office Document? |
|||||||||||||||||||||||
