Defusing the Data Dilemma

Rob Macdonald

Your mission is to create the best possible presentation of your data without blowing your budget on one VB form. Your query will return multiple records. Do you: 1) present one record at a time in a customized display built out of several controls to get exactly the right effect; or 2) use a data grid so that you can present multiple rows and accept the limitations of the particular grid you work with? Find out how Rob tackles this all-too-common situation.

Any of you who have dealt with "the data dilemma" recognize the problem and realize that neither option is particularly "ideal." The problem is that users usually want to see multiple rows, but they often want a specialized appearance. I'm not saying that the many data grids out there aren't good, but, frankly, each and every one of them has features that you end up working around rather than working with, and none of them is flexible enough to let you create just-right visual effects every time. At the same time, even thinking about writing a form that presents multiple rows where you have to manage the recordset, scrolling, cursors, dynamic control creation, and so on will make your blood pressure rise.

What you really want is to be able to put all of your effort into designing the functionality of a single row -- and then add some magic "fairy dust" that turns your single row into a scrolling multi-row grid. If that sounds too good to be true, it might just be time to start believing in magic, because the ability to do exactly this has been added into VB6 in the form of the DataRepeater control.

To show you how the DataRepeater works, I'll create an example based on the familiar BIBLIO.MDB sample database that comes with VB. Using the following SQL, I'm going to extract title, author, and year published data, sorted by author. I can then present the results in a scrollable grid.

SELECT Authors.Author, Titles.Title, 
Titles.'Year Published' 
FROM Authors, 'Title Author', Titles 
WHERE Authors.Au_ID = 'Title Author'.Au_ID 
   AND  'Title Author'.ISBN = Titles.ISBN
ORDER BY Authors.Author


This sounds quite straightforward to display, but it actually gives me two problems. The title and author fields can both be quite long, and displaying them side by side in a grid would require a very wide form. For this reason, a traditional grid isn't ideal. However, the query returns well over 10,000 records. Users will want convenient scrolling to be able to find the author they're looking for. At the same time, I can't exactly create a customized form with enough controls to display all of these records.

The basic idea
The DataRepeater solves my problem by allowing me to focus on the design of a single row of data (see Figure 1).

Having created my control, I have two more tasks. The first is to create an ADO Recordset to provide the data. The second is to use a DataRepeater control to link the Recordset to the custom control, resulting in a form that looks rather like Figure 2.

What you see in Figure 2 is one DataRepeater control, efficiently managing the process of scrolling through thousands of records, using my custom control to present the results. The DataRepeater will give the appearance of presenting as many instances of my custom control as the screen space I give it allows. However, there's only ever one instance of my control created. The DataRepeater control creates bitmaps to give the effect of displaying multiple controls. Only the "current" row (as shown by the row indicator arrow on the third "row" in Figure 2) has a real control behind it, supporting the full functionality of the control as the user interacts with it.

The net result is an astonishing combination of simplicity and efficiency for the developer -- and a tailored user interface for the user. For the rest of this article, I'll simply flesh out the overall approach I've outlined. And, as usual, you'll have access to the source code from the accompanying .

Creating the single-row data-aware control
The individual row must be created as a data-aware ActiveX control. Rather than start a project with the Form Designer, select the UserControl designer. My control has three components on it, and to match these, I've added three properties to my control: "Author," "Title," and "PubDate." These will correspond directly to the three fields in my SQL query.

The DataRepeater will assign values from the ADO Recordset to these three properties, and when these properties are set, I need to be able to display the values provided in the relevant components of my control. Therefore, I've defined these properties as property procedures (which is the recommended approach for properties anyway). The definition of these properties is shown in Listing 1. As an extra touch, I made the date display change color according to how recently the book was published. By the way, this is the only code in the entire program!

Listing 1. Defining the properties for the AuthorTitle custom control.

Option Explicit
Public Property Get PubDate() As String
PubDate = lblPubDate.Caption
End Property
Public Property Let PubDate(ByVal sNewValue As String)
lblPubDate.Caption = sNewValue
If sNewValue < "1995" Then
   lblPubDate.ForeColor = vbRed
   Else
   lblPubDate.ForeColor = vbBlue
   End If
