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:
- Getting the Identity Value from SQL
- How do I get the record number of a just added record, using an Access database table?
- What are @@rowcount and @@identity used for?
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:
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!
|Return to user tips...|