We help IT Professionals succeed at work.

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

MTSDL
MTSDL asked
on
Medium Priority
3,104 Views
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"
cn.Open
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.AddNew
rs!Name = "Testing"
rs.Update

Open in new window

Comment
Watch Question

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

Author

Commented:
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.

Commented:
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?

Author

Commented:
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:
http://support.microsoft.com/kb/940569
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.

Commented:
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

Commented:
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?

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Commented:
Thanks for for efforts, but I finally got the answer from an MSDN forum:
http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/052ae8de-4ea2-437a-b5d6-d4f9a9e8bdea/?lc=1033&ffpr=0

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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.