RecordCount and Bookmarks

[Previous] [Next]

When displaying data, many developers want to do two things as soon as they open their Recordset—display the results in a grid and display the number of records returned by the query. Depending on the cursor type, you might not be able to do either. You might find that the Recordset object's RecordCount property returns -1 and that you get an error when you try to bind your Recordset to your grid.

Why Does the RecordCount Property Return -1?

Not all cursors provide the functionality required to report how many records they contain. While developers who have a lot of experience with cursors might find this obvious, others are initially amazed—they find this behavior absurd. An ADO Recordset is unable to provide a definite value for RecordCount when using forward-only or dynamic cursors, while static and keyset cursors have a well-defined RecordCount.

With a static cursor, the data remains the same; changes made by other users will not be visible. If you scroll back and forth through the cursor, you'll always see the same number of records unless you're using a client-side cursor, in which case you're the only one adding or removing records. For this reason there is a well-defined number of records in a static cursor at all times. While keyset cursors allow you to see changes made by other users to the records that make up the cursor, membership in the cursor is still static. Thus, keyset cursors also contain a well-defined number of records.

Forward-only cursors are more concerned with performance than with functionality. You generally ask for data in a forward-only cursor if you want to retrieve data as quickly as possible. Forward-only cursors do not support a record count and the RecordCount property is set to -1 to reflect this. If you need to know how many records the query returned with a forward-only cursor, count the records as you fetch them. Alternatively, most database systems support a COUNT function that you can use to simply return the number of records retrieved by a query.

 strSQL = "SELECT Count(CustomerID) FROM Customers" Set rsNumCustomers = cnDatabase.Execute(strSQL, , adCmdText) lngNumCustomers = rsNumCustomers(0).Value rsNumCustomers.Close 

Keep in mind that between the time you issue the query to retrieve the record count and the time you issue the query to retrieve the contents of the Customers table, the number of records in the table might have changed.

Dynamic cursors are geared more toward functionality than performance, but a dynamic cursor never contains a definite number of records. As discussed earlier, you can move to the first record of a dynamic cursor and examine each record to the end of the cursor in order to count them, but there's no guarantee that you'll find the same number of records if you perform that operation again. With a dynamic cursor, the query processor examines the data in the database to determine which records satisfy the query's criteria each time ADO requests more data to fill its cache. For this reason, when using a dynamic cursor, the RecordCount property returns -1 rather than reporting potentially misleading information.

What Do You Mean, "The rowset is not bookmarkable"?

This common but somewhat confusing error message might be familiar to some programmers. Let's talk about why this error message occurs and what you can do when you encounter it.

When envisioning the results of a query, many developers automatically imagine a nice, orderly grid of data. Grids are a great way to represent data, but only to a point. Not all grids are created equal. In Visual Basic 6, for example, the Hierarchical FlexGrid control can display the contents of some Recordsets that the DataGrid control cannot. If you try to bind the Hierarchical FlexGrid to a forward-only cursor, the grid will display the contents of the Recordset, while if you try to bind the DataGrid to a forward-only cursor, the grid will report an error saying, "The rowset is not bookmarkable."

The Hierarchical FlexGrid reads the contents of the Recordset into its own area of memory and displays that information in the grid. It's not really a bound control in the traditional sense. When you click on a particular record in the grid, you're not navigating to that record in the Recordset object. Once the Hierarchical FlexGrid reads the contents of the Recordset into its own internal data structures, it no longer interacts with the Recordset object. The grid is unaware of changes made to the Recordset, and vice versa.

With the DataGrid, on the other hand, clicking on a particular row in the grid automatically moves you to that row in the Recordset object to which the DataGrid is bound. While this may not seem impressive, it requires some functionality from the Recordset that's not supported for all cursors: the bookmark.

Think of a bookmark in the traditional sense—as a placeholder you put in a book to mark the page you want to return to later. You assume that the page you return to will be identical to the page you bookmarked. A bookmark in a cursor provides similar functionality. When you click on a record in the DataGrid, there must be some way of uniquely identifying each record in the cursor so that you can be sure you're moving to the correct record in the Recordset.

It's not enough to say, "Move to the fourth record in the cursor" if the membership in the cursor is not fixed (as in a dynamic cursor) or if the records in the cursor can be reordered (by changing the Sort property on a client-side Recordset). The records in the cursor must be uniquely identified in order to support bookmarks. This functionality is supported by the same cursor types that support the RecordCount property—static and keyset—since their membership is fixed.

Forward-only cursors cannot support bookmarks; since there's no way to move back to a particular record in a forward-only cursor, a bookmark serves no purpose. Dynamic cursors do not support bookmarks, because there is no guarantee that the record you want to mark will be in the cursor later.