|
Spooky's Databases > Advanced > DRW >
Getting the ID of last added record
When you submit a new
record to a database, you would sometimes like to be
able to return the ID of the record for other purposes.
The unique ID is a way
of recognizing the record that the user has added. You
could use other fields or methods, but there isn't
always a guarantee that this number or record is
correct.
The modifications we are
about to do, are similar to the
Spooky
diet. The web
bot information is removed and the code is reduced to
ASP only.
This is necessary as the web bot controls the ASP that
is written to our page (in HTML view)
It the web bot was left,
all our work gets overwritten when we save the page.
NOTE this topic
is only relevant if using ACCESS as a database, this
method does not work for SQL.
So, moving right
along........
Firstly, we will
construct a very simple page containing 1 text box.
- "New Page"
- Insert > Form > Text
box
- Right click > Form
properties > Send to database
- Options > Create
Database > OK > OK
- Save the file as ASP
Ok, now we have our
file. Lets switch to HTML view and see what we have.
It will look something like this :
<html>
<head>
<meta http-equiv="Content-Type"
content="text/html;
charset=windows-1252">
<meta name="GENERATOR"
content="Microsoft FrontPage 5.0">
<meta name="ProgId"
content="FrontPage.Editor.Document">
</head>
<body>
<form method="POST" action="--WEBBOT-SELF--">
<!--webbot bot="SaveDatabase"
SuggestedExt="asp" S-DataConnection="new_page_12" S-RecordSource="Results"
U-Database-URL="fpdb/new_page_12.mdb" S-Builtin-Fields="REMOTE_HOST
HTTP_USER_AGENT Timestamp REMOTE_USER" S-Builtin-DBFields="Remote_computer_name
Browser_type Timestamp User_name" S-Form-Fields="T1"
S-Form-DBFields="T1" --><p>
<input type="text" name="T1"
size="20"><input type="submit" value="Submit"
name="B1"><input type="reset" value="Reset"
name="B2"></p>
</form>
</body>
</html>
Now press "F5" to
refresh your screen. You see all the ASP that FP has
just generated?
Your page will now look like this :
<%
' FP_ASP ASP Automatically generated by a Frontpage
Component. Do not Edit.
On Error Resume Next
Session("FP_OldCodePage") = Session.CodePage
Session("FP_OldLCID") = Session.LCID
Session.CodePage = 1252
Err.Clear
strErrorUrl = ""
If Request.ServerVariables("REQUEST_METHOD") = "POST"
Then
If Request.Form("VTI-GROUP") = "0" Then
Err.Clear
Set fp_conn = Server.CreateObject("ADODB.Connection")
FP_DumpError strErrorUrl, "Cannot create connection"
Set fp_rs = Server.CreateObject("ADODB.Recordset")
FP_DumpError strErrorUrl, "Cannot create record set"
fp_conn.Open
Application("new_page_12_ConnectionString")
FP_DumpError strErrorUrl, "Cannot open database"
fp_rs.Open "Results", fp_conn, 1, 3, 2 ' adOpenKeySet,
adLockOptimistic, adCmdTable
FP_DumpError strErrorUrl, "Cannot open record set"
fp_rs.AddNew
FP_DumpError strErrorUrl, "Cannot add new record set to
the database"
Dim arFormFields0(1)
Dim arFormDBFields0(1)
Dim arFormValues0(1)
arFormFields0(0) = "T1"
arFormDBFields0(0) = "T1"
arFormValues0(0) = Request("T1")
FP_SaveFormFields fp_rs, arFormFields0, arFormDBFields0
If Request.ServerVariables("REMOTE_HOST") <> "" Then
FP_SaveFieldToDB fp_rs,
Request.ServerVariables("REMOTE_HOST"), "Remote_computer_name"
End If
If Request.ServerVariables("HTTP_USER_AGENT") <> ""
Then
FP_SaveFieldToDB fp_rs,
Request.ServerVariables("HTTP_USER_AGENT"), "Browser_type"
End If
FP_SaveFieldToDB fp_rs, Now, "Timestamp"
If Request.ServerVariables("REMOTE_USER") <> "" Then
FP_SaveFieldToDB fp_rs,
Request.ServerVariables("REMOTE_USER"), "User_name"
End If
fp_rs.Update
FP_DumpError strErrorUrl, "Cannot update the database"
fp_rs.Close
fp_conn.Close
FP_FormConfirmation "text/html; charset=windows-1252",_
"Form Confirmation",_
"Thank you for submitting the following information:",_
"test.asp",_
"Return to the form."
End If
End If
Session.CodePage = Session("FP_OldCodePage")
Session.LCID = Session("FP_OldLCID")
%>
<html>
<head>
<meta http-equiv="Content-Type"
content="text/html;
charset=windows-1252">
<meta name="GENERATOR"
content="Microsoft FrontPage 5.0">
<meta name="ProgId"
content="FrontPage.Editor.Document">
</head>
<body>
<form method="POST" action="--WEBBOT-SELF--">
<!--webbot bot="SaveDatabase"
suggestedext="asp" s-dataconnection="new_page_12" s-recordsource="Results"
u-database-url="fpdb/new_page_12.mdb" s-builtin-fields="REMOTE_HOST
HTTP_USER_AGENT Timestamp REMOTE_USER" s-builtin-dbfields="Remote_computer_name
Browser_type Timestamp User_name" s-form-fields="T1"
s-form-dbfields="T1" u-asp-include-url="_fpclass/fpdbform.inc"
startspan --><input
TYPE="hidden" NAME="VTI-GROUP" VALUE="0"><!--#include
file="_fpclass/fpdbform.inc"--><!--webbot bot="SaveDatabase"
endspan i-checksum="40548" --><p>
<input type="text" name="T1"
size="20"><input type="submit" value="Submit"
name="B1"><input type="reset" value="Reset"
name="B2"></p>
</form>
</body>
</html>
This is now the full Web
bot based code that FP uses to save to your new
database.
To modify it, there's a couple of things we need to do.
- Remove ALL of the
gray code EXCEPT this text :
<!--#include file="_fpclass/fpdbform.inc"-->
- Remove this
line of text :
' FP_ASP ASP Automatically generated by a Frontpage
Component. Do not Edit.
This is important, as when you save this file, all of
the red code will be removed automatically if you do
not.
- Ok, now press F5 and
then save the file.
Your page now should
look like this :
<%
On Error Resume Next
Session("FP_OldCodePage") = Session.CodePage
Session("FP_OldLCID") = Session.LCID
Session.CodePage = 1252
Err.Clear
strErrorUrl = ""
If Request.ServerVariables("REQUEST_METHOD") = "POST"
Then
If Request.Form("VTI-GROUP") = "0" Then
Err.Clear
Set fp_conn = Server.CreateObject("ADODB.Connection")
FP_DumpError strErrorUrl, "Cannot create connection"
Set fp_rs = Server.CreateObject("ADODB.Recordset")
FP_DumpError strErrorUrl, "Cannot create record set"
fp_conn.Open
Application("new_page_12_ConnectionString")
FP_DumpError strErrorUrl, "Cannot open database"
fp_rs.Open "Results", fp_conn, 1, 3, 2 ' adOpenKeySet,
adLockOptimistic, adCmdTable
FP_DumpError strErrorUrl, "Cannot open record set"
fp_rs.AddNew
FP_DumpError strErrorUrl, "Cannot add new record set to
the database"
Dim arFormFields0(1)
Dim arFormDBFields0(1)
Dim arFormValues0(1)
arFormFields0(0) = "T1"
arFormDBFields0(0) = "T1"
arFormValues0(0) = Request("T1")
FP_SaveFormFields fp_rs, arFormFields0, arFormDBFields0
If Request.ServerVariables("REMOTE_HOST") <> "" Then
FP_SaveFieldToDB fp_rs,
Request.ServerVariables("REMOTE_HOST"), "Remote_computer_name"
End If
If Request.ServerVariables("HTTP_USER_AGENT") <> ""
Then
FP_SaveFieldToDB fp_rs,
Request.ServerVariables("HTTP_USER_AGENT"), "Browser_type"
End If
FP_SaveFieldToDB fp_rs, Now, "Timestamp"
If Request.ServerVariables("REMOTE_USER") <> "" Then
FP_SaveFieldToDB fp_rs,
Request.ServerVariables("REMOTE_USER"), "User_name"
End If
fp_rs.Update
FP_DumpError strErrorUrl, "Cannot update the database"
fp_rs.Close
fp_conn.Close
FP_FormConfirmation "text/html; charset=windows-1252",_
"Form Confirmation",_
"Thank you for submitting the following information:",_
"test.asp",_
"Return to the form."
End If
End If
Session.CodePage = Session("FP_OldCodePage")
Session.LCID = Session("FP_OldLCID")
%>
<html>
<head>
<meta http-equiv="Content-Type"
content="text/html;
charset=windows-1252">
<meta name="GENERATOR"
content="Microsoft FrontPage 5.0">
<meta name="ProgId"
content="FrontPage.Editor.Document">
</head>
<body>
<form method="POST" action="--WEBBOT-SELF--">
<input TYPE="hidden" NAME="VTI-GROUP"
VALUE="0"><!--#include
file="_fpclass/fpdbform.inc"--><p>
<input type="text" name="T1"
size="20"><input type="submit" value="Submit"
name="B1"><input type="reset" value="Reset"
name="B2"></p>
</form>
</body>
</html>
It does? good!
Now we need to modify the ASP to show us the new record
ID.
Find the lines :
fp_rs.Update
FP_DumpError strErrorUrl, "Cannot update the database"
Add a new line between
them like so :
fp_rs.Update
NewId = fp_rs(0)
FP_DumpError strErrorUrl, "Cannot update the database"
What we have done, is
updated the record (fp_rs.Update) and then we have
asked for the value of the new record, from the first
column in our database (0) which is our Autonumber
column.
We have saved this new ID number to the variable "NewId"
Ok, so now we have the
ID number, we need to display it. Find the line :
"Thank you for submitting the following information:",_
And modify it to look
like this :
"Record ID "&NewId&" - Thank you for submitting the
following information:",_
Now save the file and
open it in your browser to see the result!
If you are redirecting to another page, then the method is
similar, however you will change the redirect code from this :
Response.Redirect "confirmation.asp
To this :
Response.Redirect "confirmation.asp?ID="& NewId
Then, in your 'confirmation' page the variable will be available
via the request :
<%=Request.Querystring("ID")%>
|
