Sql Server - Retrieving Identity Field Values
What are Identity Fields?
If you have developed programs using Visual Basic with data stored in Access databases, you are probably familiar with the concept of autoincrement fields (sometimes called counters). Typically you will have used these to provide a unique record identifier (I won't get into a discussion on indexes or keys here), and often to help maintain the relationship between two or more tables. As an example, the following code (simplified) adds a record to the transaction table in a stock system, reads back the value of the autoincrement field, and uses this to 'link' entries in the pallet table.
Set LocalRs = LocalDb.OpenRecordset("TRANSACTION", dbOpenTable)
LocalRs.AddNew
LocalRs("STOCKCODE") = LocalHeader
........
........
LocalRs.Update
LocalRs.Bookmark = LocalRs.LastModified
StockPtr = LocalRs("COUNTER")
LocalRs.Close
Set LocalRs = LocalDb.OpenRecordset("PALLETS", dbOpenTable)
LocalRs.AddNew
LocalRs("STOCKCODE") = LocalHeader
........
........
LocalRs("TRANSACTION") = StockPtr
LocalRs.Update
Sql server has a similar concept of autoincrementing fields (at least relatively recent versions do). Fields can have an Identity property that uses seed and increment values to update the field after a record has been added to the recordset. From a programming perspective this works the same way as our previous Access example, with one major exception, there is not an obvious way to retrieve the value.
Possible Methods of retrieving the value for a newly created record
Sql server maintains a global value for the identity of the most recent recordset addition, and you can read this value programmatically (@@identity). Unfortunately there are a number of drawbacks, most important of which is that this value will always be zero if you are using recordsets (and addnew) rather than an Sql Insert statement. Of couse, for new projects, you could structure your code to use Insert (though you need to take care with the contents of string fields, pictures, etc) but we were concerned with upgrading existing code, and very reluctant to make significant structural changes.
Even if you intend to use Insert, create a stored procedure that returns the identity value as part of the procedure (Example available on request. Sql server 2000 adds identity values that are at session level - scope_identity, and table level - ident_current(tablename), which should be thread safe, and avoid known problems with triggers updating other tables).
If you have been searching the web for a solution to the problem of reading identity fields, you will have seen the suggestion that you could just read the largest value for that field. This sounds ok at first, until you remember that you are working on a multiuser system, and that another user may have added a record since yours was added. Also, you cannot use this method to find a record that you have just edited (rather than added), unlike the bookmark functionality offered by Access.
What we need is some way of uniquely identifying 'our' record, so that we can find it again, and read the value of the identity field. Now it may be that your data is such that it would be relatively easy to find any given record on a combination of fields, but this did not apply in our case, so we decided to add our own data stamp to the record, prior to the record update. This gave us two further problems - creating the unique value, and having a field to populate, and a little concern about speed.
Chosen Method
The are many methods that you could use to generate a unique string or numeric value, and you will be glad to hear that most of them are well documented on the web. For this particular application we liked the idea of using GUIDs (Globally Unique Identifiers), and there is plenty of example code around to show you how to use them.
Example
Our previous code example will now look something like this:
Dim Zrecord As Long
Dim Zvalue As String
Zrecord = GetMaxZrecord("TRANSACTION", "COUNTER")
' Get The Current Maximum Value For The Identity Field
' And Check To See If We Have A Field To Put Our Unique Value In
GUIDString Zvalue
' Generate The Unique String Based On A GUID
Set LocalRs = LocalDb.OpenRecordset("TRANSACTION", dbOpenTable)
LocalRs.AddNew
LocalRs("STOCKCODE") = LocalHeader
........
........
LocalRs("ZUNIQUE") = Zvalue
' Stuff The Unique Value Into The Field
LocalRs.Update
LocalRs.Close
StockPtr = ThisIdentity("TRANSACTION", "COUNTER", Zrecord, Zvalue)
' Get The Value Of The Identity Field For The Record That We Have Just Added
Set LocalRs = LocalDb.OpenRecordset("PALLETS", dbOpenTable)
LocalRs.AddNew
LocalRs("STOCKCODE") = LocalHeader
........
........
LocalRs("TRANSACTION") = StockPtr
LocalRs.Update
With supporting functions as follows:
Function GetMaxZrecord(ByVal Tname As String, ByVal Fname As String) As Long
Dim SQL As String
Dim LocalRs As New ADODB.Recordset
Dim MaxId As Long
On Error GoTo GetMaxErr
SQL = "Alter Table " & Tname & " Add ZUNIQUE VarChar(255)"
SQLServerDB.Execute SQL
' Not An Ideal Solution, But This Is A Retro Fit, And Sometimes You
' Just Have To Be Pragmatic. Obviously If You Know About This In Advance
' You Can Add The Required Fields During The Data Conversion Process
' Or you could add them all at once after conversion How
SQL = "SELECT Max(" & Fname & ") As NEXTZIDENTITY From " & Tname
LocalRs.Open SQL, SQLServerDB, adOpenDynamic
MaxId = Val(Dbl(LocalRs("NEXTZIDENTITY")))
LocalRs.Close
GetMaxZrecord = MaxId
GoTo GetMaxEnd
GetMaxErr:
Select Case Err
....Case -2147217900
........Resume Next
....Case Else
........ErrorFlag = ShowErr("ModGuid:GetMaxZrecord", Err, Error)
' Call Whatever You Have In The Way Of A Standard Error Handler
........If ErrorFlag Then
............Resume
........Else
............Resume Next
........End If
End Select
GetMaxEnd:
End Function
Public Function ThisIdentity(ByVal Tname As String, ByVal Fname As String, ByVal MinRec As Long, ByVal Zunique As String) As Long
Dim SQL As String
Dim LocalRs As New ADODB.Recordset
Dim NextId As Long
On Error GoTo NextErr
SQL = "SELECT " & Fname & " As NEXTZIDENTITY From " & Tname
SQL = SQL & " Where " & Fname & " >= " & MinRec & " And ZUNIQUE = '" & Zunique & "'"
LocalRs.Open SQL, SQLServerDB, adOpenDynamic
If LocalRs.BOF Then
....NextId = UNSET
Else
....NextId = Val(Dbl(LocalRs("NEXTZIDENTITY")))
End If
LocalRs.Close
If (NextId = UNSET) Or (NextId = 0) Then
....Beep
....Debug.Print "Could Not Get Record Value For " & Fname & " In Table " & Tname
End If
ThisIdentity = NextId
GoTo NextEnd
NextErr:
Select Case Err
....Case Else
........ErrorFlag = ShowErr("ModGUID:ThisIdentity", Err, Error)
....If ErrorFlag Then
........Resume
....Else
........Resume Next
....End If
End Select
NextEnd:
End Function
This all seems to work for new records, what about when you are updating an existing record?
Simple enough, just update the value returned by GetMaxZrecord to be that of the record currently being edited
Code in our example would look like:
Zrecord = LocalRs("COUNTER")
If you do not do this, ThisIdentity will be looking for a record it cannot find. There is of course a speed issue for tables with a lot of records, but this seems to be a price worth paying - unless you know better.