Data Access and Transactions

Keeping Track of New Records

When you add a new record, several factors determine where in the Recordset it will be inserted. If you need to move the cursor after you’ve added a new record, keeping track of where the record was inserted can be a little tricky.

If your cursor type supports bookmarks, you could save a bookmark to the current row and come back to the record later. However, if you insert records into a table without an index, you will not be able to get back to the newly inserted row until after you use the Requery method to request a new Recordset.

Identity fields, which are updated automatically when you insert a record, can be useful for tracking entities in your database. However, you have no control over the value in this field, and from ADO you have no completely foolproof way of determining what the value will be before it is assigned. Since the value is assigned by the database system itself, you must use a dynamic or keyset cursor, and call the Resync method after you Update the new inserted row. The following example demonstrates this technique by adding a new record from data posted using an HTML form, and by displaying the new row’s identity value:

<%
   'Open a static cursor on the Survey table, and add record.
   Set rs = Server.CreateObject("ADODB.Recordset")
   rs.Open "Survey", Application("ConnectionString"), _
   adOpenKeyset, adLockOptimistic, adCmdTableDirect
   rs.AddNew
   'Add form fields to Recordset, using field names as columns.
   For Each Item In Request.Form
   strItem = Trim(Request.Form(Item))
   If strItem <> "" Then
   rs(Item) = Server.HTMLEncode(strItem)
   End If
   Next
   'Set time of update, and update new record with form values.
   rs("Date") = Now()
   rs.Update
   
   'Force an update of the Recordset with the identity information.
   rs.Resync
   Response.Write "You are response number: " & rs("ID") & "<BR>"
   rs.Close
%>

Important Although this technique provides reasonable performance for smaller tables with few records, performance slows down considerably with larger tables. If you are using SQL Server, you should strongly consider using stored procedures to add new records. For more information, see Stored Procedures.