Concurrency violation - the UpdateCommand affected 0 of the expected 1 records

Concurrency Violation: The UpdateCommand Affected 'X' of the Expected 'Y' R​​ecords

Error Example:

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

Cause:

General:

This error happens when it is trying to update a record that no longer exists in the database OR that you have required fields that cannot be NULL but no values are being added.

SQL Server Specific:

SQL will trim saved values automatically which can cause this error as it for example treats "10" to be equal to "10 " (note the trailing whitespace). If the system that is synced to SQL sends the value "10 " with the first sync, SQL saves "10". With the next sync the Cloud Connector notices that the value "10 " from the source and the value "10" from SQL are different and triggers an update to SQL. However, as SQL treats the values to be equal, it complains that no actual changes have been made (as functionally it hasn't), that it's not actually an update, and thus the error is thrown.

AS/400 Specific:

There is also a special case with AS/400 where if the fields are not set to allow NULLs, it can also have this error.

Resolution/Workaround:

General:

Check to make sure columns that do not allow NULL values are not being passed nulls (basically correct data mistakes).

If it's due to the other cause where the target system is changing the value somehow, you should be able to detect this with the trace log, which will show that the incoming value is the same as the original value.

SQL Server Specific:

To work around the issue, add the following connection string parameter to your Sql data entity:

TrimWhitespaces=false;

 

This parameter has been introduced with version 10.5.4.0, so you might need to update to the current version: 

AS/400 Specific:

In some cases, it was discovered there was a Primary key set in the IBM AS\400 DB but that it had not been properly exposed due to some security setting on the table. See this article for more information and how to fix it:

http://www.mysamplecode.com/2011/06/rpgle-null-values-in-fields-alwnull-in.html
​