Handling “Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.”

I came across a dreaded exception when using tableadapter to update a datarow:

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

I pulled my hair out on this one – read up on concurrency violation and what could cause this exception. MSDN and stackoverflow users would tell you it’s due to a datarow being “detached” from the dataset, or somewhere in code you are doing an update to dataset and your copy of datarow is not the current one.

Whatever, not going to pretend I understand it. Not going to pretend I don’t have a hole in my code somewhere. I just want to update my record Goddammit!

So, here is what you do… go to your dataset designer, right click on the property of your tableadapter and expend on the UpdateCommand property. You’ll want to change the SQL query text’s WHERE cause to only match the primary key ONLY.

For example, the auto-generated update command may be:

UPDATE table
SET field1=value1, field2=value2, etc.
WHERE id=@original_id, field1=@original_field1, field2=@originalfield2…

All you got to do is change it to update row with a row ID:

UPDATE table
SET field1=value1, field2=value2, etc.
WHERE id=@original_id

Problem solved! No more concurrency violation exception, but this doesn’t mean you shouldn’t look for places in your code where concurrent updates may take place and put in some safety measures.

Leave a Reply