Retrieving Unique ID after Insert with SQL Server 2000
How to use "Insert Record With Identity" Extension by Richard Davies
A very common question that is asked in the various Ultradev newsgroups is "How do I retrieve the newly created Unique ID after an insert?"
There are a couple of Extensions available that allow you to do this with MS Access, but to achieve it with MS SQL Server 2000 is a little more difficult. This tutorial sets out to explain how to use a pre-built extension from George Petrov to achieve this aim.
Firstly download the extension by George Petrov from
Once installed the extension is available from the Server Behaviours panel with the caption "InsertRecordWithIdent"
The most important thing to remember about this extension is that you should not specify a page to redirect to in the Server Behaviour interface. This part should be done after the Behaviour has been applied and needs to be handcoded - don't worry, its very straightforward.
The second thing you need to understand is that this Behaviour returns the newly created ID to the page that does the Inserting, in the form of a normal variable. We need to convert it into a Session or Cookie before we move on. If we do not convert it then the variable will be lost by the time we get to the next page. The variable name, when returned is "LastIdent"
Now, we will use a simple example to demonstrate how this works. Remember that the same principals of a "Normal" insert apply - we are only really concerned with the retrieval of the ID here. If you are unfamiliar with normal Insert behaviours then you should consider reading up in the Ultradev Helpfiles.
We shall be using MS Win 2K Pro, UD 4 and MS SQL Server 2000 for this example.
I set up a simple site and a database with one table called "tblPeople". See picture 1 & 2 for details of this table. You can see that I have entered a few records to ensure that its working OK.
Now we create a page called "insertrecord.asp". This page has a form with a simple 2 column by 3 row table. See picture 3.Note that the two Text Fields are named "FirstName" and "LastName" respectively.
Now is the time to apply the Server Behaviour which we earlier downloaded and installed.
On the Server Behaviours Panel, click the Server Behaviours tab and select "InsertRecordWithIdent". If you have'nt specified a Server Model then you will be prompted to do so (it will obviously need to be ASP). You will also have needed to establish the connection to the development database.
You can see how I have filled in the "InsertRecordWithIdent" dialogue box in picture 4. This screenshot is taken just prior to I clicked "OK" (Note that the field for "After inserting go to" is left blank).
Now we need to open up the code and make a slight amendment, but its very easy so don't worry. Press F10 to go into "Code View" and find the part of the code which Inserts the form into the database. In my page this is just before the <HTML> section and is preceded by a comment which reads in the code.
'execute the insert
What we need to do now is solve the issue which we discussed in the introduction - we need to put the returned value of "LastIdent" into a session.
This is the code which I inserted
What this basically means is "If something has been inserted on this page already, then get the value held in the variable LastIdent and put it into a Session called LastIdent, then move to nextpage.asp.
Just to clarify, here is that section of code AFTER I have finished handcoding:
' execute the insert
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = "SET NOCOUNT ON;" & MM_editQuery & ";SELECT @" & "@IDENTITY AS Ident"
Set rsLastIdent = MM_editCmd.Execute
if NOT rsLastIdent.EOF then
LastIdent = rsLastIdent.Fields.Item("Ident").Value
<> "") Then
<title>Insert the Record and Retrieve ID</title>
We will now build the page which we redirect to after inserting, and to prove that this works we will write the newly created ID to the page.
In UD open a new window and name it "nextpage.asp" (which is the page that insertrecord.asp directs to.
Then we click the Data Bindings tab of the Server Behaviours Panel and, after clicking the Plus sign we select "Session Variable". In the name section of the form we enter "LastIdent" which is the name we gave to the variable when we handcoded the previous page.
Now all we have to do is write this to the screen and we are done. This is accomplished by dragging the newly created Session from the Palette onto the screen. Now you should Save, upload and test. Good luck!