"Identity could not be determined for newly inserted rows" after adding ADO RecordSet AddNew and updating
I get an "Identity could not be defined for newly inserted rows" error when I try to edit a field for an ADO RecordSet after calling AddNew and Update in a .vbs file. However, I can access the primary key that was returned from the database.
I'm not looking for a workaround (like closing the recordset and fetching the record by its ID), I just would like to understand why this error is happening. I have simplified my code in the test file to rule out any other problems. What you see below is the exact code I am executing without files (I removed the credentials from 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 managed to get this code to work by adding the following line after the first update:
rsTaskLog.AbsolutePosition = rsTaskLog.AbsolutePosition
Something about moving the current record puts the RecordSet back in an editable state (MoveLast and MoveFirst work as well). Does anyone know what is going on behind the scenes that is causing this?
source to share
The solution I came up with was adding the following line of code right after calling the first update on the RecordSet:
rsTaskLog.AbsolutePosition = rsTaskLog.AbsolutePosition
For some reason, moving the cursor position puts the RecordSet back into a state where the update can be called again without throwing an error (MoveFirst and MoveLast also work, but by setting AbsolutePosition for ourselves, we can maintain the current position). I'm not entirely sure what's going on behind the scenes here, feel free to clarify if you know in the comments.
source to share