Sometimes when you insert a record into your SQL database using ASP, you want toget the identity value of the row you just inserted. Imagine, for example, thatyou allow a user to create a profile. When a user creates a new profile, a new rowis inserted into a table called CustomerProfile. Let's say CustomerProfile has thefollowing rows: an integer identity column / primary key named CustomerProfileID; a varchar(30) column named CustomerName; and a tinyint character field called CustomerAge.
As soon as the user enters theirinformation and clicks "Save", you want them to be taken to the profile editing screen,where they will see the information they just entered and can edit the values andupdate their profile.
Let's look at this as a series of three steps. First, the user must enter his or herinformation. Second, the database must be updated. Third, the user must be sent to theprofile editing page, and have their data shown. Step one could be a simple HTML pagewith a form in it, like so:
<FORM NAME="frmCustomerProfile" METHOD="POST" ACTION="CreateProfile.asp">
Name: <INPUT NAME="txtName" SIZE=30><BR>
Age: <INPUT NAME="nAge" SIZE=5>
</FORM>
In step 2, we need to insert a row into the table. Before I show you the code forStep 2, let's consider what information we need in Step 3. We will need to know theCustomerProfileID so that we can display the correct record. So, in Step 2 we need notonly insert the row, but grab the identity value of the row just inserted. Let's lookat the code for CreateProfile.asp:
<%
Dim txtName, nAge, strSQL, Conn
Set Conn = Server.CreateObject("ADODB.Connection")
txtName = Request.form("txtName")
nAge = Request.form("nAge")
strSQL = "INSERT INTO CustomerProfile (CustomerName, CustomerAge) VALUES ('" & txtName & "'," & nAge & ")"
Conn.Execute(strSQL)
'Now, we must get the identity for the value we just inserted!
Dim rsIdentity
'a recordset to hold the identity value
'This line of code will get us the indentity value for the row
' we just inserted!!
Set rsIdentity = Conn.Execute("SELECT @@IDENTITY FROM CustomerProfile")
'Read the current value:
Dim iCurrentValue
iCurrentValue = rsIdentity(0)
%>
We make use of the system variable
@@IDENTITY in SQL
to obtain the Identity column valuefor the row we just inserted. To find out more about SQL's system variables, check outthe SQL Books Online.For the final Step, we would just need an ASP page that would look very similar to theStep 1 HTML page, except it would have the values of the current Customer's profilealready entered into the INPUT boxes.
I hope this article has helped!
Happy Programming!
UPDATE: Consider Using SCOPE_IDENTITY() Instead of @@IDENTITY |
|---|
In addition to @@IDENTITY, SQL Server includes a function named SCOPE_IDENTITY() that also
returns the just-inserted ID value in an IDENTITY column. The difference between these two functions is subtle, but
important. From the technical documentation:
"
What that means is that if you have a trigger on the |
Related Articles:
