Advanced Visual Basic Techniques
(Publisher: John Wiley & Sons, Inc.)
Author(s): Rod Stephens
ISBN: 0471188816
Publication Date: 06/01/97

The Data Control

Using a data control and other controls attached to it, an application can interact with a database using little or no Visual Basic code. Before describing the more advanced database operations performed by PeopleWatcher, this section explains how an application can use the data control to access data with a minimum of effort. The Simple.VBP project located in the Ch8\Simple directory on the compact disk demonstrates this minimalist approach.

A data control’s DatabaseName property indicates the name of the file that contains the database the control should manipulate. Its RecordSource property indicates the way the control relates to the database. The RecordSource can be an SQL statement similar to the ones described later in this chapter, or it can be the name of a table.

In the Simple.VBP project, the data control PeopleData has DatabaseName property set to D:\Ch9\Simple\Simple.mdb. Before you can test the program Simple on your computer, you will need to change this value to reflect the location of the database on your system. The PeopleData control has RecordSource value People. Together these properties indicate that the control should gather data from the People table in the database file D:\Ch9\Simple\Simple.mdb.

Other controls in an application can be connected to a data control. A control’s DataSource property indicates the name of the data control to which another control is connected. The control’s DataField property indicates the field selected by the data control that should supply the data for the control. A control connected to a data control in this way is called a data bound control.

Project Simple contains two text boxes named LastNameText and FirstNameText. Both are bound to the data control PeopleData. The DataField property of LastNameText is set to LastName; this indicates that the text box should be filled with data from the LastName field in the data selected by the database control. Similarly, the DataField property of FirstNameText is set to FirstName, indicating it should be populated with values from the data control’s FirstName field.

Project Simple is ready to run with no additional source code. Even though it does not include a single line of code, project Simple can perform several basic database chores. The data control’s left and right arrow buttons move the control through the People table. The start-of-file and end-of-file buttons, the buttons with arrows pointing to lines, move the control to the first and last record in the table. As the data control moves through the table, the text boxes automatically display the values of the records’ LastName and FirstName fields.

If the user enters text in one of the program’s two text boxes, the data control automatically begins editing the corresponding database record. When the user moves to another record by clicking one of the movement buttons, the data control automatically updates the database. Figure 8.5 shows this simple program in action.

Binding Other Controls

Just as an application can bind text boxes to a data control, it can bind other types of controls. For example, an application can bind an image or picture box control to a database field that contains pictures. As is the case with text boxes, the control’s DataSource property should be set to the name of the data control. Its DataField property should be set to the name of the data field that contains the appropriate images.

Different databases have different ways of storing large chunks of binary data such as pictures. These objects are often called binary large objects or BLOBs. Some databases provide a BLOB data type. Access databases do not, but their Long Binary data type can be used to store pictures. To make an image control display a picture, the application should set its DataField property to indicate a field that has the Long Binary data type. As the data control moves through the database, the image control will automatically display the pictures in the corresponding records.

A non-Access database may store pictures in a different way. For example, it might store pictures in fields of the BLOB data type. It is also possible that there is no easy way for the database to store a picture. In that case, it may be easiest to store the images in files on a hard disk and then keep only the names of the files in the database itself.

Selecting Records

In program Simple described in the previous section, the data control’s DatabaseName property is set to the location of the People.MDB database file. Its RecordSource property is set to the name of the People database table. When the program runs, it uses these properties to create a list of records for the data control to manage. This list is called a recordset. In this simple example, the recordset includes all of the records in the People table, but there are other ways a program can define a data control’s recordset.

FIGURE 8.5  A simple database program using the data control.

One of the most flexible ways to define a recordset is by using an SQL statement. SQL stands for Structured Query Language, an industry-standard language for manipulating relational databases. The complete SQL language includes commands for creating and dropping tables from the database; adding, deleting, and updating records in the tables; composing complex queries; and performing many other data manipulation tasks. An SQL SELECT statement can be used to select the records that should be contained in a data control’s recordset.

For example, in the Simple project the PeopleData control’s RecordSource property could be set to the following SQL statement instead of the table named People.

SELECT * FROM People ORDER BY LastName

Then when the program runs, the data control’s recordset includes all the records in the People table sorted by the LastName field. The records are ordered by LastName, but they are not ordered by FirstName. The names Ursula Jones, Harry Jones, and Xavier Jones appear in an undefined order—Harry does not necessarily come first. This can be easily fixed by adding the FirstName field to the SELECT statement’s ORDER BY clause.

SELECT * FROM People ORDER BY LastName, FirstName

Now when the data control lists the records, Harry comes first, Ursula comes second, and Xavier comes last.

Another important clause that can be included in a SELECT statement is the WHERE clause. The WHERE clause allows a statement to specify criteria a record must meet to be included in the recordset. For example, the following SELECT statement would select only records that have the LastName field value Jones.

SELECT * FROM People WHERE LastName=‘Jones’ ORDER BY LastName, FirstName

The following statement selects records that have LastName values that come alphabetically before Jones:

SELECT * FROM People WHERE LastName<‘Jones’ ORDER BY LastName, FirstName

In these statements the value Jones is surrounded by single quotation marks. An SQL statement can use either single or double quotation marks to delimit text values. Single quotes are a bit easier to handle in Visual Basic because SQL statements are usually stored in String variables. Single quotes are easier to embed in strings in Visual Basic code.

SQL statements are case insensitive. Even database table and field names can be in uppercase or lowercase. That means the previous SELECT statement is equivalent to the following:

Select * from people WhErE LASTname=‘Jones’ order by lastNAME, FirstName

Statements are easier to read, however, if SQL keywords like SELECT are entered in all capital letters and if table and field names are capitalized as they are defined in the database.

You can search the Visual Basic help for “SELECT queries, defining” to find much more information.