Using Visual Basic 6

Previous chapterNext chapterContents

- 32 -
Enhancing Your Programs Using the Advanced Data Controls

What Are Advanced Data Controls?

In Chapter 30, "Database Basics and the Data Control," you learned how to use the Data control to link automatically to a database. The Data control is good for browsing records and allowing you to edit existing records one at a time. With the data binding possible with the Data control, you can build a simple data entry form quickly.

However, very few applications can use a data-entry form as simple as the one you built. Most forms enable users to choose values from various lists, such as state or country codes. In other cases, it's more helpful for users to see multiple records at the same time, especially when dealing with financial data.

Active Data Objects (ADO)

ADO is Microsoft's long-term data access strategy. Instead of the alphabet soup that exists now (DAO, RDO, ODBC, and so on), ADO will replace all of them. ADO also can read data from sources other than databases. For instance, ADO can read data from Microsoft Index Server, which is used to search the contents of a Web site. ADO uses OLE DB and OLE DB providers to actually connect to a data source. Just like DAO can be used to connect to Access' Jet engine, ADO is used to connect to the OLE DB engine. Microsoft has already released OLE DB providers for Access/Jet, ODBC, and others. Visit http://www.microsoft. com/data for more information on Microsoft's strategic plans for ADO.

For these and other reasons, Visual Basic includes three other specialized controls that bind to data in different ways for more flexibility. This chapter will show you how to use these three controls: DataGrid, DataList, and DataCombo. These new controls use Active Data Objects (ADO) to communicate with the database. Visual Basic 6 makes extensive use of ADO in advanced database programming. Rather than use the Data control that you learned about in Chapter 30, these new controls make use of the ADO Data control. This control works similar to a Data control in that it handles the database access. However, it's much more flexible than a standard Data control because it uses ADO to communicate with the database instead of the older Data Access Objects (DAO).

Adding and Configuring the ADO Data Control

Before you can use these new controls, you have to create at least one ADO Data control on the form.

Create and configure an ADO Data control

1. From the Project menu, choose Components.

2. From the list, select Microsoft ADO Data Control 6.0 (OLE DB) and then click OK (see Figure 32.1). The ADO Data control will now be shown in your Toolbox (see Figure 32.2).

FIGURE 32.1 Add the ADO Data control to your Toolbox by selecting it from this dialog.

FIGURE 32.2 The ADO Data control is now part of your Toolbox.

Sample databases

The examples in this chapter all use several sample Access databases included with Visual Basic. These databases are located in Visual Basic's installation directory. You can modify these databases to your heart's content. Before you do, however, make a copy of them--you may want to reuse the database later.

3. Double-click the ADO Data control to add one to your form. Set the control's name to adcCustomers, because it will be showing customer information from the sample database.

4. With the control added to the form, it's time to configure it to talk to the database. Click the ellipsis button in the ConnectionString property and then click the Build button on the Property Pages dialog (see Figure 32.3).

FIGURE 32.3 The Property Pages dialog lets you specify how to connect to the data source.

5. Select an OLE DB provider to use. Because the sample database is an Access database, select the Microsoft Jet 3.51 OLE DB Provider to connect to it (see Figure 32.4). Click Next to continue.

FIGURE 32.4 The ADO Data control can connect to a data source by using any of these OLE DB providers. Use the Jet provider for any Access database.

6. Select the database file to use with this connection. Click the ellipsis button next to the database name text box to select the Nwind.mdb file in the main VB directory. The database filename will be filled in the Database Name field on the dialog (see Figure 32.5).

FIGURE 32.5 After you select a database, its filename will appear in this dialog.

Notice that a user ID has been entered in this dialog. That user ID should be left as is for now. Every Access database has a default user ID of Admin created. You need to specify this when you're building the connection.

7. Click the Test Connection button to make sure that the connection will work. If you get a good result (see Figure 32.6), click OK to continue. If not, follow the steps again to make sure that all the information is filled in properly.

FIGURE 32.6 If you see this message, you correctly configured your ADO Data control.

8. With the connection to the database working, you can specify which table to load. Click the Custom property's ellipsis button and then select the RecordSource tab (see Figure 32.7). From the Command Type drop-down list, select 2 - adCmdTable. Then, from the Table or Stored Procedure Name drop-down list, select Customers. Click OK.

That's all you have to do to configure the ADO Data control. It may seem like a lot of steps, but after a few times it will become second nature to you.

FIGURE 32.7 The RecordSource tab lets you specify the data to use in your ADO Data control.

Using the DataGrid Control

The first control you'll be using is the DataGrid, which lets users see and edit multiple rows of data simultaneously. The DataGrid is also useful for rapid entry of large amounts of data. To use this control, you have to add it to the Visual Basic Toolbox.

Add the DataGrid control

1. From the Project menu, choose Components.

2. From the list, select Microsoft DataGrid Control 6.0 and then click OK. The DataGrid control will now be shown in your Toolbox window.

You can now add a DataGrid to your form. Double-click the DataGrid icon in your Toolbox. Enlarge the control so that it fills most of the form.

