To read the article online, visit http://www.4GuysFromRolla.com/webtech/100598-1.shtml

Getting the Identity Value from SQL


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:

"SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope."

What that means is that if you have a trigger on the INSERT statement for a table, and if that trigger performs an INSERT (such as inserting a record into a log table), @@IDENTITY returns the just-inserted ID value of the log table, whereas SCOPE_IDENTITY returns the just-inserted ID value of the table that contains the trigger. For more information, see #9 of Doug Seven's article 10 Things You Shouldn't Do with SQL Server.


Related Articles:

  • How do I get the record number of a just added record, using an Access database table?


  • Article Information
    Article Title: Getting the Identity Value from SQL
    Article Author: Scott Mitchell
    Published Date: Monday, October 05, 1998
    Article URL: http://www.4GuysFromRolla.com/webtech/100598-1.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers