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.

if you catch this error inside the IDE then you might also see:

A first chance exception of type 'System.Data.DBConcurrencyException' occurred in ...

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.
• There are exceptional circumstances where where this error is generated when values have not changed and yet the values read back from the database is not what ADO expects.

  I have encountered this in two situations, but there may be more:

  1. 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 - which means that this error can arise in production and yet even with identical data cannot be reproduced in test - it has happened to me.
  2. If the date stored in the database is 0, then ADO seems to treat this as System.DBNull even though it isn't. Presumably the checking logic appreciates that a 0 date and System.DBNull are not the same and thus throws the DBConcurrencyException. Whilst there may be good a good reason to store "0" (or rather "0000-00-00 00:00:00") as a date in the database a simple work around would be to consider storing a date that avoids this ambiguity.

     Note: This might depend on the database and the driver being used. I encountered this while connecting to a MySQL database, so I cannot comment on whether the same issue exists with SQL Server.

  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", e.g.:

  dim cb as OdbcCommandBuilder(adapter)
cb.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 as it would be better to consider a design review.

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