why is dataAdapter.UpdateCommand not updating?

[Guest]

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

 

[Guest]

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.

 

[Cor Ligthert [MVP]]

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

 

[Guest]

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]]

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

 

[Guest]

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]]

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