DB2 7.2 and Visual Basic 6.0 - Bound and Unbound ADO Forms

Document options Document options requiring JavaScript are not displayed Sample code Rate this page Help us improve this content

Level: Introductory

Essma Hasin (mailto:essma@neneva.com), Visual Basic and C Programmer, Quality Assurance Engineer , Neneva Technologies

06 Jun 2002

This article gives step-by-step directions on how to develop DB2 applications that use bound and unbound data forms, as well as guidance on which type of form to use for your application.

© 2002 International Business Machines Corporation. All rights reserved.

Introduction

Using Visual Basic 6.0, graphical user interface (GUI) developers can build applications that access data stored in IBM DB2® Universal Database^TM (UDB) 7.2. Typically, these GUI applications use Microsoft ®ActiveX Data Objects (ADO) to manipulate data and are made up of one or more Visual Basic GUI forms. Each form has one or more controls that allows for presenting or modifying application data, in which the form controls may be either bound or unbound to the data source.

When form controls are bound to an application data source, the form control value is automatically synchronized with the data source value. Therefore, any updates to the control are automatically reflected in the data source, and any updates to the data source are also automatically reflected back in the control. When form controls are unbound, then the control value must be programmatically kept in sync with the data source value, and vice versa.

This article describes how to develop DB2 applications that use bound and unbound data forms and offers guidance on which type of form to use for your application.

Back to top

Sample scenario - Authors and books information

To demonstrate the implementation differences between bound and unbound data form controls, we will look at a sample form (shown in Figure 1) that allows users to select an author from a stored list of authors, and then to select a book from the list of published books for that author. The idea is to display both the author and book information using this Visual Basic form. We will work through implementing this form using bound and then unbound form controls.

Figure 1

The list of authors will be shown in the Authors drop-down combo box. When an author is selected, the author information group of controls will be populated with the selected author information, and the main Books drop-down combo box will be populated with the list of books for this author. When a book is selected from the books combo-box, the book information group of controls will be populated with the selected book information.

This application will access two tables, Books and Authors, from the sample DB2 database.

For more information on how to access the sample scenario Visual Basic project, create the two tables, and populate the application tables with data, see the Appendix.

Back to top

Overview of the ADO programming model

ActiveX Data Objects (ADO) enables applications to access and manipulate data in a database server through any OLE DB provider. DB2 UDB ships with a native OLE DB provider called IBMDADB2, which will be installed on your system when you install DB2.

ADO enables you to write an application to access and manipulate data in a database server through the use of a common set of programming objects and APIs, isolating the application developer from the specific interfaces of the particular database server. ADO's primary benefits include ease of use, high speed, low memory overhead, and a small disk footprint. ADO supports key features for building client/server and Web-based applications.

As shown in Figure 3, the major objects found in the ADO programming model include the following:

Back to top

Bound data forms

Data-bound form controls simply refer to the use of data binding properties of the various form controls, in which these controls are automatically linked to result set columns of an ADO data control, allowing for automatic synchronization between the control data and the result set data.

This is accomplished by specifying the ADO recordset data source as well as the recordset field name, thus linking the form control to the record set field or fields.

Data-bound forms using an ADO data control object

There are many ways to bind form controls to a data source, including the use of an ADO data environment (connection and command) objects, as well as ADO data control objects.

The steps required to create the data bound form using a data control object are as follows:

1. Add the required ActiveX components to your project
2. Specify the ADO connection properties for connecting to DB2
3. Specify the ADO command properties for retrieving the DB2 data
4. Bind the form controls to the ADO data controls
5. Connect the four ADO data controls
6. Test the completed form

Step 1: Add the required ActiveX components to your project
The Microsoft ADO Data Control is used to provide quick access to data from an OLE DB provider. It allows you to quickly create a connection to a database using ActiveX Data Objects (ADO) and to retrieve a set of records based on a provider-specific records source statement.

Before using the required ActiveX data control components, we must first add them to our project as follows:

1. Access the menu entry Project->Components...
2. Select the entry Microsoft ADO Data Control 6.0 by clicking its checkbox.
3. Select the entry Microsoft DataList Controls 6.0 by clicking its checkbox.
4. Create four ADO data control objects in your form, and call them adodcAuthors, adodcAuthorData, adodcBooks, and adodcBookData.

