Concurrency violation while row exists

Question

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

Answer 1

How do you know the database has been updated with the newly added row?

When you select another row, RowChanged event has been triggered for sure. The adapter.update() method will be called. However, when you still stay in the same row, it's possible the RowChanged event hasn't been triggered.

Answer 2

I agree with Bill.  I would also check following:

1. After INSERT, let's check in Access Database physically whether it inserted a row in it.

2. Before Delete, try to debug to see whether bound datasource has got that newly added row.

3. As you have written adapter.update in RowChanged and RowDeleted events, it should physically update database during these events only so have a peek in that.

Please let me know your findings....

Answer 3

Thanks for thinking with me guys. First of all i know for sure that the row is added to the database (eventually). Because when I add the row and close the program, open the database in Access it shows the row. Besides that i know for sure that the update methode is triggered(see debug below) and i know that the adapter processed the requests(eg the RowUpdated and RowUpdating events are triggered).

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);

}

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);

}

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. I could post all my sourcecode, but i think that would be an overkill.

 

Thanks,

ChoKamir

Answer 4

ChoKamir,

I think the reason why your code doesnt work is because the datatable's primairy key doesnt get updated.

Set the DataAdapter's updated rowsource to FirstReturnedRecord and make sure you return the correct information.

The dataadapter will then update the key field it uses when deleting the row.

Hope this helps!

Answer 5

Thank you for helping, i think we are on a lead here. What you say sounds very logic. I checked the UpdateRowSource state of the commands before changing it and it was set to Both. So that should be alright and changing it to FirstReturnedRecord did not change anything therefore. The error still appeared.

Although the insert query doesn't return any information ofcoarse. So the dataadapter does not receive the id of the newly added row. If i understand your post and the help correctly. Now my new question is: how do i make sure that the dataset receives the newly added id. Should i run the insert first and receive after that the id in the same command or is there a better way? And how do you explain the error appearing only now and then?

 

Thanks i think this is at least a step in the good direction.

 

Chokamir

PS the datagrid shows the correct id for the primary key, so is it possible that the dataset does not know it while the grid displays it?

Answer 6

Chokamir,

If you instantiate the DataAdapter using the select commandtext, it will use the CommandBuilder to generate the additional CRUD commands. Those commands do not contain any RowUpdated support.

You must add the update rowsource query to your commands manually (as far as I know). But beware the CommandBuilder will override your commandtext if you instantiate your dataadapter with a select commandtext.

What I usually do is write the commands seperately, takes a little more time.

Answer 7

There could be one other issue here that is good to be aware of.

With Access database there is a issue of delayed writes of changes that impacts how you program to Access.  Essentially each unique connection to an Access database can cache changes and flush these in a delayed fashion.  So you can run into a situation where Connection1 updates a record and Connection2 cannot see update to the record, even when Connection1 and Connection2 are in the same process.

So something to think about.  The general recommendation is to use a single global connection object inside your process when working with Access if you want to avoid this issue.  For ASP.NET this is difficult to do but for a standalone app this is not as difficult.

Answer 8

Hey Guys,

Thanks for helping. First i used the commandbuilder, but i though maybe that was the reason for the error. I wrote my own commands and thought it helped, but with no luck. About the two connection thing, it is really something to remember but is not the case in this program. I only make one connection to the database.

So i did some further debugging and found out that it are the id's of the primary column that are out of sync. When i add a new row it gets for instance id 55 in the datagridview. I delete it and it was succesfull, then i add a new row which suddenly gets the id of 57. Ofcoarse when i delete this row i get the error because when i open the database this row has a id of 56. How can i make sure that the id's of the primary column(autoNumber) are synchronized correctly with the database and the dataset?

 

At least a big leap further.

Greetings,

ChoKamir

Answer 9

I had similar situation. The solution was to force your adapter do a fill after your update
            Me.yourTableAdapter.Update(Me.yourDataSet.yourTable)
            Me.yourDataSet.youTable.AcceptChanges()
            Me..YourTableAdapter.Fill(Me.yourDataSet.yourTable)

Hope this helps.

Answer 10

In the dataset set the autonumbering step to -1 and the seed to 0.

Primairy keys in the dataset will be negative and in the database will be positive.

Make sure your dataadapter insert command contains SQL logic that returns the ID from the database. Like so "Insert into Customer(Name) values 'Nortwind';Select @@IDENTITY as ID;" and set the UpdatedRowSource of your command to First Returned record.

Hope this helps.

Answer 11

Although you probably solved your problem a long time ago, I ran into the same problem and came up with a simple fix that I'd like to share.

My form also had a master-detail format.  The master record has a primary key field, and the detail section references the master's primary key and display's all records in the detail table that match the primary key of the master.  Right?  So far so good.