If you were to run your program now, the DataGrid would be completely empty because you haven't linked it to the ADO Data control yet.

Linking a DataGrid to the ADO Data control

1. Click the DataGrid control on your form. Set its Name property to dgrdCustomers because it will be showing customer information.

2. From the DataSource property's drop-down list, select adcCustomers, which is the ADO Data control you created earlier.

After you specify the DataSource for the DataGrid, the DataGrid can configure itself automatically with the appropriate number of columns and rows. To see the form in action, choose Start from the Run menu . The window will show data from the Customers table (see Figure 32.8).

FIGURE 32.8 The DataGrid control can automatically configure itself to show the data from the source you specify.

Because the default settings aren't always the best, the DataGrid is fully configurable and lets you specify which columns are shown, the format, and the color of each. You also can split the DataGrid into multiple parts that each scroll independently of the other parts. This functionality is common to Microsoft Excel and other spreadsheets.

Selecting Grid Columns

Because database tables can grow quite large, you won't always want to show users every single column in the table. At other times, you will want to show every column so that users can add more records. This section will show you how to specify the columns to show in a DataGrid control. Before you start, however, you have to decide which columns to show in the DataGrid. For this example, the DataGrid will show the Company Name, the Contact Name, the Phone Number, and the Country.

Specify columns in a DataGrid

1. Right-click the DataGrid and select Retrieve Fields from the pop-up menu. Answer Yes when VB asks if you want to replace the existing grid layout. All the fields from the selected RecordSource (in this case, the Customers table) will be loaded into the DataGrid control (see Figure 32.9).

FIGURE 32.9 The DataGrid control loads all the fields from the table so that you can select the ones you want to see.

2. Right-click the DataGrid and select Edit from the pop-up menu. You won't see any changes to the control when you choose Edit; however, you now can click individual columns and edit them.

FIGURE 32.10 When the DataGrid is in Edit mode, you can add and remove columns visually.

3. To delete columns from this list, right-click the CustomerID field and choose Delete. The CustomerID field will no longer be shown (see Figure 32.10).

4. Delete the ContactTitle, Address, City, Region, PostalCode, and Fax columns by using the same technique used in step 3.

5. The control won't quite let you rearrange the columns by using drag-and-drop. If, for instance, you wanted to swap the ContactName and CompanyName columns, you would first cut the ContactName column and then right-click CompanyName and select Paste. The DataGrid will keep all the settings you've made for a column. Feel free to rearrange the columns as you see fit.

6. When showing the data in this DataGrid, the column headers don't necessarily have to be the same as the field names. For instance, CompanyName should be written Company

Name. To make this change, right-click a column and select Properties. On the Columns page, enter the new name for the column in the Caption text box (see Figure 32.11).

FIGURE 32.11 The Caption property on the Columns page controls what will be shown in each column's header.

7. Repeat step 6 for each column header whose caption you want to change. You can pick each column without leaving the Properties window.

Cleaning Up the Form

You can do some fine-tuning to this form to make it look more finished. First, the ADO Data control doesn't need to be visible. It's used automatically by the DataGrid, and it isn't necessary to allow users to see it. Set the ADO Data control's Visible property to False.

You also can cause the DataGrid to always fill the form. First, set the Align property to 1 - vbAlignTop to cause the DataGrid to sit at the top of the form and fill the form left to right. Then add the following code to force the DataGrid to fill top to bottom:

Private Sub Form_Resize()
   dgrdCustomers.Height = Me.ScaleHeight
End Sub

FIGURE 32.12 Your DataGrid is complete and even includes a few professional touches.

A final polish is to add a title to your form, such as Customer Viewer, through the form's Caption property. When you're done, your form will have a more professional look (see Figure 32.12).

Configuring Other DataGrid Properties

You can configure quite a few other properties on the DataGrid. As they're all fairly easy to use, Table 32.1 explains the purpose of each property.

Be careful with AllowDelete

If your record is linked to other tables in any type of relationship, deleting the record won't be allowed and the resulting error message could confuse your users. Deletions can normally take place only if all other related records have been removed.

TABLE 32.1  DataGrid Properties

Property Explanation
Align Specifies whether the DataGrid should be aligned with the top, left, right, or bottom of the form.
AllowAddNew If set to True, new records can be added at the bottom of the DataGrid.
AllowArrows If set to True, navigation can be performed by using the arrow keys.
AllowDelete If set to True, rows can be deleted from the DataGrid by pressing the Delete key.
AllowUpdate If set to True, rows can be edited and have their changes saved automatically.
Property Explanation
CausesValidation If set to True, the Validate event will trigger when focus shifts to another control.
ColumnHeaders Turns column headers on or off.
HeadLines Specifies the number of vertical lines for the column headers.
RowDividerStyle Supplies various options for the format of the row dividers.
TabAcrossSplits Performs the Tab key shift between the split portions of the DataGrid
TabAction Specifies the response to a Tab keypress
WrapCellPointer Specifies whether the cursor should shift to the next row and first column when reaching the end of a row.

