Error: Row cannot be located for updating. Some values may have been changed since it was last read.-CodePudding

On change of provider from SQLOLEDB to MSOLEDBSQL in the ADODB connection string, we get the error:

-2147217864 Row cannot be located for updating. Some values may have been changed since it was last read.

The connection string is:

Provider=MSOLEDBSQL;SERVER=servername;APP=Applicationname;DATABASE=databasename;WSID=id;Trusted_Connection=yes;MARS Connection=True;DataTypeCompatibility=80

And the code looks like:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient 
rs.Open("SELECT * FROM tableName WHERE 1 = 2", Adoconnection, adOpenStatic, adLockBatchOptimistic, CommandTypeEnum.adCmdText) 
rs.AddNew
'Add the fields
...
...
rs.UpdateBatch ''this line throws error

Now, when in the connection string of provider is changed to SQLOLEDB, with the same code it works great without any issue.

CodePudding user response：

Try adding a timestamp, or so called "rowversion" column to the table. (use type timestamp - which has ZERO to do with time).

Also, if you have any bit columns in that table, then make sure they are not null, and make sure a default value of (0) is set for that bit column.

And if the application has linked tables, then re-link your tables after you made the above change server side.

CodePudding user response：

I found out the issue, it was in the SQL triggers.

The respective table had some update statements on the trigger. Adding SET NOCOUNT ON just before the update statement in the trigger helped me to avoid this error.