Of the ADO objects, the Recordset object does most of the data processing and thus it has more properties, methods, and events than the other ADO objects.

Basic Usage

If you are not returning a recordset, then you should probably use the Connection or Command object. If you are returning row(s) in a recordset, then the Recordset object is for you.

  • Create Recordset
  • Open Recordset
  • Move Through Recordset
  • Now that you're where you want:
    • Accessing Fields in Recordset
    • Update Data in a Recordset
    • Delete a Row in a Recordset
    • Add Row to a Recordset
    • Persist Data in a Recordset
  • Close and Release Recordset

Create Recordset

In Visual Basic this is done with syntax similar to the following:

Dim rstObject1 as New ADODB.Recordset
Dim rstObject2 as ADODB.Recordset

In VBScript this is done with syntax similar to the following:

Set rstObject1 = Server.CreateObject("ADODB.Recordset")

In JavaScript/JScript this is done with syntax similar to the following:

rstObject1 = new ActiveXObject("ADODB.Recordset");

Open Recordset

The basic syntax for opening a Recordset is as follows:

recordset.Open Source, ActiveConnection, CursorType, LockType, Options
  • Source. A Command object or an expression for a SQL statement, table, or stored procedure.
  • ActiveConnection. A Connection object or an expression for a connection string that might be used to make Connection object.
  • CursorType. ADO only allows four cursors. ADO also lets you decide the cursor location (via the CursorLocation property), but if a client-side cursor is chosen, then it has to be a Static cursor.
    • adOpenForwardOnly. 0, the default; it is like a forward-only static cursor.
    • adOpenKeyset. 1.
    • adOpenStatic. 3.
    • adOpenDynamic. 2.
  • LockType. ADO supports 4 kinds of record locking.
    • adLockReadOnly. 1, the default.
    • adLockPessimistic. 2, locks when editing begins.
    • adLockOptimistic. 3, locks when Update() method is used.
    • adLockBatchOptimistic. 4, locks when BatchUpdate() method is used.
  • Options. Multiple options can be selected which define what kind of command the Source parameter contains and how to execute the Recordset object.
    • adCmdUnspecified. -1.
    • adCmdText. 1.
    • adCmdTable. 2.
    • adCmdStoredProc. 4.
    • adCmdUnknown. 8. Tries modes 1, 2, and then 4.
    • adCmdFile. 256.
    • adCmdTableDirect. 512.
    • adAsyncExecute. 16, execute the statement following the Open() method without waiting for the method to complete.
    • adAsyncFetch. 32, execute the statement following the Open() method after the method has fetched the rows for the CacheSize property.
    • adAsyncFetchNonBlocking. 64, don't wait for rows while fetching.
    • adExecuteNoRecords. 128.
    • adOptionsUnspecified. -1.

Move Through Recordset

Here are some quick notes regarding moving through the rows in recordsets.

  • When a Recordset is opened, it goes to the first record unless there are none, in which case the BOF and EOF properties are True.
  • It is possible to move through forward-only cursors but then why did you make it forward-only in the first place?
  • Any attempts to move past the BOF or EOF will return an error.
  • Ordinal position shifts with additions and deletions but Bookmarks are constant until a Requery(), Resync(), or Close() method is called.
  • These move methods are self-explanatory: MoveFirst(), MoveLast(), MoveNext(), and MovePrevious().

Here are some Recordset properties that can move through a recordset:

  • AbsolutePage. The page the current recordset is in. Retrievable. Set this to move to the first row in a retrieved page. Set the PageSize property to determine the number of rows on a page.
  • AbsolutePosition. The ordinal position for a row. Retrievable. Set this to move to a retrieved value. It has these possible values:
    • 1 to RecordCount property.
    • adPosUnknown. -1, i.e. empty recordset, AbsolutePosition property unsupported, etc.
    • adPosBOF, -2.
    • adPosEOF, -3.
  • Bookmark. A system-generated value for a row. Retrievable. Set this to move to a retrieved value.
  • Filter. Makes a new subset recordset of the original recordset. Retrievable. Set to one of three possible types:
    • A criteria string similar to a SQL WHERE clause.
    • An array of bookmarks.
    • An ADO constant:
      • adFilterNone. 0.
      • adFilterPendingRecords. 1. In batch update mode, the rows that have changed but not batch updated yet.
      • adFilterAffectedRecords, 2. The rows affected by the last Delete(), Resync(), UpdateBatch(), or CancelBatch() method call.
      • adFilterFetchedRecords, 3. The rows in the current cache.

