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"
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...
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...