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.
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
- Open Recordset
- Move Through Recordset
- Now that you're where you want:
- Accessing Fields in Recordset
- Update Data in a Recordset
- Delete a Row in a Recordset
- Add Row to a Recordset
- Persist Data in a Recordset
- Close and Release 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")
rstObject1 = new ActiveXObject("ADODB.Recordset");
The basic syntax for opening a Recordset is as follows:
recordset.Open Source, ActiveConnection, CursorType, LockType, Options
Source. A Command object or an expression for a SQL statement, table, or stored procedure.
ActiveConnection. A Connection object or an expression for a connection string that might be used to make Connection object.
CursorType. ADO only allows four cursors. ADO also lets you decide the cursor location (via the CursorLocation property), but if a client-side cursor is chosen, then it has to be a Static cursor.
- adOpenForwardOnly. 0, the default; it is like a forward-only static cursor.
- adOpenKeyset. 1.
- adOpenStatic. 3.
- adOpenDynamic. 2.
LockType. ADO supports 4 kinds of record locking.
- adLockReadOnly. 1, the default.
- adLockPessimistic. 2, locks when editing begins.
- adLockOptimistic. 3, locks when Update() method is used.
- adLockBatchOptimistic. 4, locks when BatchUpdate() method is used.
Options. Multiple options can be selected which define what kind of command the
Sourceparameter contains and how to execute the Recordset object.
- adCmdUnspecified. -1.
- adCmdText. 1.
- adCmdTable. 2.
- adCmdStoredProc. 4.
- adCmdUnknown. 8. Tries modes 1, 2, and then 4.
- adCmdFile. 256.
- adCmdTableDirect. 512.
- adAsyncExecute. 16, execute the statement following the Open() method without waiting for the method to complete.
- adAsyncFetch. 32, execute the statement following the Open() method after the method has fetched the rows for the CacheSize property.
- adAsyncFetchNonBlocking. 64, don't wait for rows while fetching.
- adExecuteNoRecords. 128.
- adOptionsUnspecified. -1.
Move Through Recordset
Here are some quick notes regarding moving through the rows in recordsets.
- When a Recordset is opened, it goes to the first record unless there are none, in which case the BOF and EOF properties are True.
- It is possible to move through forward-only cursors but then why did you make it forward-only in the first place?
- Any attempts to move past the BOF or EOF will return an error.
- Ordinal position shifts with additions and deletions but Bookmarks are constant until a Requery(), Resync(), or Close() method is called.
- These move methods are self-explanatory: MoveFirst(), MoveLast(), MoveNext(), and MovePrevious().
Here are some Recordset properties that can move through a recordset:
- AbsolutePage. The page the current recordset is in. Retrievable. Set this to move to the first row in a retrieved page. Set the PageSize property to determine the number of rows on a page.
AbsolutePosition. The ordinal position for a row. Retrievable. Set this to move to a retrieved value. It has these possible values:
- 1 to RecordCount property.
- adPosUnknown. -1, i.e. empty recordset, AbsolutePosition property unsupported, etc.
- adPosBOF, -2.
- adPosEOF, -3.
- Bookmark. A system-generated value for a row. Retrievable. Set this to move to a retrieved value.
Filter. Makes a new subset recordset of the original recordset. Retrievable. Set to one of three possible types:
- A criteria string similar to a SQL WHERE clause.
- An array of bookmarks.
An ADO constant:
- adFilterNone. 0.
- adFilterPendingRecords. 1. In batch update mode, the rows that have changed but not batch updated yet.
- adFilterAffectedRecords, 2. The rows affected by the last Delete(), Resync(), UpdateBatch(), or CancelBatch() method call.
- adFilterFetchedRecords, 3. The rows in the current cache.
The Move() method has this syntax:
recordset.Move NumberOfRecords, start
NumberOfRecords. If the
startparameter is not set, then this is a positive or negative number that indicates rows relative to the current row.
start. The row from which the move should occur.
- adBookmarkCurrent. 0, the default.
- adBookmarkFirst. 1.
- adBookmarkLast. 2.
The Seek() method has this syntax:
recordset.Seek KeyValues, options
KeyValues. An array of variant values corresponding to the one or more columns that form the index of a recordset. Set the index column name(s) by setting the Index property.
options. The type of comparison between the
KeyValuesand the index.
adSeekFirstEQ. 1, the first key equal to the
adSeekLastEQ. 2, the last key equal to the
adSeekAfterEQ. 4, a key equal to the
KeyValuesor just after it would have occurred.
adSeekAfter. 8, a key just after where the
KeyValueswould have occurred.
adSeekBeforeEQ. 16, a key equal to the
KeyValuesor just before it would have occurred.
adSeekBefore. 32, a key just before where the
KeyValueswould have occurred.
- adSeekFirstEQ. 1, the first key equal to the
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:
index. Either the field name or its ordinal position.
property. Any of the field properties.
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:
AffectRecords. The rows affected by the batch update.
- adAffectAll. 3, if no Filter or Filter is not set to a string, then all rows, otherwise all visible rows.
- adAffectChapters. 4, all rows.
- adAffectCurret. 1, the current row.
- adAffectGroup. 2, the visible rows.
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.
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:
- A table used by the recordset may require some fields but those fields were not made part of the recordset. In this case, be sure to include all the fields in updates.
- The recordset was made via a SQL command that used the JOIN clause on multiple tables. Where would the new row go? In this case, avoid joined tables.
Delete a Row in a Recordset
Now that you're on the right row, delete the row, and then move to a known row.
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
FieldList. Optional. A single field name or array of field names or array of ordinal positions.
values. Optional. A corresponding value or array of 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.
<% 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) %>
Dim xmldoc As New MSXML.DOMDocument rst.Save(xmldoc, 1)
Close and Release Recordset
When you are finished with the recordset close it.
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.
|Methods by Category||Methods Alphabetically|