ADO RecordsetObject

Example

GetRows
This example demonstrates how to use the GetRows method.

Recordset Object

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

In ADO, this object is the most important and the most frequently used object to operate on data in a database.

ProgID

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

When you first open a Recordset, the current record pointer will point 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 - As soon as you call the Update method, all changes are written immediately to the database. Batch update - The provider caches multiple changes and then uses the UpdateBatch method to transfer these changes to the database.

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

  • Dynamic cursor - Allows you to view additions, changes, and deletions made by other users.
  • Keyset cursor - Similar to a dynamic cursor, except that you cannot view additions made by other users, and it prevents you from accessing records that other users have deleted. Data changes made by other users are still visible.
  • Static cursor - Provides a static copy of the recordset, which can be used to find data or generate reports. In addition, additions, changes, and deletions made by other users will not be visible. When you open a client-side Recordset object, this is the only allowed cursor type.
  • Forward-only cursor - Only allows scrolling forward in the Recordset. In addition, additions, changes, and deletions made by other users will not be visible.

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

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

Properties

Property Description
AbsolutePage Sets or returns a value that specifies the page number in a Recordset object.
AbsolutePosition Sets or returns a value that specifies the sequential position (ordinal position) of the current record in a Recordset object.
ActiveCommand Returns the Command object associated with the Recordset object.
ActiveConnection If the connection is closed, sets or returns the connection definition; if the connection is open, sets or returns the current Connection object.
BOF Returns true if the current record position is before the first record; otherwise, returns false.
Bookmark Sets or returns a bookmark. This bookmark saves the position of the current record.
CacheSize Sets or returns the number of records that can be cached.
CursorLocation Sets or returns the location of the cursor service.
CursorType Sets or returns the cursor type of a Recordset object.
DataMember Sets or returns the name of the data member to be retrieved from the object referenced by the DataSource property.
DataSource Specifies an object that contains data to be represented as a Recordset object.
EditMode Returns the edit status of the current record.
EOF Returns true if the current record position is after the last record; otherwise, returns false.
Filter Returns a filter for the data in a Recordset object.
Index Sets or returns the name of the current index of a Recordset object.
LockType Sets or returns a value that specifies the locking type when editing a record in a Recordset.
MarshalOptions Sets or returns a value that specifies which records are returned to the server.
MaxRecords Sets or returns the maximum number of records to return to 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 a Recordset object.
RecordCount Returns the number of records in a Recordset object.
Sort Sets or returns one or more field names on which the Recordset is sorted.
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 fetching data.
Status Returns the status of the current record with regard to batch updates or other bulk operations.
StayInSync Sets or returns whether the reference to child records changes when the parent record position changes.

Methods

Methods Description
AddNew Creates a new record.
Cancel Cancels an execution.
CancelBatch Cancels a batch update.
CancelUpdate Undo changes made to a record in a Recordset object.
Clone Create a copy of an existing Recordset.
Close Close a Recordset.
CompareBookmarks Compare two bookmarks.
Delete Delete a record or a group of records.
Find Search for a record in a Recordset that meets a specified condition.
GetRows Copy multiple records from a Recordset object to a two-dimensional array.
GetString Return the Recordset as a string.
Move Move the record pointer in a Recordset object.
MoveFirst Move the record pointer to the first record.
MoveLast Move the record pointer to the last record.
MoveNext Move the record pointer to the next record.
MovePrevious Move the record pointer to the previous record.
NextRecordset Clear the current Recordset object and return the next Recordset by executing a series of commands.
Open Open a database element that provides access to table records, query results, or a saved Recordset.
Requery Update data in a 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 Return 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 a Recordset object.
UpdateBatch Save all changes in the Recordset to the database. Use in batch update mode.

Events

Note: You cannot use VBScript or JScript to handle events (only Visual Basic, Visual C++, and Visual J++ languages are allowed to handle events).

Events Description
EndOfRecordset Triggered when attempting to move past the end of the Recordset.
FetchComplete Triggered after all records in an asynchronous operation have been read.
FetchProgress Triggered periodically during an asynchronous operation, 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 is changed.
RecordsetChangeComplete Triggered after a Recordset is changed.
WillChangeField Triggered before the value of a Field object changes.
WillChangeRecord Triggered before a record is changed.
WillChangeRecordset Triggered before the Recordset changes.
WillMove Triggered before the current position in the Recordset changes.

Collections

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

Properties of the Fields Collection

Attribute Description
Count

Returns the number of items in the fields collection. Starts at 0.

Example:

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

Returns a specified item in the fields collection.

Example:

itemfields = rs.Fields.Item(1)
或者    
itemfields = rs.Fields.Item("Name")

Properties of the Properties Collection

attribute Description
Count

Returns the number of items in the properties collection. Starts at 0.

Example:

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

Returns a specified item in the properties collection.

Example:

itemprop = rs.Properties.Item(1)
或者
itemprop = rs.Properties.Item("Name")
Other extensions.