Automatically Generated Commands & Optimistic Concurrency

[Kay]

Hello,

I have created a dataadapter by specifying the select command and then used
the SqlCommandBuilder to automatically generate the Update, Delete and
Insert commands. I fill the dataset, close the DB connection, open the
connection again, manually change the database, and then update the dataset
and call the dataadapter update method, I was expecting it to raise a
dbconcurrrency error but none was raised. why is this as the help on the
Microsoft pages states
"The logic for generating commands automatically for UPDATE and DELETE
statements is based on optimistic concurrency. That is, records are not
locked for editing and can be modified by other users or processes at any
time. Because a record could have been modified after it was returned from
the SELECT statement, but before the UPDATE or DELETE statement is issued,
the automatically generated UPDATE or DELETE statement contains a WHERE
clause such that a row is only updated if it contains all original values
and has not been deleted from the data source. This is done to avoid new
data being overwritten. In cases where an automatically generated update
attempts to update a row that has been deleted or that does not contain the
original values found in the DataSet, the command will not affect any
records and a DBConcurrencyException will be thrown."

any help would be appreciated,
Kay.

My code is as follows:
Dim dataset As New DataSet

Dim connectString As String = "db connection string is in here"
Dim myConn As New SqlConnection(connectString)
Dim SqlSelectCommand As New SqlCommand("SELECT ID, Description FROM optFrame
WHERE (RoleAccessRevised = 1)", myConn)
Dim dataadapter As New SqlDataAdapter(SqlSelectCommand)
dataadapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

Dim cmdBuilder As New SqlCommandBuilder(dataadapter)


myConn.Open()
dataadapter.Fill(dataset, "optFrame")
myConn.Close()

'NOTE: At this point I go to sql server DB and manually change the data in
the specified row, hoping it will cause concurrency

myConn.Open()
Dim rowToUpdate As DataRow =
dataset.Tables("optFrame").Rows.Find(TextBox13.Text) 'NOTE : the row
specified in textbox13 exists and is updated
rowToUpdate.Item("Description") = TextBox14.Text

Try
dataadapter.Update(dataset, "optFrame") 'Note: concurrency is not
caught here
Catch ex As DBConcurrencyException
Label28.Text = "concurrency error"
End Try



myConn.Close()

 

[Miha Markic]

Hi Kay,

Show us the generated update command:
dataadapter.UpdateCommand.CommandText.

BTW it would be easier and faster if you create dataadapters at design
time...

 

[Kay]

Miha,

for some reason when the sqlcommand builder is used the
dataadapter.updatecommand remains at nothing but to see the generated update
statement you have to use cmdBuilder.GetUpdateCommand.CommandText and this
displays the following : "UPDATE optFrame SET Description = @p1 WHERE ( (ID
= @p2) )". I normally create the dataadapter at design time, I was just
testing this method to see if it would handle concurrency. The DB is
actually updated but the concurrency error is not raised.

Thanks,

Kay

 

[William \(Bill\) Vaughn]

I wrote an article that discusses the foibles of the Command(don't
use)Builder...
http://www.betav.com/msdn_magazine.htm

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Miha Markic]

Hi Kay,

Miha,

for some reason when the sqlcommand builder is used the
dataadapter.updatecommand remains at nothing but to see the generated update
statement you have to use cmdBuilder.GetUpdateCommand.CommandText

I see - I've never used it

and this

displays the following : "UPDATE optFrame SET Description = @p1 WHERE ( (ID
= @p2) )". I normally create the dataadapter at design time, I was just
testing this method to see if it would handle concurrency. The DB is
actually updated but the concurrency error is not raised.

That's because UPDATE is not checking the old values in WHERE condition.
It should check something like
WHERE ID=@p2 and Description=@oldDescription

As also Bill stated - don't use CommandBuilder.
Rather create adapters using wizards or by code.