Step 2: Specify the ADO connection properties for connecting to DB2
We must set the data control connection property to point to the DB2 sample database. You can do this by selecting each of the four ADO data control objects you just created, and then:

1. In the Properties view, double click the Connection String property.
2. Select Use connection string and click the Build button.
3. In the Provider tab, select the IBM OLE DB Provider for DB2 Servers entry.
4. In the Connection tab, specify sample for the Data Source field.
5. Select the Use a specific user name and password radio button.
6. Click the Test Connection to validate that the connection string is valid.
7. Click OK to accept the generated connection string.

Step 3: Specify the ADO command properties for retrieving the DB2 data
We must set the data control properties required to execute the appropriate queries required to retrieve the data from the DB2 sample database. To do this, select each of the four ADO data control objects, and then:

1. In the Properties view, modify the Command Type to be adCmdText, to indicate that the command will be a SQL string.
2. In the Properties view, modify the Cursor Location to be adUseClient, to indicate that the client-side cursors supplied by a local cursor library is to be used, as local cursor engines often will allow many features that driver-supplied cursors may not.
3. In the Properties view, modify the Cursor Type to be adOpenStatic, to indicate that a static copy of the resulting set of records are to be used where additions, changes, or deletions by other users are not visible.
4. In the Properties view, modify the Lock Type to be adLockOptimistic, to indicate that the provider locks records only when you call the control's Update method.
5. In the Properties view, modify the Record Source to be the required SQL query as shown in Table 1.

Table 1. Record source SQL queries

Step 4: Bind the form controls to the ADO data controls
Now that we have set up our data controls to retrieve the required DB2 data, we can bind the various form controls directly to these ADO data controls for automatic data synchronization (display and update).

Binding the special data controls
There are two main special combo box data controls, dcboAuthors and dcboBooks, both of which need to be populated with the results from a data control ADO record set.

A combo box data control differs from standard combo box control in that it has additional properties including ListField, RowMember, and RowSource. These properties allow for automatic population of the combo box items list with record set data from an ADO data control.

In our form, we will make use of RowSource to specify the source data control name for the combo box, and ListField to specify the record set column name for the combo box items list, as shown in Table 2.

Table 2. Specifying the source for the combo box

For each control in the author data group, the DataSource property will be set to adodcAuthorData, and the DataField property will be set to the appropriate column name from the record set. Refer to the adodcAuthorData SQL statement in Table 1 for a complete list of columns.

Similarly, for each control in the book data group, the DataSource property will be set to adodcBookData, and the DataField property will be set to the appropriate column name from the record set. Refer to the adodcBookData SQL statement in Table 1 for a complete list of columns.

Step 5: Connect the four data controls
Our design dictates that when an author is selected from the list of authors (combo box), both the author data controls and the list of author books (combo box) must be updated to reflect the new author selection. Similarly, when a book is selected, the book data controls must be updated to reflect the new book selection.

Update the author data
When a new author Name is selected in the dcboAuthors combo box, we must first determine the corresponding AuthorID for the selected author, and then re-execute the adodcBooks and adodcAuthorData queries using the selected AuthorID. As the queries are re-executed, the form controls that are bound to these two ADO data controls will be automatically updated with values from the re-executed query result sets.

The Visual Basic code in Listing 1 will accomplish this task for us:

Listing 1. Visual Basic code to handle new author selection

 
' Handle change in author selection 
 
