Retrieve a record identity from a SQL Server auto-incremented field on INSERT

SQL Server

Retrieving the identity of a newly inserted record can be very useful if you wish to return the value to the browser or use it in a subsequent insert or update command in order to create or maintain a relationship between two or more records.

Using a stored procedure

CREATE PROCEDURE spInsertRecord

@Text varchar(20),
@Number int

AS

SET NOCOUNT ON

INSERT INTO tblName(TextField, NumberField) VALUES(@Text, @Number)
SELECT @@IDENTITY

RETURN @@IDENTITY

SET NOCOUNT OFF

Go

You can now call the stored procedure from the ASP page with the following code:

<%
set commInsert = Server.CreateObject("ADODB.Command")
commInsert.ActiveConnection = strConnection
commInsert.CommandText = "spInsertRecord"
commInsert.CommandType = 4
commInsert.CommandTimeout = 0
commInsert.Prepared = true
commInsert.Parameters.Append commInsert.CreateParameter("@RETURN_VALUE", 3, 4,4)
commInsert.Parameters.Append commInsert.CreateParameter("@Text", 200,_     1,20,"ABC")
commInsert.Parameters.Append commInsert.CreateParameter("@Number", 3, 1,4,"123")
commInsert.Execute()
%>

This is how you can retrieve the new record ID provided by the RETURN.

<% intNewID = commInsert.Parameters.Item("@RETURN_VALUE").Value %>

Coding the SQL statement in ASP

If you are using ASP with SQL Server it's preferable to use stored procedures, in situations where this isn't possible you can achieve similar results with the following code.

<%
strSQL = "SET NOCOUNT ON INSERT INTO tblName(TextField, NumberField)
    VALUES('ABC', 123) SELECT @@IDENTITY AS NewID SET NOCOUNT OFF"

strConnection = "Provider=SQLOLEDB.1;Password=YourPassword;Persist Security     Info=True;User ID=YourUserID; Initial Catalog=YourDB;_
    Data Source=YOUR_REMOTE_SERVER_NAME"

Set commInsert = Server.CreateObject("ADODB.Connection")
commInsert.Open strConnection
Set rsNewID = commInsert.Execute(strSQL)
intNewID = rsNewID("NewID")
commInsert.Close()
Set commInsert = Nothing
rsNewID.Close()
Set rsNewID = Nothing
%>

The insert command is executed and the new identity returned and stored in the variable varNewID. Note the SET NOCOUNT ON and OFF, either side of the INSERT command, these are important, the command will fail without them.

Notes for Dreamweaver users

Using stored procedures with Dreamweaver is very straight forward. Set up your stored procedure in the SQL Server database as usual. Now click the + button in the data bindings window and select Command (Stored Procedure) from the list. Give your command a name, choose your connection and select Stored Procedure from the type list. In the database items window, click the + symbol next to stored procedures and select your procedure from the list. If your stored procedure required any input values they will appear in the variables box, ready for you to supply the default and run-time values.

Once you have set up the command using a stored procedure as shown above you should see @RETURN_VALUE in the data bindings. Simply drag this to the page as you would any other dynamic text, in code view this should look like this:

<%= commInsert.Parameters.Item("@RETURN_VALUE").Value %>

This will display the new record identity on the page once the INSERT has completed, you can easily modify this to store the value in a variable or use the code directly if wish to use the value rather than simply displaying it, for example to store the value in a variable NewID:

<% intNewID= commInsert.Parameters.Item("@RETURN_VALUE").Value %>