End Property
Public Property Get Author() As String
Author = lblAuthor.Caption
End Property
Public Property Let Author(ByVal sNewValue As String)
lblAuthor.Caption = sNewValue
End Property
Public Property Get Title() As String
Title = lblTitle.Caption
End Property
Public Property Let Title(ByVal sNewValue As String)
lblTitle.Caption = sNewValue
End Property


With the layout and code in place, I have one final task to complete my control, which is to make it data-aware. Once it's data-aware, it can be used with any VB data control (DAO, RDO, or ADO), but I'll be using it purely in an ADO setting, as required by the DataRepeater.

Each of my three properties needs to be made data-aware. With the code window for the control open, you can make a property data-bindable by selecting Procedure Attributes from the Tools menu. Select a property from the drop-down list, and then press the Advanced button. The dialog box will then appear as shown in Figure 3.

Once the Procedure Attributes dialog box is open, the "Property is data bound" and "Show in DataBindings collection at design time" boxes need to be checked. When this is done, you can save and compile the control. Give the project a meaningful name before you do. Mine is called "Custom" (okay, that's not very meaningful, but you get the picture!). The control must be compiled as separate .OCX before the DataRepeater can pick it up. However, once you've compiled it, you can continue to work on it interactively alongside the DataRepeater by creating a project group.

Creating the data source
The DataRepeater requires an OLE DB compliant data source to work with. You could use the Microsoft ADO Data Control (but not the standard, Jet-based Data Control) with its RecordSource property set to the required SQL, but I'd encourage you to use a DataEnvironment, because it's so much easier to create the SQL graphically, as well as being a great way to organize data access.

Figure 4 shows my DataEnvironment, which should be set up in your Standard EXE (client) project, not in the ActiveX control. The Biblio connection object uses the Microsoft Jet 3.51 OLE DB Provider to point to the standard biblio.mdb file that comes with VB6. The "Titles" command was created using VB6's Graphical SQL designer and uses the SQL shown at the beginning of the article.

The final step in creating the display shown in Figure 2 is to use a DataRepeater control to link the data and the custom ActiveX control. You'll need to add the control to your project via the Components dialog box. Place a DataRepeater control on your form, and the set the properties as shown in Table 1.

Table 1. Properties of the DataRepeater control.
Property Value
RepeatedControlName Custom.AuthorTitle (or the name of your control)
DataSource DE1 (or the name of your DataEnvironment or ADO Data Control)
DataMember Titles (or the name of your Command object if using a DataEnvironment; leave blank if using the ADO Data Control)


This process links the ADO command to your custom control. You can then specify which field in the command should be linked to which property on your control. In other words, we need to specify that the Author field in the command should be linked to the Author property on the control, and so on. While in design mode, right-clicking on the DataRepeater and selecting the Properties menu item will display a set of Property Pages. The one we're interested in is "RepeaterBindings." You can use this to select each property and link it to a command field by pressing the Add button. When all three properties have been linked to fields, the DataRepeater Property Pages should appear, as shown in Figure 5. Once you've done this, the project is ready to run.

Conclusion
I find the DataRepeater control one of the more satisfying new features of VB6, if only because it provides a dramatic example of how all our efforts invested in learning component-based design, control creation, and ADO can really pay off. You really can create tailored, "grid-like" displays without paying the price of performance and development effort that used to be required.

While investigating this approach, I got to thinking that it would be nice to be able to use data that I'd normally hold in an array or collection with a DataRepeater, rather than just the results of SQL queries. After all, I often want to create grid-type displays with all sorts of tabular data. Well, the DataRepeater requires ADO Recordsets, and I know how to make any type of tabular data look like ADO by creating my own OLE DB Provider, a technique I described in the December 1998 issue of Visual Basic Developer (see "Do-It-Yourself Recordsets").

So I tried to use the provider I created for that article (which makes a FRM file look like a Recordset) work with the DataRepeater control. It worked perfectly, as you can see in Figure 6. The DataRepeater makes it possible to create highly customized grid-type displays for all kinds of data -- not just the results of database queries. Data Grid vendors, watch out!



Rob Macdonald is an independent software specialist based in London and southern England. In addition to consulting and training in Windows, client/server, VB, COM, and systems design and management, he also runs the U.K. ODBC User Group and is author of RDO and ODBC: Client Server Database Programming with Visual Basic, published by Pinnacle. +44 1722 782 433, rob@salterton.com.