Provides access to the result set of a query or other database operation
| Dim rs As New ADODB.Recordset |
Recordset - Properties
AbsolutePage, AbsolutePosition,
BOF, EOF, Filter, PageSize
- AbsolutePage [RW]
- Identifies the page number on which the current record is located.
Changing this makes the first record in the indicated page the current record.
>PageSize
- AbsolutePosition [RW]
- The ordinal position of the current record. AbsolutePosition will return 1..RecordCount
- or, if there is no current record, one of these values:
| adPosUnknown |
-1 |
The Recordset is empty (or the provider does not support absolute position) |
| adPosBOF |
-2 |
BOF is true |
| adPosEOF |
-3 |
EOF is true |
- ActiveCommand
- The Command object that created a Recordset object
- BOF
- 'Beginning Of File' - indicates that the current record position is before the first
record
>EOF
- Bookmark
- Returns a system-generated value that uniquely identifies the current record. Setting
this property to a saved value restores the corresponding record position.
- CacheSize
- The number of records in a Recordset that are cached locally in memory
- CursorLocation
- Sets or returns the location of the cursor - client (web server) or server (database
server).
Various cursor types provide different features for processing a Recordset.
- CursorType
- Controls whether an open Recordset will reflect database changes made by other users.
- EditMode
- Indicates whether changes to the current Recordset are in progress
- EOF
- 'End Of File' - indicates that the current record position is after the last record
rs.MoveFirst
Do While Not rs.EOF
---
rs.MoveNext
Loop
|
- >BOF, MoveFirst, MoveNext
- Filter
- Selectively screens out records in a Recordset - creating a new subset. Restricts
viewing to records satisfying given criteria.
Changing the Filter property changes the current record position to the first record in
the filtered result set.
| adFilterNone |
0 |
Removes the current filter and restores all original records to view |
| afFilterPendingRecords |
1 |
Provides access only to records that have changed but have not yet been batch updated. |
| adFilterAffectedRecords |
2 |
Provides access only to records affected by the last Delete, Resync, UpdateBatch or CancelBatch call. |
| adFilterFetchedRecords |
3 |
Provides access only to records in the current cache (the results of the last call to
retrieve records from the database). |
- NB: in general, it is more efficient to do any filtering in the SQL statement that
creates the result set.
- Index
- Identifies the index currently in effect for the Recordset
- LockType
- Controls how the database will lock records (stop other users from updating them) while
your code has an update in progress.
- MarshalOptions
- Controls which records from a client-side Recordset get written back to the server
(Remote Data Service only).
- MaxRecords
- Limits the number of records the provider returns from the data source
- PageCount
- The number of pages of data currently in a Recordset
- PageSize
- The number of records contained in one page in the Recordset.
>AbsolutePage
- RecordCount
- The current number of records in the Recordset
- Sort
- Indicates one ore more field names on which tje Recordset is sorted - ascending or
descending.
- Source
- The source of the data in the Recordset: a Command object, a SQL statement, a Table
name, or a stored Procedure name.
- State
- The current state of the Recordset - open or closed.
- Status
- The status of the current record with respect to batch updates or other bulk operations.
- StayInSync
- Indicates, in a hierachical Recordset object, whether the reference to the underlying
child records changes when the parent row position changes.
Recordset - Methods
AddNew, CancelBatch, CancelUpdate, Close, ConvertToString, Delete, Find,
GetRows, GetString, Move,
MoveFirst, MoveLast, MoveNext,
MovePrevious, NextRecordSet, Open, Refresh, Requery, Resync, Save, Seek, Supports, Update, UpdateBatch
- AddNew
- Creates a new blank record that's ready to accept field values.
>Delete, Update
- CancelBatch
- Cancels a pending batch update.
>UpdateBatch
- CancelUpdate
- Cancels any changes made to the current record.
>Update
- Close
- Closes an open Recordset and any dependant objects
- >Open
- ConvertToString
- Converts a Recordset to a MIME string that represents the Recordset data.
- Delete
- Deletes the current record or a group of records.
>AddNew
- Find
- Makes the record that satisfies a specific condition the current record.
>Move, Seek
- GetRows
- Retrieves multiple records of a Recordset into an array.
- GetString
- Returns the Recordset as a string
- Move
- Moves the current record position forward or backward a specified number of records.
If the Recordset is empty, any move will generate an error (both BOF
and EOF are true).
| rs.Move NumRecords[, Start ] |
| NumRecords |
|
the number of records to move - positive (forward) or negative (backward). |
| Start |
|
where to start: any bookmark or a value:
| adBookmarkCurrent |
|
0 |
|
(default) Start at the current record |
| adBookmarkFirst |
|
1 |
|
Start at the first record |
| adBookmarkLast |
|
2 |
|
start at the last record |
|
|
- >Find, Seek
- MoveFirst
- Makes the first record in a Recordset the current record.
>MoveNext
- MoveLast
- Makes the last record in a Recordset the current record.
- MoveNext
- Makes the next record in a Recordset the current record.
NB: MoveNext when EOF is true generates an error.
rs.MoveFirst
Do While Not rs.EOF
---
rs.MoveNext
Loop
|
- MovePrevious
- Makes the previous record in a Recordset the current record.
This will generate an error if the recordset is opened as adOpenForwardOnly
NB: MovePrevious when BOF is true generates an error.
rs.MoveLast
Do While Not rs.BOF
---
rs.MovePrevious
Loop
|
- NextRecordSet
- Clears the Recordset object, tells the data provider to run the next command in its
list, and makes the result set available.
- Open
- Obtains a result set from the data provider and makes it available for access.
NB: all constants are defined in ADOVBS.INC - >ADO files
| rs.Open Source, ActiveConnection, CursorType,
LockType, Options |
| Source |
|
Any expression that evaluates to a valid SQL statement, Table name, Command object, or
stored Procedure name.
| sql = "SELECT * FROM members" |
|
| ActiveConnection |
|
Any expression that evaluates to a valid connection string
- or - the name of a Connection object. |
| CursorType |
|
The type of cursor the provider should use.
| adOpenForwardOnly |
|
0 |
|
Forward-only cursor (default) - on a 'static' recordset (changes,
deletions and additions made by other users are not reflected). |
| adOpenKeyset |
|
1 |
|
Keyset cursor - Changes and deletions (but not additions) made by
other users are reflected. All types og movement are allowed. |
| adOpenDynamic |
|
2 |
|
Dynamic cursor - Changes, deletions and additions are reflected - All
types of movement are allowed. |
| adOpenStatic |
|
3 |
|
Static cursor - No changes are reflected - All types of movement are
allowed. |
|
| LockType |
|
| adLockReadOnly |
|
1 |
|
Read-Only (default) - data cannot be altered. |
| adLockPessimistic |
|
2 |
|
Pessimistic Locking, Record by Record - when you start editing a
record, the provider locks it. If the record is already in use, the statement that began
the edit will fail. |
| adLockOptimistic |
|
3 |
|
Optimistic Locking, Record by Record - the provider does not lock
records until your code calls the recordset's Update method. If the record is already in
use, the statement attempting the update will fail. |
| adLockBatchOptimistic |
|
4 |
|
Optimistic Batch Updates - ADO stores all updates, and then applies
them when your code calls the recordset's BatchUpdate method. If any record in the batch
is already in use, the statement attempting the update will fail. |
|
| Options |
|
Specifies the type of object to expect as the Source argument
- and how the Open statement is executed
adCmdUnspecified
adOptionUnspecified |
|
-1 |
|
Unspecified |
| adCmdText |
|
1 |
|
the Source argument is a text-definiton of a command. >adExecuteNoRecords |
| adCmdTable |
|
2 |
|
the Source argument is a table name |
| adCmdStoredProc |
|
4 |
|
the Source argument is the name of a stored procedure. >adExecuteNoRecords |
| adCmdUnknown |
|
8 |
|
the type of command in the Source argument is unknown. ADO will try using
types 1, 2 and 4 |
| adAsyncExecute |
|
16 |
|
The command should execute asynchronously: the statement following the Open statement
can execute without waiting for the Open statement to complete. |
| adAsyncFetch |
|
32 |
|
After retrieving the number of rows specified by the CacheSize property, the provider
should retrieve remaining rows asynchronously. |
| adAsyncFetchNonBlocking |
|
64 |
|
The main thread should never block while retrieving. If a requested row has not been
retrieved, the current row moves to the end of the file. |
| adExecuteNoRecords |
|
128 |
|
The command text is a command that does not return rows. Any retrieved rows are
discarded. This applies to adCmdText and adCmdStoredProc. |
| adCmdFile |
|
256 |
|
the Source argument is the file name of a stored Recordset |
| adCmdTableDirect |
|
512 |
|
the Source argument is a table name whose columns are all returned |
|
|
sql = "SELECT jersey, fname, sname, position " & _
"FROM roster " & _
"ORDER BY jersey;"
rs.Open sql, cn, 3, 3
|
| rs.Open sql, cn, adOpenForwardOnly, adLockReadOnly,
adCmdText |
- >Close
- Refresh
- Updates the objects in a collection to reflect objects available from, and specific to,
the provider.
- Requery
- Updates the data in a Recordset by reexecuting the query that created it.
- Resync
- Refreshes the data in a Recordset from the underlying database.
- Save
- Saves the Recordset in a file or a Stream object.
- Seek
- Searches a recordset's index to locate the row that matches a specified condition.
>Find, Move
- Supports
- Determines whether a specified Recordset supports a particular type of functionality.
- Update
- Saves any changes made to the current record.
>AddNew, CancelUpdate
- UpdateBatch
- Writes all pending batch updates to disk ('transaction processing').
>CancelBatch
ASP: ADODB.Recordset