There are some instances where you will need to insert a record to a database and, at the same time, need to retrieve the unique ID that was just created for that record. For example, a visitor may register on your website to become a customer. When they complete their registration, you may want to give them a unique Member ID #.
To do this, create a database called MyMembers and create a table called tblMemberInfo with these fields:
ID – autonumber
Email – text field
Name – text field
DateEntered – date/time field
Next, create a form page called /Register.asp and copy the below code into your page:
<form name="YourFormName" method="Post" action="confirmation.asp">
<table>
<tr><td>Email: </td>
<td><input type="text" name="Email" size="50"></td></tr>
<tr><td>Name: </td>
<td><input type="text" name="Name" size="50"></td></tr>
</table>
<input type="submit" name="Submit" value="Submit Form">
</form>
Then, create a page called /Confirmation.asp and copy the below code into your page:
<!--#include virtual="/includes/connection.asp" -->
<%
DIM objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "tblCustomerInfo", objConn, , adLockOptimistic, adCmdTable
objRS.AddNew
objRS("Email") = Request.Form("Email")
objRS("Name") = Request.Form("Name")
objRS("DateEntered") = Date()
objRS.Update
DIM bookmark
bookmark = objRS.absolutePosition
objRS.Requery
objRS.absolutePosition = bookmark
DIM strMemberID
strMemberID = objRS("ID")
%>
<p>Thank you for registering with us. Here is your Member ID: <% =strMemberID %></p>
<%
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>
There you have it. Now you can insert a new record, retrieve its unique ID, and display it on your page all in one fell swoop!