Don't panic!

 
 

Cry about...
MS-Windows Troubleshooting


Error: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records


Symptom:

When performing an update (using a DataAdapter) the following exception is generated:

Exception type: DBConcurrencyException
Exception message: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

Cause

When performing an update the underlying ADO driver compares the values for the current database record with what it expects before it performs the update. This check is done to detect whether some other process has updated the database. If the data is not as expected then a DBConcurrencyException is generated.

This exception is being thrown because the driver thinks that the data has been updated by another process.

Possible Remedies:

  • Avoid concurrent updates. Allowing two (or more) processes to update the same database records is risky, because it implies that one process may perform an update using old and out of date information. It is strongly suggested that you review your application design to avoid this.
  • If the database record contains a floating point value (even if that floating point value is not being changed) then this exception can be generated due to a rounding error. For example the value 5.6 cannot be stored without rounding the database (or in memory either, but will be represented by the approximation 5.599999...) To complicate matters slight hardware differences between the implementation of floating point calculations on CPUs can mean that for identical data this error may be thrown on one system but not on another.

    If you are confident that this is the problem (and there is no risk of genuine concurrent updates to the data), then tell the driver to overwrite when it detects a "conflict", eg:

    dim cb as OdbcCommandBuilder(adapter)
    db.ConflictOption = ConflictOption.OverwriteChanges

    I have only encountered this specific issue when using the OdbcDataAdapter and OdbcCommandBuilder, but believe this same error can affect non-ODBC versions.

    This approach can be used to avoid the error being thrown when there are genuine concurrent data updates, but I would not advise it.


These notes have been tested on Windows Vista Business and Windows 2003 server, with ASP.NET 2.0 and may apply to other versions as well.