[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


ADO 2.8 recordset AddNew / Update - what determines whether @@Identity or Scope_Identity() is used

Posted on 2009-02-17
Medium Priority
Last Modified: 2012-05-06
Does anybody know what determines whether @@Identity or Scope_Identity() is used in the auto generated SQL sent to SQL Server by the ADO recordset AddNew/ Update methods in classic ADO 2.8 with Client side cursor / Lock Optimistic, and with SQL Native Client 10 in the connection string.

The problem I have is that on my own development computer, I can see, using Profiler,  that SELECT SCOPE_IDENTITY() AS SCOPE_ID_COLUMN is part of the SQL generated by the Update - which is exacty what I want.

Yet, the same compiled program on a customer's server generates SELECT @@IDENTITY - which is bad news on a replicated database, as this means that the wrong ID is sometimes returned to the recordset - due to the insert triggers used in SQL 2008 replication.

I've compared loaded DLL versions, assuming it must be due to the wrong/old dependency file related to data access, and I can't see anything abnormal here.

What is going on ?  How can I make the Recordet Update method always use Scope_Identity() to retrieve the  identity for the new record.
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Set cn = New Connection
cn.ConnectionString = "Provider=SQLNCLI10.1; Data Source=MARSBAR; Initial Catalog=GMach; Integrated Security=SSPI"
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = cn
rs.CursorLocation = adUseClient
rs.Open "SELECT TOP 1 * FROM Contacts ORDER BY ID DESC", cn, adOpenStatic, adLockOptimistic
rs!Name = "Testing"

Open in new window

Question by:MTSDL
  • 4
  • 3
LVL 41

Expert Comment

ID: 23663873
I'm confused...  what part get's automatically generated?
Typically you'd write the event handler your self

Author Comment

ID: 23664797
The update method of an ADO recordset after an AddNew inserts the new record to the table by generating some SQL (INSERT INTO...) to execute on the SQL server, and this also includes SELECT statement which returns the new identity of the auto-increment field.  
On my development comuter SELECT Scope_Identity() is part of the SQL sent, on a customers server, it seems that SELECT @@identity is sent.
Scope_Identity() returns the correct new identity to the recordset, whereas @@identity may or may not.
The result is that the ADO recordset may or may not contain the correct identity for that new record.
Event handlers don't come into it.
LVL 41

Expert Comment

ID: 23665062
Are you sure?   I've never heard of ADO automatically generating a select statement on an insert.
Back in the VB6 days, using "classic" ADO, we had to write our code for this...   Are you using some sort of home-made helper class?   Are you talking about a stored procedure that runs on an SQL Server to perform an Insert followed by a Select?
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.


Author Comment

ID: 23665646
Absolutely sure - I've seen it with my own eyes in SQL Profiler, and this is the way the new identity is returned if there is one when using AddNew/Update.  It's all under the bonnet ADO magic, and barely documented, maybe because it is provider specific.
Anyway I may have found an answer in the following:
Though this would only make sense if have this fix already installed on my development pc - and I don't  think I've knowingly done that.
Will check later.
LVL 41

Expert Comment

ID: 23666649
OK... I'm finally following you... yes, the Jet provider does automatically return the last autonumber on an Update in order to fill in the value for the autonumber field in the RecordsSet
So, why not just ignore the automatically created stuff and write your own... that way you'd be able to choose the right version.   For example, use the follow after each insert:

   Set rs = con.Execute("SELECT @@Identity")
   ID = rs.Fields.Item(0).Value

Open in new window

LVL 41

Assisted Solution

graye earned 400 total points
ID: 23666765
Hang on... Are you saying that the RecordSet is already returning the wrong ID (or possibly the wrong ID)?   If so, then you're probably not all that interested in an alternate way to get to the Autonumber, since it's the RecordSet's values that you're trying to fix.
Whew!....  I *FINALLY* figured out what this question is all about!   No, I don't know how to influence the internal workings of the ADO provider.   But let me see if I can help (since I haven't been of much help so far)
  • Are the "SQL Compatibility" levels for the two databases the same... (both set to 90 or 100)?
  • I presume you've checked the connection strings
  • Same OS on both systems (not Vista vs XP)?
  • Same SQL Versions (including Service Packs, Hotfixes, etc)?
  • Same "bit-ness" 32-bit vs 64-bit?

Accepted Solution

MTSDL earned 0 total points
ID: 23669899
Thanks for for efforts, but I finally got the answer from an MSDN forum:

Apparently this was an issue that was not fixed until Windows XP Service Pack 3, and Vista SP1.
My computer is XP SP3, the customers computer was on XP SP2 - hence the difference in behaviour.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question