The Move() method has this syntax:

recordset.Move NumberOfRecords, start
  • NumberOfRecords. If the start parameter is not set, then this is a positive or negative number that indicates rows relative to the current row.
  • start. The row from which the move should occur.
    • adBookmarkCurrent. 0, the default.
    • adBookmarkFirst. 1.
    • adBookmarkLast. 2.

The Seek() method has this syntax:

recordset.Seek KeyValues, options
  • KeyValues. An array of variant values corresponding to the one or more columns that form the index of a recordset. Set the index column name(s) by setting the Index property.
  • options. The type of comparison between the KeyValues and the index.
    • adSeekFirstEQ. 1, the first key equal to the KeyValues .
    • adSeekLastEQ. 2, the last key equal to the KeyValues
    • adSeekAfterEQ. 4, a key equal to the KeyValues or just after it would have occurred.
    • adSeekAfter. 8, a key just after where the KeyValues would have occurred.
    • adSeekBeforeEQ. 16, a key equal to the KeyValues or just before it would have occurred.
    • adSeekBefore. 32, a key just before where the KeyValues would have occurred.

A typical VB parsing of a Recordset:

Do Until rst.EOF
    Response.Write "<option>" & rst.Fields("OrganizationName").Value & "</option>"
    If rst.Fields("OrganizationName").Value <> "Berwyn" Then Exit Do
    grst.MoveNext
Loop

Accessing Fields in Recordset

Now that you're on the right row, access the column(s) in the row. The syntax is as follows:

recordset.Fields(index).property
  • index. Either the field name or its ordinal position.
  • property. Any of the field properties.

This is essentially an ADO Field object.

These syntaxes are equivalent:

recordset.Fields("FieldName").Value
recordset.Fields(IndexNumber).Value
recordset(IndexNumber)
recordset!FieldName

At time it is advantageous to loop through the Fields collection. EG:

For i = 0 to rst1.Fields.Count - 1
    str1(i) = rst1.Fields(i).Name 'Column name
    str2(i) = rst1.Fields(i)      'Cell value
Next

Update Data in a Recordset

Now that you're on the right row, change some data in the fields and update the database with the new data.

This is usually performed by passing values to columns in rows and then either moving from the current row or using an update method (This assumes that the Recordset was not opened with adLockReadOnly locking.). EG:

rst1("FirstName") = "George"
rst1.Update()
rst1("LastName") = "George"
rst1.MoveNext()

If the Recordset was opened with adLockBatchOptimistic locking, then you may update batches of rows by using the UpdateBatch() method which has this syntax:

recordset.UpdateBatch AffectRecords
  • AffectRecords. The rows affected by the batch update.
    • adAffectAll. 3, if no Filter or Filter is not set to a string, then all rows, otherwise all visible rows.
    • adAffectChapters. 4, all rows.
    • adAffectCurret. 1, the current row.
    • adAffectGroup. 2, the visible rows.

If you have passed values to columns in rows but have not updated the data source yet you can cancel the changes so far. After a cancellation, you should make sure the recordset is on a known row.

recordset.CancelUpdate
record.fields.CancelUpdate
recordset.CancelBatch AffectRecords

If you have enclosed the whole affair in an ADO transaction, then you can rollback updates.

There can be a number of problems that turn up when updating rows:

  • A table used by the recordset may require some fields but those fields were not made part of the recordset. In this case, be sure to include all the fields in updates.
  • The recordset was made via a SQL command that used the JOIN clause on multiple tables. Where would the new row go? In this case, avoid joined tables.

Delete a Row in a Recordset

Now that you're on the right row, delete the row, and then move to a known row.

recordset.Delete AffectRecords

Note that if all you are doing is deleting a row in a table, eg deleting old rows in a log, then you may be better off just using the Connection.Execute() method.

Add Row to a Recordset

The process of adding a row to a recordset involves using the Recordset.AddNew() method which moves you to a new row (if you are currently editing a row when the method is called then that row may be updated!). From the new row you have to pass the appropriate values to the fields and then update the recordset.

