Error: Concurrency violation: the UpdateCommand affected 0 of the
expected 1 records
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 ...
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.
- 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:
- 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.
- 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
not the same and thus throws the
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
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
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
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.
About the author: Brian Cryer
is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.