Editing rows in a grid

When you're designing your application and think about using a grid, you can edit data in a grid only if you store the data in a single table. If you need to put data into more than one table for a single record, you must use a different type of form. For example, an order will have both information about the order and information about the items on the order. You couldn't edit an order in a single grid because the data would be stored in at least two different tables. However, the DataGrid can show lists of items, such as shippers or state codes. If you just need to view data from other tables, the DataGrid may still be an option for you.

Using the DataList Control

The DataList control is much simpler than the DataGrid control. It's used to show a single column list of entries from a database table. Because it's data-bound, there's no memory limit as to how many items you can show. However, the more you add, the harder it is for users to find a particular item. Like the DataGrid, the DataList also uses an ADO Data control.

Create and use a DataList control

1. On a new form, add a new ADO Data control that accesses the Customers table in the Nwind.mdb database, as explained earlier in this chapter.

2. Add the DataList control to your Toolbox window by choosing Components from the Project menu.

3. From the list, select Microsoft DataList Controls 6.0 and then click OK.

4. The DataList control will now be shown in your Toolbox window. Double-click it to add it to your form with the ADO Data Control.

5. Name the DataList control dlstCustomers. This example will list the customer names in the DataList control so you can see the differences in presentation.

6. Set the RowSource property to adcCustomers. This property specifies the data source that will provide the list of data to show. To specify which field to show, set the ListField property to CompanyName.

FIGURE 32.13 The DataList control shows a single column of data from the data source.

Those are the only properties you have to set to make this form work. Make sure that the DataList is large enough for you to see the contents, and then choose Start from the Run menu . The companies will be listed in the DataList control (see Figure 32.13).

You can also apply the same cleanup techniques to this form. To automatically resize the DataList to fill the whole form, simply add the following code to the form:

Private Sub Form_Resize()
   dlstCustomers.Height = Me.ScaleHeight
   dlstCustomers.Width = Me.ScaleWidth
End Sub

Configuring DataList Control Properties

As mentioned earlier, the DataList control has far fewer properties than the DataGrid. However, it does have some interesting features that the DataGrid doesn't, as listed in Table 32.2.

TABLE 32.2  Selected DataList Properties

Property Explanation
CausesValidation If set to True, the Validate event will trigger when focus shifts to another control.
IntegralHeight Specifies whether the DataList control will show portions of items if the control isn't large enough.
MatchEntry As users type, the DataList jumps based on the characters entered. This property determines how that matching works.

Using the DataCombo Control

The DataCombo control is nearly identical to the DataList except in its appearance. Rather than take up a lot of vertical space, the DataCombo's list portion can drop down as necessary. This is especially helpful in cases where the form needs to stay small. The DataCombo, like a standard ComboBox, should be used for small numbers (less than 100) of items.

Create and use a DataCombo control

1. On a new form, add a new ADO Data control that accesses the Customers table in the Nwind.mdb database, as explained earlier in this chapter. Because the DataList and DataCombo controls are packaged together, the DataCombo control is already in your Toolbox.

2. Double-click the DataCombo control. Name it dcboShippers because it will be used to hold the names of available shippers.

3. Add an ADO Data control that accesses the Shippers table in the Nwind.mdb table, as explained earlier in the chapter. Name the control adcShippers.

FIGURE 32.14 The DataCombo control loads the CompanyName field from the rows in the Shippers table.

4. Set the DataCombo's RowSource property to adcShippers and ListField property to CompanyName, like you did with the DataList control.

When you run your program now, you'll see the available shippers listed in the DataCombo control (see Figure 32.14).

The configurable properties for the DataCombo are basically the same as the ones available for the DataList control. One difference is the Style property, which functions in a similar fashion to the ComboBox's Style property. The DataCombo can be read-only or editable, which gives users a chance to add their own record to the available list. Because the DataCombo is used most often for selecting values from a set list, you normally want to leave the Style property at 2 - Dropdown List.

Working with the Data Form Wizard

If all the preceding material looks a little daunting, don't fear because Visual Basic 6 comes with an all-new Data Form Wizard that can create most of these controls and data entry forms for you. In this section, you'll learn a little bit about what types of forms the Data Form Wizard can build and how you can make the most of this helpful tool.

FIGURE 32.15 Select the type of form the wizard should build.

After selecting the database you want to use in the Data Form Wizard, you'll be presented with several options for what you want your form to do (see Figure 32.15).

As you select each type of form, the graphic in the upper-left corner will show a sample of what the form will look like. The Binding Type section shows how the form will work:

Feel free to try out all three methods with any of the form types and see what you get. In most cases, you'll have to adjust the output a bit to get what you want, but the Data Form Wizard is certainly a major timesaver for these types of forms.

Later in the process of answering the wizard's questions, you'll select a table or query to use, pick the fields for the form, select a sorting order, and even select which actions are allowed for the form. The wizard can automatically build code to handle adding new records, editing existing ones, deleting records, and refreshing the existing data.

Previous chapterNext chapterContents

© Copyright, Macmillan Computer Publishing. All rights reserved.