While some cases may start of as complex, they sometimes turn out to be caused by something not that complex. As the case always is once you have the solution. So I thought I would share some of the things that I've come across, and hopefully tha

I am issue for updating Two different table which are in same also in Different Database. When using following code, it is not updating second table and saying "concurrency violation the updatecommand affected 0 of the expected" error. Please note in following code I am updating Grid one column and updating the Database.

If you are using the wizards in Visual Studio to create SQL commands that include optimistic concurrency then this problem may occur if you change the schema of the table you are updating. In particular if you make a field nullable that wasn't previously it is essential that you regenerate the commands as they WILL fail because the old SQL didn't allow for the possible NULL value. This applies both when generating stored procedures and inline SQL.

This is not always the case. We just ran into a case where the row and data existed but still received the error. The problems was with the compare of a float column. The shown value in the DB was 0.1 and the update failed the where clause (WHERE column = 0.1) I believe this is due to a floating point rounding error.

So if you have floats/doubles as columns in the DB they may (not always) cause this error even though the data looks the same.

A concurrency exception occurs if the update command calls a database procedure to do the update, e.g., call update_customer (ID, Name), even if the update is successful. Get around this by including a version or timestamp in the procedure call, e.g., call update_customer (ID, Name, Version), and update the version or timestamp in the procedure. The parameter must be Input/Output direction

I came across a flaw (bug) in the interface between the TableAdapter's handling of Nullable columns and the logic inserted in the generated stored procedures (when using Strongly Typed DataSets with generated code and allowing the wizard to generate the stored procs).

The generated UPDATE (and DELETE) stored procedures will contain the following predicate for each Nullable column:

AND ((@IsNull_MyNullableColumn = 1 AND [MyNullableColumn] IS NULL) OR ([MyNullableColumn] = @Original_MyNullableColumn))

However, the problem is that the value for parameter @IsNull_MyNullableColumn (as handed off by the TableAdapter) contains the value indicating whether the UPDATED value of MyNullableColumn is NULL – NOT whether the ORIGINAL value was NULL. This causes the update target row to not be found, and a Concurrency Violation exception being raised when updating a column from a NULL to a non-NULL value.

A simple fix is to modify the UPDATE (and DELETE) stored procedure predicates for nullable columns to the form:

AND (([MyNullableColumn] IS NULL) OR ([MyNullableColumn] = @Original_MyNullableColumn))

(simply removing the "@IsNull_MyNullableColumn = 1 AND " portion of the statement is a quick and easy fix). However, that is not necessarily bulletproof.

A more general fix would be to modify the predicates to this form:

AND ((@Original_MyNullableColumn IS NULL AND [MyNullableColumn] IS NULL) OR ([MyNullableColumn] = @Original_MyNullableColumn))

This will match when the table value and the original value are either both NULL or are both non-NULL and have the same value. The additional @IsNull_MyNullableColumn is really just a distraction in the generated SPs.

More commonly than missing data, I think people must run into the situation I resolved. The automatically built update command can build as many as three parameters for each field in the SELECT command. It builds one for the new version of the data, a second to for the original version of the data, and a third for which I don't understand the purpose. The first parameter (p1) is used in the SET clause of the update command to push in the new value. The second and third – if they exist – are used in the WHERE clause in a series of AND statements like:

AND ((@p3 = 1 AND [Blah] IS NULL) OR ([Blah] = @p2))

This provides plenty of opportunity to go wrong. As the author points out, missing rows are a problem. But so are rows where one of your values don't match either the original or the new value or the null/default condition of the field. Here is a list of ways you can go wrong and it's probably not the complete list:

1) Somebody else has gotten to the row before you and changed one or more of the values

2) You have created one or more columns in your database that allow null values, AND specified a Default Value or Binding that is NOT null (one of my dumb mistakes). You need to do one or the other because the command builder will put in the AND [blah] IS NULL condition because the column can have them. But your default/binding value will never be null so the row will never be found

3) You like to get all hilarious in your SELECT queries and fill in default values for stuff that is null in the table (another dumb mistake I was making). A statement like this should do the trick: COALESCE([Blah], 'Blank'). Since the value the DataTable thinks the 'original' is now 'Blank' instead of null like it is in the database table, the row will never be found.

4) You are using a different SELECT statement in the SqlAdapter build than you used to generate your table. You can actually get away with this once you understand how the commands are built from the SELECT statement – something kind of poorly explained here: