Accessing Fields in RecordSets

The data-bound controls can display the fields of the current record in the Record-Set Of a Data control, but you must also be able to access the fields’ values from Within the application. The field values can be accessed via the Fields object of the RecordSet. The following expression:

recordset.Fields

represents the fields (columns) of the RecordSet. The recordset variable represents a RecordSet (it could be a Data control’s RecordSet property, Data1.RecordSet, or a RecordSet variable).

You access individual fields through the field’s name or through the field’s ordinal position in the table. If the Data1 Data control is connected to the Titles table of the BIBLIO database, you can access the Title field of the current record with either of the following statements:

bookTitle = Data1.Recordset.Fields(0)
bookTitle = Data1.Recordset.Fields.(‘Title’)

Two more properties of interest are the RecordCount property of the RecordSet object (which returns the number of records in the RecordSet) and the Count property of the Fields object (which returns the number of fields in the RecordSet’s row). These two properties are actually the dimensions of the RecordSet. The number of rows in the RecordSet of the Data1 Data control is:

Data1. RecordSet.RecordCount
and the number of columns in the same RecordSet is:
Data1. RecordSet. Fields.count

Vb6 at Work: Scanning a Recordset

Let’s develop a short application to demonstrate how you can scan the records in a RecordSet and process their fields. The processing is quite trivial: We will place the rows of a RecordSet on a ListBox control. However, the code demonstrates how to scan a RecordSet and extract specific fields.

To build this application, follow these steps:

1. Start a new project and place a ListBox control and a Data control on it.
2. Set the Data control’s Visible property to False. You can’t use the Data control to navigate through the RecordSet, since all the records will be in the ListBox control.
3. Set the DatabaseName and RecordSource properties of the Data control to any table of the BIBLIO or NWIND database.
4. Now, enter the following code in the Form’s Load event:
   

Seek The Seek method of the RecordSet object can instantly locate a record in a table based on the value of an index field. The Seek method is extremely fast because it uses the index of a table, and in effect, locates an item in a sorted list The Seek method has the following syntax:

Seek operator, key

The operator argument is one of the following relational operators:

1. =(equal)
2. >(greater than)
3. < (less than)
4. >= (greater or equal)
5. <= (less or equal)

The key argument is the value to be compared with the key field of the index, If the index is made up of a single field, key is a single value. If the index is made up of multiple fields, the key argument may contain multiple values, separated by commas.

For example, if the Customers table is indexed according to its State field, you can seek the first customer in California with the following statement:

Datal.Recordset.Seek ‘ = ‘ , ‘CA’

The Seek method is much faster than the Find method, but it’s not as flexible. If  you frequently need to locate records based on a specific field’s value, create an index on this field and use the Seek method. You can use the Find method to perform all types of searches involving multiple fields an~ the LIKE operator, but it’s not nearly as fast as the Seek method.

VB6 at Work: The ManyTblsProject

The Many This application (see Figure 17.15) demonstrates the use of the Seek method to quickly locate records based on their primary keys. This application combines all the tables in the BIBLIO database to display the titles, along with their authors, publishers, and related data (comments a d descriptions);

As the user navigates through the titles with the navigation buttons of the TITLES Data control, the program displays the next or previous title in the first textbox and the book’s Comments and Description fields in their corresponding textboxes. These textboxes are bound directly to the Data control on the Form.

To display the publisher of the title, the application uses the PubID field of the Titles table as a key to the Publishers table, where it locates (using the Seek method) the record with the same PubID.

To display the author of the title, the application uses the ISBN field to locate the matching record in the Title Author table. When the record that corresponds to the Current title is found, the program uses the Au_ID field as a key to the Authors table to locate the primary author’s name.

These actions must take place within the Data control’s Reposition event, which is triggered every ‘time the user clicks OI)eof the navigation buttons to move to another record in the Titles table. The application uses all four tables in the database, and you must therefore use four RecordSets, one for each table. The Form of the application at design time contains four Data controls, as shown in Figure 17.16,but only one of them is.visible at runtime.

To design the Many This application, follow these steps:

1. Start-a new project and place the Label and TextBox controls you see in Figure 17.16.
2. Place four Data controls on the Form: TITLES, PUBLISHERS, AUTHORISBN, and AUTHORS Set their DatabaseName property to the path of the BIBLIO database.
3. Each Data control sees a different table of the database, so you must set the RecordSource property of each control according to Table 17.10.

   

     4. Now bind the textboxes to the corresponding fields of the Data controls on the Form.

       – Bind the textbox in which the title is displayed to the Title field of the TITLES Data control.
– Bind the textbox in which the publisher is displayed to the  Publisher – field of the PUBLISHERS Data control.
– Bind the textbox in which the author is displayed to the Author field of the AUTHORS Data control.

Bind the remaining textboxes to the corresponding fields of the proper Data control. You can open the ManyTbls application in Visual Basic’s IDE and examine how the various data-bound controls are bound to the Data controls. At this point, you can combine any title with any author and any publisher because the threeData controls are not connected and can be positioned at will in the corresponding tables.

We are going to use the Seek method of the Data control to connect the controls, so we must use the primary indices of the tables. This must take place when the Form is loaded.

       5. Enter the following code in the Form’s Load event:

To open an index file for a Table RecordSet,you must assign the name of the index to the Index property of the RecordSet. Once the Index property has been set, all Seek operations on the RecordSet will use this index.

      6. Enter the.following code in the TlTIES Data control’s Reposition event

All the action takes place in the Reposition event. Each time the user repositions the Data control in the RecordSet, the program does the following:

1. It uses the Seek method on the PUBLISHERS RecordSet to locate the record whose pubID field matches the PubID field of the title displayed.
2. If no such record exists, it prints asterisks in the textbox where the publisher’s name would normally appear. If a matching record is found, the program doesn’t do anything. The Seek method repositions the PUBLISHERS Data control to the row of the Publishers table with the title’s publisher, and the corresponding data-bound textbox is updated automatically.
3. The program then locates the record of the Title Author table whose ISBN field is the same as the current book’s ISBN. If no such record exists, it prints aster-isks and exits the subroutine. If a matching record is found, its Au_ID field becomes the key for. the last seek operation, which locates the record in the AUTHORS table, whose Au_ID field matches the Au_ID field located in the Title Author table.

The Seek method is fast; the fields on the Form are updated instantly. Even with large files, ‘this approach works well and doesn’t introduce any significant delays. Notice that you can seek records in a Recordset based on more than one index. Each Seek operation must be performed with a specific index, but you can change the current index by setting the Index property of the RecordSet to another value.