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

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!

---

Related Articles:

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

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