recordset.AddNew FieldList, values
  • FieldList. Optional. A single field name or array of field names or array of ordinal positions.
  • values. Optional. A corresponding value or array of values.

Note that if all you are doing is adding a row to a table, eg adding to a log, then you may be better off just using the Connection.Execute() method.

Persist Data in a Recordset

As of ADO 2.1, the Recordset data can be persisted as an XML file.

recordset.Save(DestinationFilename, 1)
<%
Set fso = CreateObject("Scripting.FileSystemObject")
filename = Server.MapPath("my.xml")
If fso.FileExists(filename) Then
    fso.DeleteFile(filename)
End If
rst.Save(filename, 1)
%>

As of ADO 2.5, the Recordset data can be persisted as a stream and, hence, a MS XML DOM document.

<% recordset.Save(Response, 1) %>
recordset.Save(DestinationXMLDOMdoc, 1)
Dim xmldoc As New MSXML.DOMDocument
rst.Save(xmldoc, 1)

Close and Release Recordset

When you are finished with the recordset close it.

recordset.Close()

If you want to reuse the recordset, open it again, but otherwise release the object variable from memory.

Set recordset = Nothing

Properties, Methods, and Events

This table presents the Recordset methods by category in the first column and alphabetically in the second column.

Properties
  1. AbsolutePage
  2. AbsolutePosition
  3. ActiveCommand
  4. ActiveConnection
  5. BOF
  6. Bookmark
  7. CacheSize
  8. CursorLocation
  9. CursorType
  10. DataMember
  11. DataSource
  12. EditMode
  13. EOF
    Often listed as "BOF and EOF".
  1. Filter
  2. Index
  3. LockType
  4. MarshalOptions
  5. MaxRecords
  6. PageCount
  7. PageSize
  8. RecordCount
  9. Sort
  10. Source
  11. State
  12. Status
  13. StayInSync
Methods by Category Methods Alphabetically
  • Retrieving and Discarding Rows
    1. Open
    2. Clone
    3. Requery
    4. Resync
    5. Refresh
      This has mysteriously disappeared from the latest version of ADO.
    6. NextRecordset
    7. Close
  • Moving
    1. Move
    2. MoveFirst
    3. MoveLast
    4. MoveNext
    5. MovePrevious
    6. Find
    7. Seek
    8. CompareBookmarks
  • Updating
    1. AddNew
    2. Update
    3. CancelUpdate
    4. Delete
  • Batch Updating
    1. UpdateBatch
    2. CancelBatch
  • Miscellaneous
    1. GetRows
    2. Supports
    3. ConvertToString
    4. GetString
    5. Save
  1. AddNew
  2. Cancel
  3. CancelBatch
  4. CancelUpdate
  5. Clone
  6. Close
  7. CompareBookmarks
  8. Delete
  9. Find
  10. GetRows
  11. GetString
  12. Move
  13. MoveFirst
    Often listed as "MoveFirst, MoveLast, MoveNext, and MovePrevious".
  14. MoveLast
    Often listed as "MoveFirst, MoveLast, MoveNext, and MovePrevious".
  15. MoveNext
    Often listed as "MoveFirst, MoveLast, MoveNext, and MovePrevious".
  16. MovePrevious
    Often listed as "MoveFirst, MoveLast, MoveNext, and MovePrevious".
  17. NextRecordset. If command returns multiple record sets.
  18. Open
  19. Requery
  20. Resync
  21. Save
  22. Seek
  23. Supports
  24. Update
  25. UpdateBatch
Events
  1. EndOfRecordset
  2. FetchComplete
  3. FetchProgress
  4. FieldChangeComplete
    Often listed as "WillChangeField and FieldChangeComplete".
  5. MoveComplete
    Often listed as "WillMove and WillMove".
  6. RecordChangeComplete
    Often listed as "WillChangeRecord and RecordChangeComplete".
  7. RecordsetChangeComplete
    Often listed as "WillChangeRecordset and RecordsetChangeComplete".
  1. WillChangeField
  2. WillChangeRecord
  3. WillChangeRecordset
  4. WillMove


GeorgeHernandez.comSome rights reserved