ADO Recordset Object

Example

GetRows
This example demonstrates how to use the GetRows method.

Recordset object

The ADO Recordset object is used to contain a record set from a database table. A Recordset object consists of records and columns (fields).

In ADO, this object is the most important and most commonly used object for operating on database data.

ProgID

set objRecordset=Server.CreateObject("ADODB.recordset")

When you first open a Recordset, the current record pointer points to the first record, and the BOF and EOF properties are False. If there are no records, the BOF and EOF properties are True.

The Recordset object can support two types of updates:

  • Immediate update - Once the Update method is called, all changes are immediately written to the database.
  • Batch update - The provider caches multiple changes and then sends these changes to the database using the UpdateBatch method.

In ADO, there are 4 different cursor (pointer) types defined:

  • Dynamic cursor - Allows you to view additions, changes, and deletions made by other users
  • Keyset cursor - Similar to dynamic cursor, the difference is that you cannot view additions made by other users, and it prevents you from accessing records that have been deleted by other users. Changes to data made by other users are still visible.
  • Static cursor - Provides a static copy of the recordset that can be used to search for data or generate reports. In addition, additions, changes, and deletions made by other users will be invisible. This is the only cursor type allowed when opening a client Recordset object.
  • Forward-only cursor - Allows scrolling forward in the Recordset only. In addition, additions, changes, and deletions made by other users will be invisible.

The cursor type can be set through the CursorType property or the CursorType parameter in the Open method.

Note:Not all providers support all methods and properties of the Recordset object.

Attribute

Attribute Description
AbsolutePage Set or return a value that specifies the page number of the Recordset object.
AbsolutePosition Set or return a value that specifies the sequential position (numbered position) of the current record in the Recordset object.
ActiveCommand Return the Command object associated with the Recordset object.
ActiveConnection Set or return the definition of the connection if the connection is closed, or set or return the current Connection object if the connection is open.
BOF Return true if the current record position is before the first record, otherwise return false.
Bookmark Set or return a bookmark. This bookmark saves the position of the current record.
CacheSize Set or return the number of records that can be cached.
CursorLocation Set or return the location of the cursor service.
CursorType Set or return the cursor type of a Recordset object.
DataMember Set or return the name of the data member to be retrieved from the object referenced by the DataSource attribute.
DataSource Specify an object that contains the data to be represented as a Recordset object.
EditMode Return the edit state of the current record.
EOF Return true if the current record position is after the last record, otherwise return false.
Filter Return a filter for the data in the Recordset object.
Index Set or return the name of the current index of the Recordset object.
LockType Set or return a value that specifies the lock type when editing a record in the Recordset.
MarshalOptions Set or return a value that specifies which records are returned to the server.
MaxRecords Sets or returns the maximum number of records returned in a Recordset object from a query.
PageCount Returns the number of data pages in a Recordset object.
PageSize Sets or returns the maximum number of records allowed on a single page of the Recordset object.
RecordCount Returns the number of records in a Recordset object.
Sort Sets or returns a field name or names that serve as the sorting basis for the Recordset.
Source Sets a string value, or a Command object reference, or returns a string value that indicates the data source of the Recordset object.
State Returns a value that describes whether the Recordset object is open, closed, connecting, executing, or retrieving data.
Status Returns the current status of the record for batch updates or other large operations.
StayInSync Sets or returns whether the reference to child records changes when the position of the parent record changes.

Method

Method Description
AddNew Creates a new record.
Cancel Undoes an execution.
CancelBatch Undoes a batch update.
CancelUpdate Undoes the changes made to a single record in the Recordset object.
Clone Creates a copy of an existing Recordset.
Close Closes a Recordset.
CompareBookmarks Compares two bookmarks.
Delete Deletes a record or a group of records.
Find Searches for a single record in a Recordset that meets a specified condition.
GetRows Copies multiple records from a Recordset object to a two-dimensional array.
GetString Returns the Recordset as a string.
Move Moves the record pointer within the Recordset object.
MoveFirst Moves the record pointer to the first record.
MoveLast Moves the record pointer to the last record.
MoveNext Moves the record pointer to the next record.
MovePrevious Moves the record pointer to the previous record.
NextRecordset Clears the current Recordset object by executing a series of commands and returns the next Recordset.
Open Open a database element, which can provide access to table records, query results, or saved Recordset.
Requery Update the data in the Recordset object by re-executing the query on which the object is based.
Resync Refresh the data in the current Recordset from the original database.
Save Save the Recordset object to a file or Stream object.
Seek Search the index of the Recordset to quickly locate a row that matches the specified value and make it the current row.
Supports Returns a boolean value that defines whether the Recordset object supports a specific type of functionality.
Update Save all changes made to a single record in the Recordset object.
UpdateBatch Store all changes to the Recordset in the database. Use in batch update mode.

Event

Note: You cannot handle events using VBScript or JScript (only Visual Basic, Visual C++, and Visual J++ languages can handle events).

Event Description
EndOfRecordset Triggered when trying to move to a row beyond the end of the Recordset.
FetchComplete Triggered when all records in the asynchronous operation have been read.
FetchProgress Triggered periodically during asynchronous operations, reporting how many records have been read.
FieldChangeComplete Triggered when the value of a Field object changes.
MoveComplete Triggered after the current position in the Recordset changes.
RecordChangeComplete Triggered after a record changes.
RecordsetChangeComplete Triggered after the Recordset changes.
WillChangeField Triggered before the value of a Field object changes.
WillChangeRecord Triggered before a record changes.
WillChangeRecordset Triggered before the Recordset changes.
WillMove Triggered before the current position in the Recordset changes.

Collection

Collection Description
Fields Indicates the number of Field objects in this Recordset object.
Properties Contains all Property objects in the Recordset object.

Attributes of the Fields Collection

Attribute Description
Count

Return the number of items in the fields collection. Starting from 0.

Example:

	countfields = rs.Fields.Count
Item(named_item/number)

Return a specified item from the fields collection.

Example:

	itemfields = rs.Fields.Item(1)
	Or	
	itemfields = rs.Fields.Item("Name")

Attributes of the Properties Collection

Attribute Description
Count

Return the number of items in the properties collection. Starting from 0.

Example:

	countprop = rs.Properties.Count
Item(named_item/number)

Return a specified item from the properties collection.

Example:

	itemprop = rs.Properties.Item(1)
	Or
	itemprop = rs.Properties.Item("Name")