ADO Recordset Object
Example
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 commonly used for manipulating data in a database.
ProgID
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 supports two types of updates:
In ADO, four different cursor types are defined:
Dynamic Cursor - Allows you to view additions, changes, and deletions by other users.
Keyset Cursor - Similar to a dynamic cursor, except that you cannot view records added by other users, and it prevents you from accessing records deleted by other users. Changes made to data by other users are still visible.
Static Cursor - Provides a static copy of a recordset, which can be used for finding data or generating reports. Additionally, additions, changes, and deletions made by other users will be invisible. This is the only cursor type allowed when you open a client-side Recordset object.
Forward-Only Cursor - Allows scrolling forward only in the Recordset. Additionally, additions, changes, and deletions made by other users will be invisible.
The cursor type can be set via the CursorType property or the CursorType parameter in the Open method.
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 ordinal position of the current record in a Recordset object. |
| ActiveCommand | Returns the Command object associated with the Recordset object. |
| ActiveConnection | Sets or returns the connection definition if the connection is closed, or the current Connection object if the connection is open. |
| BOF | Returns true if the current record position is before the first record; otherwise, it returns false. |
| Bookmark | Sets or returns a bookmark that saves the current record position. |
| 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, it returns false. |
| Filter | Returns a filter for the data in a Recordset object. |
| Index | Sets or returns the name of the current index for a Recordset object. |
| LockType | Sets or returns a value that specifies the type of lock 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 returned from a query to a Recordset object. |
| 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, 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 status of the current record with respect to batch updates or other bulk operations. |
| StayInSync | Sets or returns whether the reference to the child records changes when the parent record position changes. |
Methods
| Method | Description |
|---|---|
| AddNew | Creates a new record. |
| Cancel | Cancels an execution. |
| CancelBatch | Cancel a batch update. |
| CancelUpdate | Cancel changes made to a single record in the Recordset object. |
| Clone | Create a duplicate 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 satisfies a specified condition. |
| GetRows | Copy multiple records from a Recordset object into a two-dimensional array. |
| GetString | Return the Recordset as a string. |
| Move | Move the record pointer within the 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, providing access to records in a table, query results, or a 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 underlying database. |
| Save | Save the Recordset object to a file or a Stream object. |
| Seek | Search the index of the Recordset to quickly locate the 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 the 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).
| Event | Description |
|---|---|
| EndOfRecordset | Triggered when an attempt is made to move to a row beyond the end of the Recordset. |
| FetchComplete | Triggered when all records have been read in an asynchronous operation. |
| FetchProgress | Triggered periodically during an asynchronous operation, reporting how many records have been read. |
| FieldChangeComplete | Triggered when the value of a Field object is changed. |
| MoveComplete | Triggered after the current position in the Recordset changes. |
| RecordChangeComplete | Triggered after a record is changed. |
| 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. |
Collections
| Collection | Description |
|---|---|
| Fields | Indicates the number of Field objects in the Recordset object. |
| Properties | Contains all Property objects in the Recordset object. |
Fields Collection Properties
| Property | Description |
|---|---|
| Count | Returns the number of items in the fields collection, starting from 0. Example: countfields = rs.Fields.Count |
| Item(named_item/number) | Returns a specific item in the fields collection. Example: itemfields = rs.Fields.Item(1) or itemfields = rs.Fields.Item("Name") |
Properties Collection Properties
| Property | Description |
|---|---|
| Count | Returns the number of items in the properties collection, starting from 0. Example: countprop = rs.Properties.Count |
| Item(named_item/number) | Returns a specific item from the properties collection. Example: itemprop = rs.Properties.Item(1) |
or
itemprop = rs.Properties.Item("Name") |