Private Sub dcboAuthors_Change() 
    Dim strAuthorName As String 
    Dim strAuthorID As String 
    Dim strSQL As String 
     
    ' If nothing selected, then do nothing 
    If dcboAuthors.BoundText = "" Then 
        Exit Sub 
    End If 
         
    ' Lookup selected book name -- fix apostrophe 
    strAuthorName = Replace(dcboAuthors.BoundText, "'", "''") 
     
    ' Locate the selection in the record set so that we get the AuthorID 
    adodcAuthors.Recordset.Find "Name= '" & strAuthorName & "'", _ 
                                , adSearchForward, adBookmarkFirst 
 
    strAuthorID = adodcAuthors.Recordset("AuthorID") 
     
    ' Change the books list to show only those books for the author selected 
    strSQL = "SELECT BookID, Title FROM Books WHERE AuthorID= '" & strAuthorID & "'" 
    adodcBooks.RecordSource = strSQL 
    adodcBooks.Refresh 
     
    ' No book is selected by default 
    dcboBooks.Text = "" 
 
    ' Change the authors data to reflect the author selected 
    strSQL = "SELECT Name, Address,  City, State, ZipCode, EMailAddress, " &_ 
             "PhoneNumber from Authors Where AuthorID = '" & strAuthorID & "'" 
    adodcAuthorData.RecordSource = strSQL 
    adodcAuthorData.Refresh 
 
End Sub 

The visual basic code shown in Listing 2 will accomplish this task for us:

Listing 2. Updating book data

 
' Handle change in book selection 
 
Private Sub dcboBooks_Change() 
    Dim strBookName As String 
    Dim strBookID As String 
    Dim strSQL As String 
     
    ' If nothing is selected, then do nothing 
    If dcboBooks.BoundText = "" Then 
        Exit Sub 
    End If 
     
    ' Lookup selected book name -- fix apostrophe 
    strBookName = Replace(dcboBooks.BoundText, "'", "''") 
     
    ' Find the book id for the title selected 
    adodcBooks.Recordset.Find "Title= '" & strBookName & "'", _ 
                                , adSearchForward, adBookmarkFirst 
                     
    strBookID = adodcBooks.Recordset("BookID") 
     
    ' Change the book data to reflect the book selected 
    strSQL = "SELECT ShortTitle, BookID, ISBNNumber, PubYear, AuthorID, " & _ 
             "UnitPrice from Books Where BookID = '" _ 
             & strBookID & "'" 
    adodcBookData.RecordSource = strSQL 
    adodcBookData.Refresh 
 
End Sub 

Step 6: Test the completed form
With the limited amount of coding shown above, we have bound our application data to our application form controls. To test this application, simply run it, click the bound form button, and select an author and then a book for that author from the data-bound form. The form data should get updated as soon as you make these selections.

Back to top

Unbound data forms

As outlined earlier, unbound data forms simply refer to using the ADO programming APIs to manually "hook up" the various form controls to ADO record sets. Although a lot more work is required to accomplish this task, it is sometimes more efficient and flexible than data-bound programming. In addition, unbound data forms are the only viable approach when developing middle-tier components of multi-tier applications. In middle-tier code, there are no visual controls to be bound; rather, result set data are retrieved from one tier, typically the data access tier, and then are passed to another tier, typically the presentation tier.

Unbound data forms using ADO programming model

The steps required to create the unbound data form using the ADO programming APIs are as follows:

1. Code the ADO connection object
2. Code the ADO command and parameter objects
3. Populate the combo box with result set values
4. Update the form controls with result set values
5. Connect the form controls for automatic update
6. Test the completed form

Step 1: Code the ADO connection object
Our application requires only one ADO connection object that can be used by all of our ADO commands to retrieve data from DB2. This ADO connection object is used to specify the OLE DB provider and data source name using the Connection String property, as well as other connection properties, including the Cursor Location property. The code fragment in Listing 3 shows how to create and set up the acnBooks connection object.

Listing 3. Creating and setting up a connection object

 
    ' Create connection object and connect to database 
    Set acnBooks = New ADODB.Connection 
 
    With acnBooks 
        .ConnectionString = "Provider=IBMDADB2.1;Data Source=SAMPLE" 
        .CursorLocation = adUseClient 
        .Open 
    End With 

Step 2: Code the ADO command and parameter objects
Our application requires four ADO commands to retrieve the various data result sets:

1. The acmAuthors command for retrieving the list of authors (see Listing 4),
2. the acmBooks command for retrieving the list of books for a given author,
3. the acmAuthorInfo command for retrieving specific author information,
4. and the acmBookInfo for retrieving specific book information.

