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!