Windows Systems Administrator Jupitermedia US-CT-Darien Post A Job | Post A Resume

WebDaily: Your daily source for Web Technology Tips and Tricks!

---

*****************************************************************
Obtaining the Identity value in SQL
*****************************************************************


* This article deals with Active Server Pages ans SQL Programming

There arise times when you immediately need the identity value of a row that was just inserted into your database. For example, say that you are writing an Internet-based information entry system which keeps track of your company's contact information. There is a screen for creating a new contact record in the database, and a screen to enter one to many contact addresses into the database. If you have two tables, a Contact table, and a ContactAddress table, you most likely have a ContactID as the primary key in your Contact table, and a ContractAddressID as a primary key in your ContactAddress table. Your ContactAddress table also most likely has a ContactID as a foreign key.

Due to DRI, before you can insert a value into the ContactAddress table, you *must* have a corresponding contact record in the Contact table. If you want your ASP application to automatically take the web user to entering contact address information immediately after they've created a new contact, you will need that new contact's ContactID. For this example, let's say we have three .ASP files: NewContact.asp, DBAddContact.asp, and NewContactAddress.asp. NewContact contains the form where the new Contact's information is filled out, which then calls DBAddContact.asp, which runs the SQL scripts needed to insert a row into the Contact table. Finally, you want NewContactAddress.asp called from DBAddContact.asp, passing the new row's identity value.

A very simple way to code for this is to use the following syntax:

DBAddContact.asp
================

<%


'DB Calls to add a row to the Contact table

Dim strSQL, rsIdentity
strSQL = "SELECT @@IDENTITY FROM Contact"
Set rsIdentity = Conn.Execute(strSQL)

Dim strURL
strURL = "NewContactAddress.asp?ContactID=" & _
rsIdentity(0)

Response.Redirect strURL


%>

The "SELECT @@IDENTITY FROM Contact" gets the identity value of the row that was just inserted.

This code can be made more efficient and cleaner by using a stored procedure. Just create a stored procedure called sp_AddContact { Contact Information, ContactID }, where Contact Information are the variables that need to be passed in to insert the correct data into the new row, and ContactID is an output variable of the new identity value. The stored procedure would look something like this:

CREATE PROCEDURE sp_AddContact
(

@Name varchar(50),
@ContactID int OUTPUT

)

AS

INSERT INTO ContactID (Name)
SELECT @Name

SELECT @ContactID = @@IDENTITY FROM Contact


As you can hopefully see, this approach is cleaner and more streamlined. When you need to get the identity value from a row you just inserted into a table, make use of the @@IDENTITY system variable. Happy Programming!

*****************************************************************
*****************************************************************
To subscribe to WebDaily, point your browser to:
http://www.4GuysFromRolla.com/webtech/webdaily
*****************************************************************
*****************************************************************

Windows Internet Technology | ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article