Retrieving Unique ID after Insert with SQL Server 2000

This tutorial addresses the commonly asked question of how to Insert a record and then retrieve the newly created Unique ID for use on subsequent pages. The author recognises that this can easily be achieved with other extensions whilst using MS Access but this tutorial applies primarily to developers who are using MS SQL Server 2000. Thanks are due to George Petrov who wrote the original Extension.

How to use "Insert Record With Identity" Extension by Richard Davies

Introduction

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.

Lets Go!

Firstly download the extension by George Petrov from

http://www.udzone.com/showDetail.asp?TypeId=3&NewsId=195

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"

Example

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.

Step 1

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.

 

Picture 1


Picture 2

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.

Picture 3

Step 2

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).

 

Picture 4

Step 3

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

<%
If (CStr(Request("MM_insert"))<>"")Then
Session("LastIdent")=LastIdent
Response.Redirect "nextpage.asp"
End if
%>

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
end if
MM_editCmd.ActiveConnection.Close

If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editRedirectUrl)
End If
End If

End If
%>

<%
If(CStr(Request("MM_insert"))<>"") Then
Session("LastIdent")=LastIdent
Response.Redirect "nextpage.asp"
end if
%>
<html>
<head>
<title>Insert the Record and Retrieve ID</title>

Final Stage

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!

Picture 5


Comments

I wonder !!!

March 14, 2002 by nicolas diogo

I have posted some comments on this page before but they seem to be deleted...

What about if you guys start giving some help and reply to our questions.

At the end of the day we are the people that keep coming back.

I had a problem with the extention mentioned here and see that other people as well. It is about an XML file missing.

Do you know how to fix it? Or you are just going to delete this message?

Nicolas

Possible wrong identity returned

March 23, 2002 by Andy Yule

Is it not possible (I think it is) that if two or more users submitted the same insert at similar times that the identity return could be from the another user. Example with two users below, in the order they happen, this problem can manifest itself more frequently when more users are operating the system and/or the same table is being updated by other processes.

1) User 1 - Issues Insert                             

2) User 2 - Issues Insert

3) User 1 - Retrieve Ident

4) User 2 - Retrieve Ident

Maybe a couple of alternatives are :

1) The table definition must have an alternative primary key (that may be a composite key) but is not the identity column, immediately after the insert, issue a select with this key to get the identity column value. If the table has been defined with a unique key then you will always get the correct identity value back.

2) Use a stored procedure that performs the insert and the does a select @@identity all within a transaction.

3) Put option 1 in a SP - I think this would be the most robust.

 

RE: Possible wrong identity returned

March 24, 2002 by George Petrov
No this is not possible because the @@identity value is for set for the current connection only - so it isn't effected by other Inserts from within other connections but the current one.

I get the message

May 8, 2002 by Louis Williams

[Microsoft][ODBC Microsoft Access 97 Driver] Syntax error in string in query expression 'SeekUsername='%'"

Please tell me what is wrong

I am using dreamweaver ultradev: in the insert record insertion form canot be completed if this is not correct

Thank you

lou

See all 7 Comments

You must me logged in to write a comment.