출처는 http://www-1.ibm.com/servlet/support/manager?rt=0&rs=0&org=ats&doc=7F367E88279FC8F98525674C00616663 입니다.

Getting Started with Visual Basic V6.0 and DB2 Universal Database

Reference #: 8525674C00616663

Techdocs Document #: D1010

Organization: DSC

Modified on: 2001/01/19

Product Information Product: DB2 UDB

Platform: Intel PC

Category: Planning and Design

Building a database application can be a challenging process. The purpose of this article is to introduce IBM's Universal Database and Microsoft's Visual Basic 6.0.

It will implement Microsoft's Active X Database Objects (ADO) introduced in Visual Basic 6.0.

By following the step by step process of building the application users will gain a basic understanding of how to introduce database connectivity and relational data into their Visual Basic applications.

Requirements:

1. UDB V5.2 Fix pack 7

2. Microsoft Visual Basic 6.0 Enterprise or Professional Edition

3. Basic knowledge of Visual Basic terminology and usage

Note: The Step-by-Step walk-through was created on a Windows NT 4.0 Service Pack 4 machine. Other environments have not been tested.

The following steps must be completed before we can begin setting up your sample application:

1. UDB needs to be installed and started.

2. The Sample database must be installed and registered as an ODBC Data Source.

Please refer to the UDB documentation for implementing this environment.

Create a Connection

To begin you must first create a connection to a database.

One alternative to coding the connect string is to create a data environment using the Data Environment designer.

To add the Data Environment designer to your Visual Basic environment, follow the follow these steps: From the Project menu choose Components, Designers tab, and check Data Environment.

The option to add a Data Environment will now appear in the Project menu or in More Active X designers submenu if more than four designers are loaded.

Choose add Data Environment from the Project menu to load a data environment.

The process also adds a Connection object to the data environment.

Set Connection Properties

The Connection is established by setting the Data Link Properties of the Connection Object.

Before we set the Data Link Properties change the name of the Connection Object to MyConnectObj.

The Data Link Properties of MyConnectObj may be obtained by a right-click of the object and selecting Properties from the popup window. The Data Link Properties window appears with four tabs Provider, Connection, Advanced, All.

Ensure that Microsoft OLE DB Provider for ODBC Drivers is chosen from the Provider menu. Select the Connection tab, and from the Dropdown menu under "Use data source name," choose the Sample database and Click OK.

The Connect object is now established.

We now need to make data available to your application. To do this we need to create a Command Object.

Create a Command Object

Your Command Object can be based on a database object or an SQL statement.

To begin creating your Command Object, right-click in the white area of your Data Environment and choose "Add Command" from the pop-up menu.

Name your Command Object MyCommandObj.

We now need to modify the properties of MyCommandObj to specify what data we want from the Sample database. Right click MyCommandObj and choose Properties from the popup menu.

The General tab allows you to choose your data access option Database object or SQL statement. For our application we will use an SQL statement, so select the SQL statement radio-button.

Choose the SQL Builder Button to begin the SQL creation process. This process will automatically prompt you to connect to the Sample database. Once you are connected the SQL Design environment appears.

Before we continue I want you to be aware of a couple of items that have been added to your project environment.

The Project window now contains a Designers folder with your new Data Environment, DataEnvironment1.

Your project also contains a new Data View window. The Data View window will be used to help build your SQL statement.

From the Data View window expand MyConnectObj and the subsequent Tables folder. You may be prompted to connect to the Sample database.

All the user tables of the Sample database will be displayed. Drag and drop the Employee table to the gray area of the SQL Design window.

Check the "All Columns" option of the Employee table and you will see the generated SQL statement.

Close the SQL Designer and you will be prompted to save the query.

The Command Object has now been created. The Data Environment has created another object during this process. A Recordset Object has been created to represent the returned records of the Command Object.

The Recordset Object has the same name as the Command Object except it is prefixed with an "rs". In our program the Command Object MyCommandObj has a Recordset Object of rsMyCommandObj. You must refer to the Recordset Object to display and modify specific rows of the database.

Both MyCommandObj and rsMyCommandObj our referenced in terms of the Data Environment. In code you can refer to your Command Object as:

DataEnvirionment1.MyCommandObj

You can refer to your Recordset Object as:

DataEnvironment1.rsMyCommandObj

Add a Command Object to a Form

Once you have created MyCommandObj you are now ready to use the object to display data on your form. Drag MyCommandObj to a blank form and notice the creation of labels and text fields for all columns of the Employee table.

You may need to expand your form to allow room for all the data values. I would also recommend increasing the size of the text fields of Midinit and Sex a little.

Add Command Buttons to Navigate Records

We will create two Command Buttons (Next, Previous) to navigate forwards and backwards through the returned record set, rsMyCommandObj. Add two Command Buttons to your form. Name the first Command Button "cmdPrevious" with a caption of "&<". Name the second Command Button "cmdNext" with a caption of "&>".

Place the Command Buttons in a convenient place on your new form. Double click the cmdPrevious Command Button to display and edit the code for the Click event. Add the following code to the event:

DataEnvironment1.rsMyCommandObj.MovePrevious

Double click the cmdNext Command Button and add the following code to the Click event:

DataEnvironment1.rsMyCommandObj.MoveNext

We will create one more Command Button to allow for a more graceful exit from your program. Add another Command Button and name it cmdExit. The Caption should be "&Exit". Add "End" to the Click event code.

We are now ready to test the first part of your new program. Save your Visual Basic project, ensure that DB2 is started, and run your program. You should be able to navigate forward and backward through the records of the Employee table.

Note: No error handling has been provided in the application. If you attempt to navigate outside the range of records you will receive a Run-time error.

Create Data Grid

We will now create a data grid based on the same SQL query. Open your DataEnvironment1 Data Environment in the Project Window. With the right mouse button drag and drop the MyCommandObj to the same form you added MyCommandObj previously.

From the Popup menu choose Data Grid. You will want to expand you new object and form to allow more room for data. Run your application to view the use of your Data Grid.

Add Graphics to Your Application

Pictures of some employees are located in the Emp_photo table. The following steps will allow you to add these pictures to your application. To begin we will need to modify MyCommandObj to pull pictures from the Emp_photo table.

Open Dataenvironment1 and right click MyCommandObj to obtain the properties of the object. Choose SQL Builder to display the SQL Design area. From the Data View Window expand the MyConnectObj until you can see the Emp_photo table.

Drag and drop the table into the gray area of the SQL Design area. Check the box next to Picture. Right click the diamond which connect the two tables and choose "Select All Rows from Employee."

Delete the Alias for the column Picture. Your SQL command should be:

SELECT EMPLOYEE.*, EMP_PHOTO.PICTURE

FROM { oj EMPLOYEE LEFT OUTER JOIN

EMP_PHOTO ON

EMPLOYEE.EMPNO = EMP_PHOTO.EMPNO }

The query will now return the binary pictures in your Record Set rsMyCommandObj. There, however, is no field on your form to handle this type of data. To do this add a PictureBox to your form and name it picEmployee_Pic. Change the following properties of picEmployee_Pic:

DataSource=DataEnvirionment1

DataMember=MyCommandObj

DataFormat=Picture

DataField=Picture

AutoSize=True

You may scroll through the records to see pictures of four different employees in three different formats.

Your application is a basic example of using Visual Basic 6.0 and DB2 Universal Database.