Concurrency violation

[Dave]

I'm just starting to try and learn ADO.NET and was following along in
Murach's VB.NET/ADO.NET book samples for typed databases with unbound
controls.

When I try to execute an update with the my data adapter and the data table
as a parameter, daMembers.Update(dsMembers.Member_Listing), I get a
concurrency violation. I'm not sure how to track down the problem.

Basically I'm doing the following; open the connection, set data adapters
selectcommand text, fill the dataset, get a data row from the table, put the
data into controls on a form, change the data, put the changes into the data
row, and execute the update command.

' load dataset
cnRosterDB.Open()
daMemberList.SelectCommand.CommandText = sSelectCommand
daMemberList.Fill(dsMembers)
cnRosterDB.Close()

' get a record
drMemberRow = dsMembers.Member_Listing(0)
With drMemberRow
txtID.Text = .member_ID
If .Islast_nameNull Then
txtLName.Text = ""
Else
txtLName.Text = .last_name
End If
If .Isfirst_nameNull Then
txtFName.Text = ""
Else
txtFName.Text = .first_name
End If
end with

' put edited data into data row
With drMemberRow
.member_ID = CInt(txtID.Text)
.last_name = txtLName.Text
.first_name = txtFName.Text
end with

' try to update the database
daMemberList.Update(DsMembers.Member_Listing)

Any guidance would be appreciated,

Thanks
Dave

 

[Guest]

Dave,

find the Update command that was generated by the Wizard and see if it is
valid. To check for concurrency validation, the SQL command contains a WHERE
clause checking for each of the fields' prior value.

One way to get this error on your way is to modify the table in the database
and forget about modifying accordingly the schmema in your project.

 

[Dave]

Thanks Gilles,

Here is the update command generated by the wizard :

"UPDATE Member_Listing SET first_name = ?, last_name = ? WHERE (member_ID =
?) AND (first_name = ? OR ? IS NULL AND first_name IS NULL) AND (last_name =
? OR ? IS NULL AND last_name IS NULL) "

Dave

 

[Guest]

Dave,

the SQL command looks right except for one thing: the ? should not be there.
Instead, you should find named parameters like @OLD_VALUE.

 

[Otis Mukinfus]

Dave,

the SQL command looks right except for one thing: the ? should not be there.
Instead, you should find named parameters like @OLD_VALUE.

He doesn't say which provider he is using. Judging from the Wizard generated
code I would guess he is using the OLDEB provider which doesn't use named
parameters, so the code generated is positional and will not use named
parameters.


Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

 

[WenYuan Wang]

Hi Dave
Thanks for your post.
Have you resolved this issue before I reply to you?
If it is not, I will help you to resolve it now.

I think you must use OleDbDataAdapter because there is "?" in the update
command generated by wizard.
And from your post "Beginning question for Access OleDb" I know your
project is developed against VS 2003.
You have encountered an issue of the concurrency violation when you
following along in Murach's VB.Net/ADO.Net Book samples for typed databases
If there is anything I misunderstood, please correct me. Thanks.

At first we can track down this problem by the HasErrors property of your
DataTable.
In you case. It is "DsMembers.Member_Listing.HasErrors". Drop
"DsMembers.Member_Listing" into your "Watch" window when you debug your
program.
We can expand the "+" before DsMembers.Member_Listing and check the value
of "HasErrors". "True" means that there must be some error in this table.
And then please expand the "+ " before "Rows" and expand the "+" before
the "list".
We can found there is many list items in it. And then we should have to
expand the each item and to check whether the "HsErrors"property of this
item is true.
If it is true, please check the "RowError" property of this item. We can
get the detail information about this error and please paste this error
message in our forum. We can give you the root cause of this issue and help
you to resolve it.

Another way, we can use profile of SQL if the database connected by ado.net
is SQL database.
We can catch the update command send from ado.net to SQL by SQL profile.
I think we must can get some useful information from the update command
caught by SQL profile.
If you are not very familiar with SQL profile and not know how to trace the
connection from .net, please tell me which version of your SQL is. I will
give you the detail steps about how to use SQL profile.

If there is anything unclear, please feel free to contact me.
I'm glad to work with you.
Sincerely,
WenYuan