User Tips: Getting the ID of the Just Inserted Database Record
By Dan H.
Hello, I'm a fan of the site, and I have a unique tip that you may want to
post! When I came upon it I was kind of surprised it worked, but it
turned out to be helpful and improves on the normal strategy of getting
the identity value from SQL. There are many articles on 4Guys and other sites
that illustrate how to grab the identity value from a newly inserted record. A couple
of these articles are:
These solutions use two database calls from an ASP page, one to insert the record and the
other to select the identity value. My approach uses a single database call and the
NextRecordset method of the ADO Recordset object.
To both insert a new row and get its ID value, use the following code:
Dim db,rcs,new_identity
'Create a database connection
Set db = Server.CreateObject("adodb.connection")
db.Open "DSN=MyDSN"
'Execute the INSERT statement and the SELECT @@IDENTITY
Set rcs = db.execute("insert into tablename (fields,..) " & _
"values (values,...);" & _
"select @@identity").nextrecordset
'Retrieve the @@IDENTITY value
new_identity = rcs(0)
|
Neat, huh? Keep in mind that since this uses the @@IDENTITY constant, this
will only work with SQL Server. For information on retrieving the just-inserted auto-incremented
ID value with Access, see: How do I get
the record number of a just added record, using an Access database table?.
The above code takes advantage of the NextRecordset method in particular, but the
trick is to use it on the Execute method of the Connection object itself.
Otherwise it returns a closed recordset, and then you can't use the
NextRecordset method. Weird quirk.
Anyways, this also makes it impossible for another record to be inserted
before getting the identity value, unlike the previous example, so you
know you are getting the right value. And it does it all with less
typing!
For more great SQL-related articles be sure to check out the Stump
the SQL Guru section on 4Guys as well as SQLTeam.com!
Happy Programming!
