The Recordset as a Data Container

[Previous] [Next]

One of the simplest features of the ADO Cursor Engine is its ability to store the results of your queries. Of course you can also use a server-side cursor to store query results, but in most cases, as we discussed in Chapter 7, you want to avoid server-side cursors.

Using a client-side Recordset object to work with the results of your query can save a great deal of coding. You don't have to move the data from the recordset to your own data structure. You also have a wealth of rapid application development (RAD) features available to you when you use the Recordset object. Bound controls are among the more obvious and traditional RAD features.

Bound Controls

Traditionally, bound controls in Microsoft Visual Basic have gotten a bad rap. But I'm much more impressed with the bound controls included with Visual Basic 6 than I was with previous versions of the controls. With version 6, you can bind controls more easily and dynamically at run time. Using bound controls with ADO Recordset objects doesn't incur as much overhead—such as network activity and additional database connections—as using them with the Recordset objects' DAO (Data Access Objects) counterparts. Visual Basic 6 makes it possible to format your data (including currency and Null data) with the DataFormat property on most bound controls. As you can see, bound controls are a more attractive option than they were in the past.

However, by using bound controls you still give up a great deal of flexibility. Displaying data in a bound grid is simple, but interacting with that data the way you want can pose a challenge. In many cases, you'll probably want more control over data manipulation in your application than bound controls offer. For example, when a user enters a product number into an order, you might want to perform a lookup and display the name, price, and availability for that product as soon as the user leaves the product number field. Sometimes you can implement this type of functionality with bound controls; sometimes you can't.

Bound controls often have limitations and bugs that you don't discover until you're halfway through the process of building your application. I've spoken with quite a few Visual Basic developers who had to give up using the DataEnvironment object in their applications because binding controls to the DataEnvironment became more trouble than it was worth.

I use the DataEnvironment object occasionally, but I avoid binding controls to it. The DataEnvironment doesn't properly broadcast events to controls. Controls are not notified of critical events such as when you're requerying your Recordset or navigating through a hierarchical Recordset. As a result, you would have to write so much code to repeatedly bind your controls to the DataEnvironment that you might decide to forego using the DataEnvironment in future projects.

Interacting with bound controls is hardly groundbreaking, however. Both DAO and RDO (Remote Data Objects) can do that, but with an ADO client-side Recordset you can do so much more.

Filtering and Searching

Have you ever wanted to display only certain records in your Recordset based on a particular criterion? For example, suppose you retrieve all your customers but, based on user input, want to display only the customers located in a particular region.

You could examine the customer data on your own and decide which data to display. You could also submit a new query to your database to retrieve only the customers in that region. ADO offers another option—you can simply apply a filter to your Recordset, like this:

 rsCustomers.Filter = "State = 'MA'" 

Now only customers living in the state of Massachusetts will be visible in your Recordset.

ADO also lets you search for a record in your Recordset using a similar syntax. Suppose that instead of applying a filter to your data, you simply want to locate a record that satisfies a certain criterion—for example, the customer must live in the state of Massachusetts. In this case, you can use the Find method on the Recordset:

 rsCustomers.Find "State = 'MA'" 

Although we discussed the Filter property and the Find method in Chapter 4, two points are worth discussing further here. First, you should be aware that ADO's filtering and searching features aren't available only in client-side Recordsets. These features also work if you use a Recordset based on a server-side cursor. However, I don't recommend using these features unless you're using a client-side Recordset.

When you use a client-side Recordset, the data in the Recordset won't change unless you modify it. Therefore, ADO can build temporary indexes to aid in filtering and searching. ADO can't build a temporary index when you use server-side cursors because it needs to continually refetch data from the cursor to locate the desired record or records. Data in server-side keyset cursors and dynamic cursors can change based on other users' modifications to the database. And what happens when another user adds a new record to your database that satisfies the criteria for the filter you applied to your dynamic server-side cursor? When does that new record appear in your cursor, if at all? Wait, wait—don't tell me. I really don't want to know.

This brings me to my second point: to be brutally honest, I have mixed feelings about ADO's filtering and searching features. They're quite limited. For example, you can't specify multiple criteria when you use the Recordset object's Find method. And the support for wildcards in character fields leaves something to be desired. The filtering and searching features also seem to choke if you use multiple delimiter characters (', ", and so on) in the criteria.

Don't get me wrong. I like the fact that these features were added in ADO 2.0, but they're simplistic and limited when compared to the same features in DAO. While the ADO documentation doesn't claim that these features are as robust as their DAO counterparts, it doesn't go out of its way to point out these shortcomings. I'd like to say that the development team plans to improve on these features, but as of ADO 2.5, this doesn't seem to be the case.

With all that said, if you use simple criteria, searching and filtering in ADO does work well. I do recommend using these features—as long as you're aware of their limitations and you know that those limitations won't affect your application. We won't discuss filtering and searching again in this book. For more information on the use and limitations of these features, see Chapter 4 and the Web site for Microsoft's online Knowledge Base (http://support.microsoft.com/search).

Sorting

Sometimes it's nice to sort data based on a field (column) or multiple fields. This functionality is available in most applications. Open a directory in Microsoft Windows Explorer, and you can click any of the column headers to sort the files and directories by their name, date, and so on. You can implement similar functionality by using the Sort property on the Recordset object.

For example, suppose you want to sort your customers by a BalanceDue field and see the customers who owe you the most money first. You could simply add the following line of code:

 rsCustomers.Sort = "BalanceDue DESC" 

No need for me to warn you about any issues here. Sorting is not nearly as complex as filtering and searching. For more information on sorting, see the discussion of the Sort property in Chapter 4.