User Tips: Getting the ID of the Just Inserted Database Record

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!

Return to user tips...