RecordSets are the foundation of database programming. Let’s look at an example that will help you visualize RecordSets and explore the Data control. The Datal application, shown in Figure 17.3, is nothing less than a frorv end for an existing table in a database.
VB6 at Work: The Data1 Project
To build this application, follow these steps:
- Start a new Standard EXE project and design a Form like the one shown in Figure 17.3 Start by drawing a Data control at the bottom of the Form. The Data control is your gateway to the database.
- With the Data control selected, open the Properties window, locate its DatabaseName property, and then click on the ellipsis button to open the Open dialog box.
- Select the NWlND database that comes with VisalBasic.
- Locate the Recordsource property in the Properties window, and drop down the list of available entries. You’ll see a list of all the tables in the NWIND database. Select the Customers table.
- Place four textboxes on the Form, as shown in Figure 17.3.
Select the first textbox, and in the Properties window locate its DataSource property. Set it to Datal.
- Set the DataField property of the textbox to CompanyName. The DataField property is a drop-down list with the names of all fields in the Customers table.
- Set the DataSource property of the other three textboxes to Datail, and set the DataField property to Country, Phone, and Fax.
Now run the application. The textboxes will display the values of the corresponding fields of the Customers table in the NWIND database. Using the Data control’s buttons, you can move through the records (rows) of the table. Clicking the leftmost button displays the first record of the table, and clicking the button next to it displays the previous record. Clicking the rightmost button displays the last record of the table, and Clicking the button next to it displays the next record.
Not only can you navigate through the table’s records with the Data control, you can also edit its fields. Change the value of a field, move to the next record, and then come back to the previous record. The changes you made to the record are saved to the database, and now you see the updated value of the record.
You should also attempt to enter invalid data in the table’s fields. The program . won’t allow you to enter invalid values and will let you know why. If you attempt to enter a very long phone number, for example, the application displays a message indicating that the entry is too long for the field and gives’ you a chance to correct the value. The field was obviously declared with a limited length in the definition of the database. It is actually the database management system that issues the warning, not Visual Basic, and certainly not the application. You have yet to enter a single line of code.
Figure 17.4 shows the relationship among the TextBox controls on the Form, the Data control, and the database. The controls on the Form can’t see the database directly. Instead, they see the database through the Data control. The Data control, in turn, sees a RecordSet, which happens to be a table of the database. You’ll learn later how to specify other types of RecordSets for the Data control.
The Data Control’s Properties
The most important properties of the Data control are:
- DatabaseName, which specifies the database to be used
- RecordSource, which speci.fies the part of the databaseseen by the control.
At any given time the Data control is positioned at a single row (record) of its RecordSet. The user can move to any other record with the help of the navigation buttons on the Data control.
The textboxes see a field in the current row. Each time the Data control is repositioned in its RecordSet, the textboxes are updated. H the data in a textbox change, the new value is written to the database when the Data control is repositioned. The TextBox controls are connected to a field of the RecordSet through the Data control, and they are called data-bound (they have been bound a field in the RecordSet).
The most important properties of a data-bound control are:
- DataSource, which is the name of a Data control through which the controls e.g., TextBox, CheckBox) are bound to a Data control; in other words, it’s the name of a Data control through which the data bound control “sees” the database.
- DataField, which is the rwne of a field in the RecordSet that the control displays and updates.
You can set the following properties of the.Data control from the Properties window, or you can use them from within your code to manipulate the control.
EOF The EOF (End of File) property returns a True/False value that indicates whether the current record position is after the last record in a RecordSet object.
BOF The BOF (Beginning of File) property returns a True/False value that indicates whether the current record position is before the first record in a RecordSet object.
BOFAction This property sets or returns a value indicating what-action the Data control should take when the BOF property is True, which can be one of the actions in Table 17.1.
EOFAction This property sets or returns a value indicating what action the Data control takes when the EOF property is True, which can be one of the actions in ) Table 17.2.
ReadOnly This property returns or sets a value that determines whether the control’s Recordset is opened for read-only access.
RecordsetType This property returns or sets a value indicating the type of RecordSet object you want the Data control to create. It ‘tan have one of the values shown in Table 17.3.
Options This property sets one or more characteristics of the Recordset object and can have one of the values shown in Table 17.4.