Switch View :

ScriptFree

Data Access in Client and Middle-Tier Programming

How to: Handle Concurrency Errors

You can catch the DBConcurrencyException object to assist in resolving issues arising from concurrency violations. The DBConcurrencyException object returns the data row that caused the error. For more information, see DBConcurrencyException Members.

The following example attempts to update a data source with the contents of NorthwindDataSet from within a try/catch block, if an error is raised an error message along with the first column of the offending data row is displayed.

Note
The code below is an illustration of one strategy in handling a database update error. The code assumes several things; an existing connection to a database, an existing dataset, as well as the assumption that execution of the update command will raise a concurrency violation. For more information and a complete example, see Walkthrough: Handling a Concurrency Exception.

To resolve a concurrency violation

Execute the command to update the database from within a try/catch block.
If an exception is raised, inspect the catch statement's Row property to determine what caused the violation.

Add code to resolve the error based on your application's business rules.

The following code uses a CustomersTableAdapter and NorthwindDataSet as examples of the adapter and dataset in your application.

Visual Basic

Try
    CustomersTableAdapter.Update(NorthwindDataSet)

Catch ex As DBConcurrencyException

    Dim customErrorMessage As String
    customErrorMessage = "Concurrency violation" & vbCrLf
    customErrorMessage += CType(ex.Row.Item(0), String)
    MessageBox.Show(customErrorMessage)

    ' Add business logic code to resolve the concurrency violation...

End Try

try
{
    customersTableAdapter.Update(northwindDataSet);
}
catch (DBConcurrencyException ex)
{
    string customErrorMessage;
    customErrorMessage = "Concurrency violation\n";
    customErrorMessage += ex.Row[0].ToString();

    // Add business logic code to resolve the concurrency violation...
}

try
{
    northwindDataSetCustomersTableAdapter.Update(northwindDataSet);
}
catch (DBConcurrencyException ex)
{
    String customErrorMessage;
    customErrorMessage = "Concurrency violation\n";
    customErrorMessage += ex.get_Row().get_Item(0).ToString();

    // Add business logic code to resolve the concurrency violation...
}

You are right. This is exactly the solution but it looks like a workaround to me. After my first update, if I refresh the DataSet, it works fine. Now it's clear but I find it a bit harsh to get a Dataset updating the datasource and not being able to get the autonumber from SQL. As refreshing the DataSet have consequences over the logic of an application. Since the Dataset does UPDATE, why it's not retrieving the AutoNumber itself ?

This issue is easily reproducible by adding a new row, saving (DataAdapter.Update) then modifying the same line and saving again (DataAdapter.Update)

I think that the DataSet should reorganize itself so you don't have to refill it again or there may have another more elegant way of doing so ?

MAXIMUM_MIKE

One Solution

The root cause of the problem is that you added a new record to your dataset and already ran an update. After you update it, you need to reload the dataset with the new data so that the new record no longer has a negative number in the autonumber field.

I found it is best to add the new record with an insert command if possible, and then load the dataset / table with the new records. Then you can edit it all you want and save the changes as many times as you want without getting that error.

The whole reason it uses a negative number in the autonumber field is that it does not know what the new record number will be once it is put in the database. Once it is inserted, it is no longer a new record, but your dataset does not know what number was ultimately assigned to that record until you load the dataset again.

This error may also happen if you are not using some type of unique record identifier such as autonumber. It needs something that uniquely identifies the record so that it can update the record. It does not need this if you are only inserting new records or reading from the database. It ony needs it for updates.

If you are using the try / catch method above, you could decide to either reload the dataset under the catch block of code, or give the user a message to have them manually relaod the dataset. Either way, it would be better to deal with the problem that cause the error in the first place and change how you handle adding new records.

GearWorld

Not relevant to all scenarios.

I'm doing the right thing, and I still get the Concurrency Violation. I would need to know what needs to be done to effectively avoid it.
This documentation deson't help tp solve specific problems occuring for this type of exception.

My scenario for instance is that I'm adding a new row, changing the fields and issuing an update of the table like this : _Adapter.Update(_DataSet, _Table);
but if I add another row and issue another update for the new row, I get the Concurrency Violation which is abnormal since it's a totaly new row I'm adding.

How to: Handle Concurrency Errors

To resolve a concurrency violation

See Also

Concepts

Other Resources