Back to Top
The nuts and bolts for
Creative web development

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


Share this Article

Comments

hiya

hi does this solution work with MySQL because im getting this error

Microsoft OLE DB Provider for ODBC Driverserror '80040e31'

Unknown system variable 'NOCOUNT'

if not is there a way to adjust it so it does or is there a tutorial you could link me to

thanks

GREAT JOB!!!

What would we do without Richard Davies?

Thanks...you made my life a little easier.

RE: I wonder !!!

You must create using ASPVB not ASPJAVA.  That is the cause of the XML error.

I get the message

[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