The Recordset Object

< Day Day Up >

When a database request returns rows to your program, they are made available through the Recordset object.

Key Properties and Methods of the Recordset Object

Table 23-8 lists some of the key properties and methods associated with the Recordset object.

Table 23-8: Key Properties and Methods of the *Recordse*t Object
Property/Method	Description
ActiveConnection	Property: points to an open Connection object that will be used to execute the command or contains a connection string that will be used to connect to the database.
AddNew	Method: adds a new row to the end of the Recordset.
BOF	Property: returns True when the current record pointer is before the first record in the recordset.
CancelUpdate	Method: restores the current row in the Recordset to its original values.
Close	Method: closes an open Recordset object and releases all its resources.
Delete	Method: deletes the current record.
EditMode	Property: describes the edit status of the current record. Can be adEditNone (current record is unchanged), adEditInProgress (current record has been changed, but not saved), adEditAdd (the AddNew method created a new, empty record, which hasn't been saved yet), or adEditDelete (the current record is deleted).
EOF	Property: returns True when the current record pointer is past the last row in the recordset.
Fields	Property: returns a Fields collection containing the set of fields associated with the current record.
Filter	Property: can be a string containing an expression similar to an SQL Where clause.
Move(NumRecords)	Method: moves the current record pointer forward (positive value) or backward (negative value) the specified number of rows.
MoveFirst	Method: moves the current record pointer to the first row in the Recordset.
MoveLast	Method: moves the current record pointer to the last row in the Recordset.
MoveNext	Method: moves the current record pointer to the next row in the recordset.
MovePrevious	Method: moves the current record pointer to the previous row in the recordset.
Open	Method: gains access to the data in the Recordset.
RecordCount	Property: contains the total number of rows retrieved from the database.
Sort	Property: contains the list of column names on which the results should be sorted.
Source	Property: contains either an object reference to a Command object or a string with an SQL statement.
Update	Method: saves any changes you might have made to the current row to the database.

The ActiveConnection property points either to the Connection object associated with Recordset, or it contains a connection string value. If the property contains a connection string, a connection to the database will be automatically opened when the Recordset object is opened.

The Source property contains either an object reference to a Command object or a string containing the name of a table, a stored procedure or an SQL statement. If you assign a Command object to the source property, reading the source property will return the CommandText value from the Command object rather than an object reference.

Once you've set up all the information that you need to retrieve data from the database, use the Open method to gain access to the information in the Recordset object. When you are through with the data, use the Close method to release all the resources associated with the Recordset object.

The Recordset object exposes the returned records from the database one row at a time. Internally, the Recordset object maintains a pointer referred to as the current record pointer, which points to the current record.

Because only one record is available at a time, you must use the move methods (MoveFirst, MovePrevious, MoveNext, and MoveLast) to navigate your way through the records in the Recordset. The Move method allows you to jump forward or backward in the record the specified number of rows.

As you move through the records in the Recordset, the BOF and EOF properties will automatically be set as the current record pointer changes. BOF stands for Beginning Of File, and EOF stands for End Of File. The best way to think of these two properties is that they represent locations before the first row in the Recordset (BOF) and after the last row in the Recordset (EOF).

When you initially open the Recordset object, the current record pointer will point to the first row. Calling the MoveNext method will move the current record pointer through each of the rows, up to and including the last row. If the current record pointer is on the last row, calling MoveNext will set the EOF property to True. If you call MoveNext a second time, although the EOF property is True, you'll get an error.

Tip

The Ends of the Earth
Never call the MoveNext method when the EOF property is True, and never call the MovePrevious method when the BOF property is True. Calling either of these methods will result in an error because it's impossible to move the current record pointer before the beginning of the Recordset (MovePrevious) or move the current record pointer after the end of the Recordset (MoveNext).

The RecordCount property contains the total number of rows retrieved from the database. Remember that this property may have a value of -1, meaning that the total isn't known at the current time. If this happens, use the MoveLast method to move to the end of the Recordset. Using the MoveLast method will also update the RecordCount property.

The Filter property can be used for several different purposes. The most useful is when you assign it a string value containing an expression similar to one you'd use in an SQL Where clause. This technique can be useful if you wish to restrict the Recordset to only a subset of the data you just retrieved.

Tip

Only Take What You Need
Although you can use the Filter property to view a subset of the rows returned from the database, it's always better to retrieve only those rows you really want to process from the database.

The Sort property is essentially the same as the SQL Select statement's Order By clause. Simply assign a list of column names followed by an optional Asc keyword or a Desc keyword and then a comma, and ADO will sort the rows in the Recordset and return them to your program in the desired order.

You can access each individual column through the Fields collection. You can extract the current value for the column through a Field object as well as by saving a new value into the column in the current row. Changing any of the values forces the EditMode property to change from adEditNone to adEditInProgress. Once you are finished making changes, you should call the Update method to save the changes to the database.

The first step in writing data to a table is to make sure the EditMode property is set to adEditAdd. Then you can use the AddNew method to insert a blank row at the end of the recordset, into which you can write each column's value using the values in the Fields collection. Finally, you use the Update method to save the changes to the database.

The Delete method deletes the current row and sets the EditMode property to adEditDelete. Again, you need to use the Update method to make the changes final.

One nice feature of the Recordset object is the CancelUpdate method. If you have made any changes to the current row and call CancelUpdate, all the changes will be discarded and the original values would be restored. If you had added a new row using the AddNew method, the new row will be discarded and the current record pointer will be reset to point to the row it was pointing to prior to calling AddNew.

Using the Fields Collection

The Fields collection contains information about the columns in the current row of the Recordset object. (See Table 23-9.)

Table 23-9: Key Properties of the *Fields* Collection
Property/Method	Description
Count	Property: returns the number of items in the collection.
Item(index)	Property: returns the Field object at the location specified by index.

For the most part, you'll use the Fields collection simply to access the various Field objects associated with the current row. The other properties and methods in the Fields collection exist to add and remove Field objects from the collection and aren't critical to the average database programmer.

Using the Field Object

The Field object contains information about a single column associated with the current row of the Recordset object. Some of the most useful properties are listed in Table 23-10.

Table 23-10: Key Properties of the *Field* Object
Property	Description
Name	Contains the name of the database column.
OriginalValue	Contains the original value of the field from the database.
Type	Contains the OLE DB data type of the column. Some common values are adSmallInt, adInteger, asSingle, adDouble, adCurrency, adDate, adBSTR, adBoolean, adDecimal, adBigInt, adBinary, adChar, adWChar, adNumeric, adDBDate, adDBTime, adVarNumeric, adVarChar, adLongVarChar, adVarWChar, adLongVarWChar, adVarBinary, and asLongVarBinary.
UnderlyingValue	Contain the current value of the column as found in the database.
Value	Contain the current value of the field.

The Name property contains the name of the column as it was defined in the database or the Select statement that returned the rows. The Type property describes the OLE DB type of the column.

There are several different properties containing the value of the column in the Field object. The Value property represents the current value of the column as seen by the application program. If your program doesn't explicitly change the Value property, it will have the same value as found in the OriginalValue property. The OriginalValue property always contains the value of the column when it was originally retrieved from the database.

On the other hand, the UnderlyingValue property contains the most recent value of the column stored in the database. Because it's possible that the database has been updated since the original Recordset object was created, you can use the UnderlyingValue property to go back to the database to get a fresh copy of the column.

Note

Use the UnderlyingValue property carefully; it forces ADO to go back to the database to get the most current value. Although it's OK to do this every now and then, performing this action repetitively can have an adverse impact on your application's performance.

In this chapter, you learned about the key objects that comprise the ADO architecture. Each of three main objects performs a specific task. The Connection object contains the information necessary for your application to communicate with a database server. The Connection object also contains the Errors collection, which describes the most recent error encountered while using ADO. The Command object describes an SQL statement that will be executed, including the collection of Parameter objects that will be passed to the command for execution. Finally, the Recordset object provides access to data that may be returned by executing a Command object, with the individual values associated with each returned row being exposed through the Fields collection and each individual column being exposed through the Field object.

< Day Day Up >