InformIT: Visual C++ 6 Unleashed > Enhanced ADO Recordset Functionality

Table of Contents

Enhanced ADO Recordset Functionality

In our example, we used basic ADO functionality that you should use in most of your ADO applications. However, ADO enables some advanced functionality that might be useful to you while developing your ADO application. This section covers some of this advanced functionality.

Limiting the Rows in a Recordset

You can limit the total number of records that will be returned into a recordset by setting the MaxRecords property of the recordset before calling its Open() method. Furthermore, you can set the number of rows that are cached locally in the recordset (as opposed to being kept in a cursor on the database server) by setting the CacheSize property of the recordset to the number of rows that you want to hold locally. Again, this property should be set before calling Open(). The value of the CacheSize property will not affect how your application code must be written—but, depending on your network environment, it can have a substantial effect on your app's overall performance.

Filtering Rows in the Recordset

When opening a recordset, you specified a command or query that generates a set of rows. You can limit the set of rows returned by setting the recordset's Filter property. The Filter property is basically an extension to the query (or table name, which generates a simple SQL query) given in the Source parameter of Open(). The string in Filter contains a WHERE clause used to limit the query contained in the source. Note, however, that the string you add to Filter should not contain the actual WHERE keyword. The value of the Filter property is also limited because you can use column names that are contained in the recordset only.

You also can set the value of the Filter property to one of the following constants, which limits the contents of the recordset to rows with a certain status:

adFilterNone cancels the current value of Filter and restores the contents of the recordset to the full result of the original query.
adFilterPendingRecords, in batch update mode, can be used to select only those rows that have changed but not yet been updated in the database.
adFilterAffectedRecords selects only rows that were affected by the last Delete(), Resync(), UpdateBatch(), or CancelBatch() operation.
adFilterFetchedRecords limits the rows in the recordset to those that are currently contained in the local cache.

You can also set the Filter property to an array of bookmark values, which limits the rows in the recordset to those referenced in the bookmark array.

Refreshing the Recordset

After you have opened a Recordset object, you can call its Requery() method to repeat the query and return a new result set. This is useful in situations wherein you suspect the underlying data in the database might have changed. Requery() will toss out all the rows currently in the recordset and execute the query again, using the current values of the Source and Filter properties.

Similarly, you can resynchronize the data in the recordset with the data in the datasource by calling the Resync() method of the recordset. This is different than the Requery() method because Resync() updates only the rows currently in the recordset, rather than executing the whole query again. The prototype for Requery() as generated by #import, is shown here:

HRESULT Resync ( enum AffectEnum AffectRecords );

You can pass any of the following values in the AffectRecords parameter:

adAffectCurrent refreshes only the current record.
adAffectGroup refreshes only the rows that satisfy the current Filter property.
adAffectAll refreshes all the rows contained in the recordset, including those that do not satisfy the current Filter setting.

`Move()`

There are four move commands (MoveFirst, MovePrevious, MoveNext, MoveLast) that were incorporated into our example. The ADO Recordset object also provides the Move() function, which gives a more flexible function for moving around in the recordset. The prototype for the Move() function, as generated by #import, is shown here:

HRESULT Move (
    long NumRecords,
    const _variant_t & Start = vtMissing );

The NumRecords parameter specifies the number of records that the current record pointer will move. If you pass a positive value of numRecords, the pointer moves forward. If you pass a negative value, the current row pointer moves backward. If you do not pass a Start parameter, the current row pointer moves from the current row. However, you can pass a bookmark value in Start. The current row is then moved NumRecords number of rows from the row specified by the bookmark.

If you attempt to move past the last row, EOF is set to True. Similarly, BOF is set to True if you attempt to move backward past the first row.

Absolute Positioning

ADO Recordset objects also enable you to set the current row to an absolute position within the recordset by setting the AbsolutePosition property of the recordset to the number of the desired current rows. The rows in the recordset are numbered from one to the number of rows in the recordset.

Scrolling by Pages

The ADO Recordset object also enables you to scroll through the recordset by pages. This is very useful in applications in which you are displaying data to the user by pages. To use page scrolling, you first need to set the PageSize property of the recordset to the number of rows that you would like to see in a page. You can then find the number of pages contained in the recordset by looking at the PageCount property. To set the current row to the first row in a logical page, simply set the AbsolutePage property to the number of the page that you want to position the current row on. Like absolute rows, pages are numbered starting with one.

Using Bookmarks

If the recordset that you are using supports bookmarks—as indicated by a call to Recordset.Supports()—you can use bookmark values to make a particular row the current row. You can retrieve the bookmark value for the current row by reading the Bookmark property of the recordset. If you save this value somewhere, you can later set the Bookmark property of the recordset to this value to again make the row indicated by the bookmark the current row.

Executing Commands | Next Section

Previous Section