VB Forum / Database Access / October 2007

¤ I have a command in a vb method that saves data to an sql 2k database as
¤ follows.
¤     Dim rs As ADODB.Recordset
¤     Dim strSQL As String
¤
¤     Set rs = New ADODB.Recordset
¤
¤     strSQL = "SELECT * " & _
¤              "FROM tblAppointment a " & _
¤              "WHERE a.Id = " & mlngId & " AND " & _
¤                    "a.StoreId = " & mintStoreId
¤
¤     rs.Open strSQL, gcnSEdata, , adLockPessimistic
¤
¤     If mflgNew Then rs.AddNew
¤
¤     With rs
¤         .Fields("StoreId") = mintStoreId
¤         .Fields("StylistId") = mlngStylistId
¤         .Fields("CustomerId") = mlngCustomerId
¤         .Fields("StartTime") = mintStart
¤         .Fields("EndTime") = mintEnd
¤         .Fields("AptDate") = mlngAptDate
¤         .Fields("PrimarySvc") = mstrPrimarySvc
¤         '.Fields("AptClassId") = mintAptClass
¤         .Fields("AptStatusId") = mintAptStatus
¤         .Fields("AptTypeId") = mintAptType
¤         .Fields("CreatedBy") = mlngCreatedBy
¤         .Fields("Confirmed") = mblnConfirmed
¤         .Fields("CallReminder") = mblnCallReminder
¤         .Fields("Notes") = mstrNotes
¤
¤         .Update
¤
¤         If mflgNew Then mlngId = rs.Fields("Id")
¤         .Close
¤
¤     End With
¤
¤     Exit Sub
¤
¤ The problem is in the last IF statement.  After the update, rsFields("Id")
¤ doesn't return a value.  It should return the auto increment number.  Any
¤ thoughts as to how to make it happen?

For SQL Server you will have to execute a follow-up query statement to retrieve the auto increment
value:

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]

Paul
~~~~
Microsoft MVP (Visual Basic)

If you open the recordset using a keyset cursor (and assuming the identity
column is part of the primary key) the field will contain its new value
after calling update.  It must be a server side cursor (CursorLocation =
adUseServer, otherwise the CursorType will revert to static.)

Alternatively, a batch cursor will retrieve server-generated values in new
records after UpdateBatch is called.  (This might also depend on the
identity being the PK -- for me, in practice, that is nearly always the case
anyway.)

And regardless of cursor type, calling Requery will retrieve them, though
you'll lose your record pointer, and overhead of the base query is incurred.

Using SCOPE_IDENTITY() or @@IDENTITY, as was suggested in other posts, can
only be done when you construct an UPDATE SQL statement and execute it in a
query batch, that returns rows, it doesn't really help much with calling the
ADO recordest Update method.

Note that in at least some cases, it is clear that ADO internally uses
@@IDENTITY to populate the identity column's field's value, rather than
reading it from the table.  This is a problem if the table being inserted
fires a trigger that inserts a row in secondary table, in which case,
@@IDENTITY returns the identity value of the new row in the secondary table,
and thus the identity value in the field is wrong -- a very disconcerting
little anomaly, when you are left to discover it the hard way!  :-)

-Mark