Listing 4. Command for retrieving list of authors

 
    ' Set up the authors command 
    Set acmAuthors = New ADODB.Command 
    With acmAuthors 
        .ActiveConnection = acnBooks 
        .CommandType = adCmdText 
        .CommandText = "SELECT AuthorID, Name from Authors" 
    End With

The command acmAuthors does not require any input parameters; however, the commands acmBooks and acmAuthorInfo will require an AuthorID input parameter, and the command acmBookInfo will require a BookID input parameter. For these commands, we must create an ADO Parameter object using the CreateParameter method and then add it to the command using the Append method, as shown in Listing 5.

Listing 5. Creating a parameter object and adding it to command

 
    ' Set up the books command 
    Set acmBooks = New ADODB.Command 
    With acmBooks 
        .ActiveConnection = acnBooks 
        .CommandType = adCmdText 
        .CommandText = "SELECT BookID, Title FROM Books WHERE AuthorID= ?" 
        Set adoParm = .CreateParameter("AuthorID", adChar, adParamInput, 1, "x") 
        .Parameters.Append adoParm 
    End With

You can implement the remaining two commands, acmAuthorInfo and acmBookInfo, in the same way but with different parameter names; namely, AuthorID and BookID, respectively. Table 2 describes the required CommandText for these ADO commands:

Table 2. The SQL Queries required for acmAuthorInfo and acmBookInfo

Step 3: Populate the combo-box with result set values
Unlike data bound combo box controls, unbound controls must be filled with list data programmatically. This is accomplished by first obtaining the desired result set, and then adding the appropriate result set column to the combo box items list one row at a time.

Executing the ADO query
First, we execute the ADO query:

1. Create an ADO record set object to hold the result of executing the ADO command.
2. Execute the command using the Open method of the ADO record set passing in the appropriate ADO command:
   • Set the parameter Cursor Type to be adOpenStatic, to indicate that a static copy of the resulting set of records are to be used where additions, changes, or deletions by other users are not visible.
   • Set the parameter Lock Type to be adLockOptimistic, to indicate that the provider locks records only when you call the control's Update method.

The code fragment in Listing 6 shows how to execute the acmAuthors ADO command:

Listing 6. Executing the acmAuthors ADO command

 
    ' Execute query 
    Set arsAuthors = New ADODB.Recordset 
    arsAuthors.Open acmAuthors, , adOpenStatic, adLockOptimistic

Listing 7 illustrates how to fill the cboAuthors standard combo box with data (arsAuthors) resulting from the execution of the acmAuthors ADO command:

Listing 7. Populating the combo box with the results of the query

 
    ' Populate combo-box with query results 
    cboAuthors.Clear 
    While Not arsAuthors.EOF 
        cboAuthors.AddItem arsAuthors!Name 
        arsAuthors.MoveNext 
    Wend

Step 4: Update the form controls with result set values
Again, unlike data-bound form controls, unbound form controls must be synchronized with result set data programmatically. This is accomplished by first obtaining the desired result set, then updating the form control values with data from that result set. This is all that is required for read-only forms.

The code fragment in Listing 8 illustrates how to update the author information controls with data resulting from the execution of the acmAuthorInfo ADO command:

Listing 8. Updating author information controls

 
    ' Execute the result set 
    Dim arsAuthorInfo As ADODB.Recordset 
    Set arsAuthorInfo = New ADODB.Recordset 
    arsAuthorInfo.Open acmAuthorInfo, , adOpenStatic, adLockOptimistic 
 
    ' Update the form controls 
    txtName = arsAuthorInfo!Name & "" 
    txtAddress = arsAuthorInfo!Address & "" 
    txtCity = arsAuthorInfo!City & "" 
    txtState = arsAuthorInfo!State & "" 
    txtZipCode = arsAuthorInfo!ZipCode & "" 
    txtEmail = arsAuthorInfo!EMailAddress & "" 
    txtPhone = Format(arsAuthorInfo!PhoneNumber & "", "(000) 000-0000") 
 
    ' Release the result set object 
    arsAuthorInfo.Close 
    Set arsAuthorInfo = Nothing

