Concurrency Violation

divjoy

Well-known member
Joined
Aug 25, 2013
Messages
129
Programming Experience
1-3
Hi,

I am using vb express 2010 and sql server 2008 express. I have a simple datagridview on a Windows Form. It retrieves from a standard table and updates the changes when I click the update button see code below. It all works beautifully except occasionally I get an error "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records" now where this has come up before it appears that if you change the data in the datagridivew and them attempt to delete a row or vice versa it causes this error. Similar questions on the internet suggest its something to do with a datatable adapter, I am using a data adapter da1, a data set ds with a table called "ClientNotes"

VB.NET:
[COLOR=blue][FONT=Times New Roman]Private[/FONT][/COLOR][COLOR=blue][FONT=Times New Roman]Sub[/FONT][/COLOR][COLOR=#000000][FONT=Times New Roman] SaveNotes_Click([/FONT][/COLOR][COLOR=blue][FONT=Times New Roman]ByVal[/FONT][/COLOR][COLOR=#000000][FONT=Times New Roman] sender [/FONT][/COLOR][COLOR=blue][FONT=Times New Roman]As[/FONT][/COLOR][COLOR=#000000][FONT=Times New Roman] System.[/FONT][/COLOR][COLOR=#4181c0][FONT=Times New Roman]Object[/FONT][/COLOR][COLOR=#000000][FONT=Times New Roman], [/FONT][/COLOR][COLOR=blue][FONT=Times New Roman]ByVal[/FONT][/COLOR][COLOR=#000000][FONT=Times New Roman] e [/FONT][/COLOR][COLOR=blue][FONT=Times New Roman]As[/FONT][/COLOR][COLOR=#000000][FONT=Times New Roman] System.[/FONT][/COLOR][COLOR=#4181c0][FONT=Times New Roman]EventArgs[/FONT][/COLOR][COLOR=#000000][FONT=Times New Roman]) [/FONT][/COLOR][COLOR=blue][FONT=Times New Roman]Handles[/FONT][/COLOR][COLOR=#000000][FONT=Times New Roman] ToolStripButton1.Click[/FONT][/COLOR]
[COLOR=#008000][FONT=Times New Roman]'Code to Save Changes to Client Notes in DatagridView 1[/FONT][/COLOR]
[COLOR=blue][FONT=Times New Roman]Try[/FONT][/COLOR]
[COLOR=blue][FONT=Times New Roman]Dim[/FONT][/COLOR][COLOR=#000000][FONT=Times New Roman] objCommandBuilder [/FONT][/COLOR][COLOR=blue][FONT=Times New Roman]As[/FONT][/COLOR][COLOR=blue][FONT=Times New Roman]New[/FONT][/COLOR][COLOR=#4181c0][FONT=Times New Roman]SqlCommandBuilder[/FONT][/COLOR][COLOR=#000000][FONT=Times New Roman](da1)[/FONT][/COLOR]
[COLOR=blue][FONT=Times New Roman]Me[/FONT][/COLOR][COLOR=#000000][FONT=Times New Roman].Validate()[/FONT][/COLOR]
[COLOR=blue][FONT=Times New Roman]Me[/FONT][/COLOR][COLOR=#000000][FONT=Times New Roman].bs.EndEdit()[/FONT][/COLOR]
[COLOR=#000000][FONT=Times New Roman]  da1.Update(ds, [/FONT][/COLOR][COLOR=#a11f12][FONT=Times New Roman]"ClientNotes"[/FONT][/COLOR][COLOR=#000000][FONT=Times New Roman])[/FONT][/COLOR]
[COLOR=blue][FONT=Times New Roman]Me[/FONT][/COLOR][COLOR=#000000][FONT=Times New Roman].ChildRecChanged = [/FONT][/COLOR][COLOR=blue][FONT=Times New Roman]False[/FONT][/COLOR]
[COLOR=#000000][FONT=Times New Roman]            ToolStripButton1.BackColor = [/FONT][/COLOR][COLOR=#4181c0][FONT=Times New Roman]Color[/FONT][/COLOR][COLOR=#000000][FONT=Times New Roman].PowderBlue[/FONT][/COLOR]
[COLOR=#000000][FONT=Times New Roman]            ShowCloseDialog([/FONT][/COLOR][COLOR=#a11f12][FONT=Times New Roman]"Clients Notes records successfully updated"[/FONT][/COLOR][COLOR=#000000][FONT=Times New Roman])[/FONT][/COLOR]

[COLOR=blue][FONT=Times New Roman]Catch[/FONT][/COLOR][COLOR=#000000][FONT=Times New Roman] ex [/FONT][/COLOR][COLOR=blue][FONT=Times New Roman]As[/FONT][/COLOR][COLOR=#4181c0][FONT=Times New Roman]Exception[/FONT][/COLOR]
[COLOR=#4181c0][FONT=Times New Roman]MessageBox[/FONT][/COLOR][COLOR=#000000][FONT=Times New Roman].Show(ex.Message, [/FONT][/COLOR][COLOR=#a11f12][FONT=Times New Roman]"HCS - Save Notes Alert!"[/FONT][/COLOR][COLOR=#000000][FONT=Times New Roman])[/FONT][/COLOR]
[COLOR=blue][FONT=Times New Roman]End[/FONT][/COLOR][COLOR=blue][FONT=Times New Roman]Try[/FONT][/COLOR]
[COLOR=#000000][FONT=Times New Roman]
    [/FONT][/COLOR][COLOR=blue][FONT=Times New Roman]End[/FONT][/COLOR][COLOR=blue][FONT=Times New Roman]Sub[/FONT][/COLOR][COLOR=#333333][/COLOR]


As best as I can figure its nothing to do with the sql server, table or another user, rather its that I am not updating the underlying dataset held in memory and then when I try to delete a row and save the change there is in effect 2 changes but the underlying data set only know about the most recent change....

A simple work around is to save after every change and between changes....but users need reminding of this whereas a fix is painless!

My hunch is its something to do with doing an EndEdit and Update. I could use the cell value changed event to update after each cell change although there is a delay as the database is updated. Maybe I only need to do an EndEdit after each Cell Value Change!

Anways if anyone has worked out a solution I would appreciate your help.

Thanks for all the help guys in the past and in the future...
 

Herman

Well-known member
Joined
Oct 18, 2011
Messages
882
Location
Montreal, QC, CA
Programming Experience
10+
When you modify the row, you set its RowState property to Modified, but the change hasn't been committed to the DB yet. To commit it you need to call SaveChanges. If you do another operation to the row, like deleting it, after it has been modified but before the modification has been committed, it will cause a ConcurrencyException. The solution is to check the row state before deleting it, and call .RejectChanges on it before deleting if it has been modified.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,346
Location
Sydney, Australia
Programming Experience
10+
When you modify the row, you set its RowState property to Modified, but the change hasn't been committed to the DB yet. To commit it you need to call SaveChanges. If you do another operation to the row, like deleting it, after it has been modified but before the modification has been committed, it will cause a ConcurrencyException. The solution is to check the row state before deleting it, and call .RejectChanges on it before deleting if it has been modified.

That's not really accurate. As you say, when you modify a row its RowState is set to Modified. When you call Update on a data adapter, that change is then saved to the database. The RowState remains Modified unless AcceptChanges is called. The thing is though, Update calls AcceptChanges implicitly unless you specifically tell it not to, so I doubt that that is going to be the issue. Unless you've set the AcceptChangesOnUpdate property of the adapter to False, it's not the issue.

I suspect that the issue here is auto-generated IDs. When you add a new record to a DataTable, it will auto-generate a primary key value if configured to do so. When you insert that record into the database, the database will auto-generate a PK for an identity column that may or may not be the same as that generated by the DataTable. If it's not the same and you don't pull the database's value back into your DataTable, the two will be out of sync. If you then try to delete that record you'll see the situation you describe.

So, are your DataTable and your database generating IDs out of sync? If so then I believe that you will need to do away with your command builder and create your own commands that will pull back the IDs. That's not an onerous task but I won't describe it in detail unless you can confirm that it's relevant.
 

divjoy

Well-known member
Joined
Aug 25, 2013
Messages
129
Programming Experience
1-3
Hi Jimmy, yes I think your correct and I would be very interested in knowing how to build my own commands to keep dataset synched with database.

Thanking you
 
Top Bottom