"Identity cannot be determined for newly inserted rows" after ADO RecordSet AddNew and Update

Question

I'm getting the error message "Identity cannot be determined for newly inserted rows" when I attempt to edit a field for an ADO RecordSet record after calling AddNew and Update in a .vbs file. However, I'm able to access the primary key that was returned from the database.

I'm not looking for a workaround (e.g. closing the recordset and retrieving the record by its ID), I just really would like to understand why this error is occurring. I've simplified my code in a test file to rule out any other issues. What you see below is the exact code I'm executing with no files included (I've stripped the credentials out of the connection string).

Dim connString : connString = "Provider=SQLOLEDB.1;Persist Security Info=True;Data Source=localhost;Initial Catalog=;User Id=;Password="

Dim conn, rsTaskLog, sSQL

Set conn = CreateObject("ADODB.Connection")
conn.Open connString

' Create a new task log entry.
Set rsTaskLog = CreateObject("ADODB.Recordset")
sSQL = "SELECT * FROM Test"
rsTaskLog.Open sSQL, conn, 1, 3, 1 'adOpenKeyset, adLockOptimistic, adCmdText
rsTaskLog.AddNew
rsTaskLog.Update

' Set the task log result.
rsTaskLog.Fields("test_int").Value = 1 ' Error occurs on this line.
rsTaskLog.Update

rsTaskLog.Close
Set rsTaskLog = Nothing

UPDATE:

I was able to make this code work by adding the following line after the first update:

rsTaskLog.AbsolutePosition = rsTaskLog.AbsolutePosition

Something about moving the current record is putting the RecordSet back into a state where it can be edited (MoveLast and MoveFirst also worked). Anyone have any idea what is going on behind the scenes that is causing this?

Answer 1

The solution I came up with was adding the following line of code right after the first Update is called on the RecordSet:

rsTaskLog.AbsolutePosition = rsTaskLog.AbsolutePosition

For some reason moving the cursor position puts the RecordSet back into a state where Update can be called again without generating the error (MoveFirst and MoveLast also worked, but by setting the AbsolutePosition to itself we're able to maintain the current position). I'm not entirely sure what is going on behind the scenes here, feel free to elaborate if you know in the comments.