ADO 2.8 recordset AddNew / Update - what determines whether @@Identity or Scope_Identity() is used
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.
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
ASKER
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.
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.
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?
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?
ASKER
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.
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.
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:
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).ValueSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Typically you'd write the event handler your self