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 

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

Move Through Recordset

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

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

The Move() method has this syntax:

recordset.Move NumberOfRecords, start

The Seek() method has this syntax:

recordset.Seek KeyValues, options

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

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

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:

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

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

Page Modified: (Hand noted: 2008-04-29 21:22:21Z) (Auto noted: 2010-12-24 22:50:38Z)