ASPWatch : Accessing Identity Values

Identity columns in SQL Server (similar to an AutoNumber field in Access, a field that is automatically populated by an incrementing integer when a record is inserted into the table) are commonly used as primary keys for tables. A common problem ASP/SQL developers run into when inserting data into more then one table on a single ASP page is how to access the value of an identity field after inserting a record that must be referred to in subsequent inserts.

How to access the value of an identity column

As an example, let's take a simple ASP order form that collects customer information and the order all on one page. This information is submitted and written to two table in a SQL Database, the "customer" and the "onlineOrder" table. The "customer" table uses an identity column as it's primary key and the "onlineOrder" table has a foreign key reference to the "customer" table. The ASP page that processes the order form needs to first insert the customer data into the "customer" table and then insert the order data in the "onlineOrder" table. The problem is, we need the value created for the identity column by the INSERT into the "customer" table to insert it into the "onlineOrder" table.

Use a SELECT query

One obvious way to get the value of the identity column is to execute a SELECT statement after the INSERT. This might look something like:

sqlInsert = "INSERT INTO customer (field1,field2,field3,...) VALUES ('" & Request.Form("field1") & "','" & Request.Form("field2") & "','" & Request.Form("field3") & ... & ")"

sqlSelect = "SELECT customerID FROM customer WHERE field1 = '" & Request.Form("field1") & "' and field2 = '" & Request.Form("field2") & "' and field3 = '" & ......

cn.Execute(sqlInsert)

set rsIdentity = cn.Execute(sqlSelect)

Now rsIdentity("customerID") contains the value of the identity field.

However, this is not a very efficient way to accomplish our task. You will eventually need to index your table on all the fields in the SELECT statement if you start getting a lot of records in the table or the SELECT query will become very slow.

Use @@IDENTITY

A more efficient way to accomplish this task is using the SQL Server global variable @@IDENTITY. SQL Server (I don't know if this will work for other database engines like Oracle) will always place the value of the last identity column inserted in the @@IDENTITY variable. So we can use the following:

sqlI nsert = "INSERT INTO customer (field1,field2,field3,...) VALUES ('" & Request.Form("field1") & "','" & Request.Form("field2") & "','" & Request.Form("field3") & ... & ");SELECT @@IDENTITY customerID"

set rsIdentity = cn.Execute(sqlInsert)
set rsIdentity = rsIdentity.NextRecordSet

Again, rsIdentity("customerID") contains the value of the identity field. Note we had to use the ADO "nextRecordSet" method since we tied two SQL statements together using a semi-colon and the identity value comes from the second.

WARNING: The @@IDENTITY column always contains the value of the LAST identity column inserted. If the table in your INSERT statement has any triggers that also perform INSERTs, then the value in @@IDENTITY might not be what thought it would be!

Use a Stored Procedure

@@IDENTITY isn't a bad way to access the value of the identity so it can be used in a subsequent INSERT statement. However, this can be accomplished even more efficiently using a stored procedure. The advantages of stored procedures is beyond the scope of this article (I'll write another article on this later and replace this with a link to that article), but the following will do what we want extremely efficiently:

CREATE PROCEDURE mysp_InsertOrder
@field1 fieldType,
@field2 fieldType,
@field3 fieldType,
...
@orderField1 fieldType,
@orderField2 fieldType,
@orderField3 fieldType,
AS

BEGIN TRANS

INSERT INTO Customer (field1, field2, field3, ...)
VALUES (@field1, @field2, @field3, ...)

INSERT INTO onlineOrder (customerID,orderField1, orderField2, orderField3, ...)
VALUES (@@IDENTITY,@orderField1, @orderField2, @orderField3, ...)

COMMIT TRANS

We also are able to take advantage of SQL "transactions." This example is a bit simplified (there's no error checking), but by performing the INSERTs in a transaction we ensure that either both or neither INSERTs occur. If INSERT 1 is successful but INSERT 2 is not, the first one will be "rolled back."

Conclusion

There you have it, three different ways to access the value of an identity column. Assuming you don't use a lot of triggers in your database, I'd recommend avoiding the first one. Stored procedures have many advantages over coding your SQL directly in your ASP pages so I'd recommend looking into them. We'll explore this further in a future article.

Happy coding...

Rick Winfield
Rick is a specialist in web/database development, data modeling, information architecture and web usability/UI design. A computer programmer since his school got a Commodore PET in 1982, an Internet user since 1989 (while a student of Linguistics and Cognitive Science at the University Of Pennsylvania) and a Web Developer since 1995, Rick has a unique combination of experience in development, customer support and training, psycology and Internet business. Rick first started using ASPs when IIS 3 was in beta and continues to use Windows DNA as his prefered development platform for Web applications (although everything needs a little PERL in it :-)

Rick is currently the development manager at workz.com, an Internet start-up serving the online small business market.

What did you think of this article?
Not useful							Very useful
Badly written							Well written
Too short							Too long

This document can be found in these Encyclopedia chapters:
º Intranets/Extranets
º Microsoft SQL Server

Got something to add to this article?
Create a new discussion

View Article Statistics

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

About your privacy | Want to advertise? | Contact Us