The problem I had arised in the same fashion, updating and deleting records.  After MUCH fiddling around, I found out that in the Data Designer view of the data set, you should have your master table and your detail table, along with their respective table adapters attached to each table.  If you select the details table adapter and examine its properties, you will see properties like SelectCommand, UpdateCommand, DeleteCommand, and InsertCommand.  If you expand these properties you will see the command text and will be able to edit it.  The Select and Insert Commands are pretty starightforward, but the Update and Delete commands are the ones that give you problems.  Examine them if you'd like, you'll see how the designer complicated things a little too much.  I suggest changing the text to something like this (through the query designer if you prefer):

Update command:

"UPDATE    [DetailTable]
SET              [SomeField] = ?, [SomeOtherField] = ?, ..., [LastOfYourFields] = ? WHERE     ([MasterTablePrimaryKey] = ?) AND ([DetailTablePrimaryKey] = ?)"

Delete command:

"DELETE FROM [DetailTable]
WHERE     ([DetailTablePrimaryKey] = ?) AND ([MasterTablePrimaryKey] = ?)"

Note that the update command has a list of fields.  You should update all the fields visible in your datatable (and others if you'd like, I guess) EXCEPT the primary key field from the master table; I think it is changing this field that generates the exception.  Happy coding.

Answer 12

Hi people, I spent a large amount of time being very annoyed by the fact that I had to keep on writing SELECT, INSERT and UPDATE commands just so I could retrieve the new IDENTITY value for inserted rows when I did a DataAdapter.Update(DataTable dt) call. Anyway, finally I discovered that it is possible to trick the CommandBuilder objects into doing most of the work (notice the Using statement... ensures that the CommandBuilder is disposed before DataAdapter.Update). My approach is shown below - each time it is called it adds to a predefined DataSet and list of DataAdapters so you would need to modify it for your needs. This saved me soooo much writing of SQL and I hope it helps some other people.

Code Snippet

/// <summary>
/// Add a table to our config DataSet. Also sets up the primary key
/// and DataAdapters to save the config table.
/// </summary>
/// <param name="cnn">SqlConnection to the database containing config tables</param>
/// <param name="table_name">Name of the config table</param>
/// <param name="primary_key_col">Primary Key column of the config table. Should be IDENTITY.</param>
public void AddConfigTable(string table_name, string primary_key_col)
{
    SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM [" + table_name + "]", config_cnn);
    DataTable dt = new DataTable(table_name);
    da.Fill(dt);

    if (dt.Columns.Contains(primary_key_col))
    {
        dt.PrimaryKey = new DataColumn[] { dt.Columns[primary_key_col] };

        dt.Columns[primary_key_col].AutoIncrement = true;
        dt.Columns[primary_key_col].AutoIncrementSeed = -1;
        dt.Columns[primary_key_col].AutoIncrementStep = -1;
       
        config_dataset.Tables.Add(dt);

        SqlCommand cmd_insert;
        SqlCommand cmd_update;
        SqlCommand cmd_delete;
        using (SqlCommandBuilder cb = new SqlCommandBuilder(da))
        {
            cmd_insert = cb.GetInsertCommand();
            cmd_update = cb.GetUpdateCommand();
            cmd_delete = cb.GetDeleteCommand();
        }

        //Use and output parameter to return back the new Identity value for the primary key
        SqlParameter new_id = new SqlParameter("@new_id", SqlDbType.Int, 4, primary_key_col);
        new_id.Direction = ParameterDirection.Output;
       
        // Modify insert command accordingly
        cmd_insert.Parameters.Add(new_id);
        cmd_insert.CommandText = cmd_insert.CommandText + " SELECT @new_id = SCOPE_IDENTITY()";
        cmd_insert.UpdatedRowSource = UpdateRowSource.OutputParameters;

        da.InsertCommand = cmd_insert;
        da.UpdateCommand = cmd_update;
        da.DeleteCommand = cmd_delete;
        config_adapters.Add(table_name, da);
    }
    else
    {
        throw new ArgumentException("The column [" + primary_key_col + "] does not exist in the table [" + table_name + "].");
    }
}

Answer 13

Thanks CodeStealer, your solution to "do a fill ..." solved the problem for me. I encountered the violation doing aTableAdapter.Update(aDataTable) in aBindingSource_CurrentChanged() event proc. I am using SQL Server CE, SQLMetal & DataGridView. Cheers, Henk.

Answer 14

I had similar situation. The solution was to force your adapter do a fill after your update
            Me.yourTableAdapter.Update(Me.yourDataSet.yourTable)
            Me.yourDataSet.youTable.AcceptChanges()
            Me..YourTableAdapter.Fill(Me.yourDataSet.yourTable)

Hope this helps.

This workd out!! :)

Answer 15

i just made this new account and sign in to say

thank you man

you are the pro fir me these three line work like a charm for me