|
All Scripts JavaScripts ASP Scripts Other Scripts
 Insert
Record and go to the Details for that Record. 
For a long time I needed an answer for the question: "How do I retrieve the newly created Unique ID after an insert?". I needed this for a few of my databases. Good example is Orders database. Original Invoice number is created automaticaly when record inserted into the database. I wanted to see that number right after I created the record.
The the best answer that I could find was an Article "How to use "Insert Record With Identity" Extension" by Richard Davies on http://www.udzone.com/ShowDetail.asp?NewsId=3044. Unfortunately this Article is for Ultradev. I use Macromedia Dreamweaver MX and the extension that I had to use with Ultradev does not work with Macromedia Dreamweaver MX. After reading that article a few times I found the way to go around that problem. This page is based on that Article.
The most important thing to remember about this insert page is that you should not specify a page to redirect. This part should 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 have to use this variable in uor URL before we move on to the next page. The variable name is "LastIdent"
After you created insert page you have to open up the code and make a slight change.
This is the code which I inserted
<%
If (CStr(Request("MM_insert"))<>"")Then
Response.Redirect ("details.asp?AutoNumber=" & LastIdent)
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 use it in URL when move to details.asp.
Just to clarify, here is that section of code BEFORE I have finished handcoding:
|
' execute the insert
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close
If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editRedirectUrl)
End If
End If
End If
%> |
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
Response.Redirect ("details.asp?AutoNumber=" & LastIdent)
end if
%>
<html>
<head>
<title>Insert the Record and Retrieve ID</title> |
Use AutoNumber to filter you Recordset on the Details Page.
You also can use it with Session Variable or with Cookies. In this case it will look as:
<%
If (CStr(Request("MM_insert"))<>"")Then
Session("LastIdent")=LastIdent
Response.Redirect "details.asp"
End if
%> |
Do not forget to create Session Variable or Coockies on your Details Page to filter the RecordSet.
|