Step 5: Connect the form controls for automatic update
Just as with the bound form description earlier, our design dictates that when an author is selected from the list of authors, both the author data controls and the list of author books must be updated to reflect the new author selection. Similarly, when a book is selected, the book data controls must be updated to reflect the new book selection.

The code required to accomplish this is very similar to the bound form code, with the exception of programmatically re-executing the query and updating the form controls as outlined earlier.

Step 6: Testing the completed form
We can test our application after we complete all of the required code to programmatically bind our application data to our application form controls. To test this application, simply run it, click the unbound form button, and select an author and then a book for that author from the unbound data form. The form data should get updated as soon as you make these selections.

Back to top

Bound versus Unbound data forms

As we've shown, Visual Basic allows you to code DB2 GUI applications using either bound or unbound form controls. Before deciding to use either method, review your application requirements. Table 3 illustrates some of the major advantages and disadvantages (differences) of these two methods.

Table 3. Comparing bound and unbound data forms

Criteria	Bound form controls	Unbound form controls
Connections	Connections are automatically opened and closed. Multiple such connections may be used, depending on the number and type of the data source control used.	You control the number of connections and when these connections are opened and closed.
Transactions	More difficult to control transactions.	Explicit control over transactions and unit of work using the Begin Transaction, Commit, and Rollback connection object methods.
Data Formatting	Form controls can perform automatic two-way (presentation <-> storage) data formatting, mapping each control to one record set field.	Multiple record set fields may be mapped to one form control. Thus it would be possible to create a custom mapping of "last name, first name" to a form control.

Back to top

Conclusion

Data-bound controls allow for rapid application development with limited coding, which is especially useful when building graphical user interfaces; however, there also exists a need to explicitly manage database connections and transactions, and control field mapping, making unbound form controls a viable alternative.

This article demonstrates that the IBM DB2 Universal Database server can work equally well with either bound and unbound data controls using the IBM OLE DB native provider for DB2 databases.

For more information about the IBM DB2 database, visit the URL http://www.software.ibm.com/data/db2/udb. For more information on the ADO programming model, visit the URL http://www.microsoft.com/data/ado.

Back to top

Appendix: Sample download

The DB2Bound.zip included with this article contains the complete DB2Bound Visual Basic project source code as well as the DB2 scripts and data files required to create and populate the two application tables.

These files are included as part of the DB2Bound.zip file:

• ADODisplay.vbp - The Visual Basic 6.0 project file
• ADODisplay.exe - The executable resulting from compiling the ADODisplay project
• CreateTables.db2 - Script to create the required DB2 tables
• CreateTables.bat - Command file used to execute the DB2 script file, CreateTables.db2. Invoke this command from a DB2 command window.
• LoadTables.bat - Command file used to execute the DB2 load facility to load the application tables with data from the authors.txt and books.txt files
• authors.txt - Delimited text data file containing the sample data for the Authors table
• books.txt - Text delimited file containing the sample data for the Books table
• DropTables.bat - Command used to delete the created application tables. Invoke this command from a DB2 command window.

Feel free to use, modify, or re-distribute these files as required with the understanding that these files are provided as is and without any explicit or implicit warranty or support.

Setting up the application and loading the data

To set up the application, simply open a DB2 command window and execute the two bat files, CreateTables.bat and LoadTables.bat. These files create and load the application tables into the sample DB2 database.

After the tables are loaded, you can either directly execute the application by running ADODisplay.exe, or you can load the project into Visual Basic using ADODisplay.vbp, and then run it from within Visual Basic.

Cleaning up the application

To clean up (drop) the resources associated with this application, simply open a DB2 command window and execute the bat file, DropTables.bat. This command deletes and drops the two application tables (authors and books), which were created by the CreateTables.bat.

Back to top

Download

Name	Size	Download method
DB2Bound.zip	25 KB	FTP|HTTP

	Information about download methods

About the author

		Essma Hasin, a certified Visual Basic and C programmer, is a quality assurance engineer at Neneva Technologies, a consulting firm that specializes in building custom solutions for small and mid-size companies utilizing IBM DB2 Universal Database and the Microsoft .Net framework. Essma can be reached at essma@neneva.com.>

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top