Concurrency violation while row exists

[ChoKamir]

Hey Guys,

 

I'm having a weird problem. I wrote some simple application in c# which shows the data of a acces database in a datagridview. On the RowChanged and RowDeleted events of the dataset i do a dataAdapter.update(dataset). It works all fine, i can add new rows, change and delete excisting rows. All no problem.

The problem starts when i add a new row and then try to delete that newly added row. I know that it executes the insert statement and finishes it succesfully, so the row excists in the database aswell as in the datagridview. But when i then try to delete i get the DBConcurrencyException telling me: Concurrency violation: the DeleteCommand affected 0 of the expected 1 records.

The weird thing is that when i first select another row and then return to the new row and delete it no problem. So my feeling is that it somehow didn't completly wrote the new row to the database but only to the memory or something like that. Is that possible? how can i check that? and how can i prevent it? This problem happens about 90% of the time.

 

I hope somebody can help out.

 

Greetings

ChoKamir

[Puiu]

Did you try to put a dataset.AcceptChanges after the update?

[ChoKamir]

Did you try to put a dataset.AcceptChanges after the update?

Thanks for thinking with me guys. I tried but doesn't help. It gets in a neverending loop when the action commit is triggered. If i filter on that the error still comes up.

I added some debuging lines to track the whole process, maybe it helps. This is the sourcecode of the events to track what is happening.

           void dataAdapter_RowUpdated(object sender, OleDbRowUpdatedEventArgs e)
           {
               
               System.Diagnostics.Debug.Print("### UPDATED("+ e.RecordsAffected + ") ###: " + e.Command.CommandText + " (" + e.Command.Parameters[0].Value + ")");
           }

           void dataAdapter_RowUpdating(object sender, OleDbRowUpdatingEventArgs e)
           {
               System.Diagnostics.Debug.Print("### UPDATING ###: " + e.Command.CommandText + " (" + e.Command.Parameters[0].Value + ")");
           }


           /************************************
            * Makes sure that the database is updated after a row is deleted
            ***********************************/
           void DatabaseTable_RowDeleted(object sender, DataRowChangeEventArgs e)
           {
               System.Diagnostics.Debug.Print("(" + this.table + ") Row Deleted: " + e.Action.ToString());
               try
               {
                   dataAdapter.Update(this);
                   this.AcceptChanges();
               }
               catch (OleDbException ex) {
                   System.Windows.Forms.MessageBox.Show(ex.Message);
               }
               catch (DBConcurrencyException ex) {
                   System.Diagnostics.Debug.Print("### ERROR ###: " + ex.Message);
               }
           }

           /************************************
            * Makes sure that the database is updated after a row is changed
            ***********************************/
           void DatabaseTable_RowChanged(object sender, DataRowChangeEventArgs e)
           {
               System.Diagnostics.Debug.Print("(" + this.table + ") Row Changed: " + e.Action.ToString());
               //if (e.Action == DataRowAction.Add || e.Action == DataRowAction.Change)
               {

                   //System.Diagnostics.Debug.Print("(" + this.table + ") Row Changed: " + e.Row.ItemArray[0].ToString() + "-" + e.Row.ItemArray[1].ToString());
                   try
                   {
                       dataAdapter.Update(this);
                       if (e.Action != DataRowAction.Commit) this.AcceptChanges();
                   }
                   catch (OleDbException ex)
                   {
                       System.Windows.Forms.MessageBox.Show(ex.Message);
                   }
               }
           }

The debug output when the deletion is succesfull

() Row Changed: Add
### UPDATING ###: INSERT INTO tbl_dagen (dag) VALUES (?) (sdfasd)
() Row Changed: Commit
### UPDATED(1) ###: INSERT INTO tbl_dagen (dag) VALUES (?) (sdfasd)
() Row Deleted: Delete
### UPDATING ###: DELETE FROM tbl_dagen WHERE id = ? (64)
### UPDATED(1) ###: DELETE FROM tbl_dagen WHERE id = ? (64)
() Row Changed: Commit

The output with the error

() Row Changed: Add
### UPDATING ###: INSERT INTO tbl_dagen (dag) VALUES (?) (sdfa)
() Row Changed: Commit
### UPDATED(1) ###: INSERT INTO tbl_dagen (dag) VALUES (?) (sdfa)
() Row Deleted: Delete
### UPDATING ###: DELETE FROM tbl_dagen WHERE id = ? (66)
### UPDATED(0) ###: DELETE FROM tbl_dagen WHERE id = ? (66)
A first chance exception of type 'System.Data.DBConcurrencyException' occurred in System.Data.dll
### ERROR ###: Concurrency violation: the DeleteCommand affected 0 of the expected 1 records.

Thanks alot for helping, it really got me puzzled. I reckon it has something to do with how the ADO.NET functions in the background.

Thanks,

ChoKamir