3

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?

David
  • 834
  • 1
  • 10
  • 27
  • On the face of it, it doesn't look like there's anything wrong with your code, so the problem must lie elsewhere. In fact, I just tried it out, including having two recordsets open on the same connection, and didn't have any problem - I could update my newly-added record as many times as I wanted. Are you sure the ScheduledTaskLog table has a primary key defined, i.e. not just an autogenerated ID? – Martha Jun 26 '15 at 02:37
  • To be honest I find this method of updating a record really poor. Why don't you (if you can) create a stored procedure on the target DB and pass your form values to this? You can have one SP to record the new logged entry and return the record PK, then another to update it. If you can't do it like that then write a parameterised insert and update directly into your code. – Paul Jun 26 '15 at 07:31
  • Paul, I've actually already done that, the code is finished. I'm asking from a purely academic standpoint which underlying mechanism is preventing this code from working correctly. Martha, I've simplified the code enough to where I don't believe anything else is affecting it, but tomorrow I will attempt the simplest possible implementation just to be sure. – David Jun 26 '15 at 07:40
  • @Martha I simplified the code down to its most basic parts and it is still giving me the error. I double-checked and it does have an integer primary key that auto-increments (database is SQL Server). – David Jun 26 '15 at 15:39
  • @David: that's very mysterious. Can you try it with a different table in your database? Your code works for me, but obviously I'm using a totally different connection string and a different table. (DB is SQL Server on my end, too.) ADODB.Recordset *should* work the same way regardless of the connection string, but maybe it doesn't? – Martha Jun 26 '15 at 15:52
  • @Martha I've simplified even further. I created a new table Test with only two columns, test_id (int primary key identity) and test_int (nullable int). Same error. Calling rsTaskLog.Fields("test_id").Value successfully returns the primary key from the database and I see the record created in the database. I thought perhaps it had something to do with the EditMode changing but I observed it returning to its original state after the first Update. I'm stumped. – David Jun 26 '15 at 16:21
  • Grasping at straws here: can you try it with the more usual shorthand syntax, `rsTaskLog("test_int") = 1`? (I *think* I tried it both ways last night, but I deleted my test page and don't have time to recreate it now.) – Martha Jun 26 '15 at 16:54
  • @Martha I tried the usual shorthand syntax and it did not work, but I was able to make it work by adding the line `rsTaskLog.AbsolutePosition = rsTaskLog.AbsolutePosition` after the first Update call. Something about resetting the current record position fixes the state of the RecordSet (it also worked with MoveLast and MoveFirst, but I set AbsolutePosition to itself to stay on the same record). It still doesn't answer WHY this is happening though. – David Jun 26 '15 at 17:45
  • 1
    Given that it ought to work, and it works for me, the only thing I can think of is that you've hit some sort of subtle bug in your particular SQL Server version, and/or the interaction between said version and the server OS/IIS/ODBC/etc. versions. I know that's not a particularly satisfying answer (especially given its unprovable nature), but it's the best I've got. – Martha Jun 26 '15 at 19:47
  • 1
    I just hit this as well, it looks like an issue with using Provider=SQLNCLI11; from what I can tell, I wasn't using that driver until updating to win 10, which caused other issues with the DRIVER=SQL Server; I was using. And the answer below makes it work – BlackICE Aug 24 '15 at 18:17
  • It definitely seems to be inconsistent across systems, and given that no one is going to be rushing to address an issue in a dying scripting language I believe the AbsolutePosition solution is the best that can be achieved if you continue to use the same drivers and providers that have the issue. – David Oct 22 '15 at 12:31

1 Answers1

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.

David
  • 834
  • 1
  • 10
  • 27