Displaying an Oracle 10G XE Table with Visual Basic 6.0

Visual Basic 6.0 is still a preferred programming option for many developers. With Oracle 10G XE being adopted by many because of its great number of virtues, connecting to Oracle with VB6 is bound to become very frequent. This tutorial shows how easy it is to display Tables, Views, and so forth from an Oracle 10G XE database in Visual Basic using the very versatile ADO Data Control.

Introduction

The ADO Data Control inherits many features from both DAO and RDO, its predecessors in data access. The data control will be using the ODBC connectivity with the database. Both VB6 and the Oracle 10G XE programs are on the same machine, XPHTEK, running on a Windows Professional XP operating system.

The objective of this tutorial is to show the steps that you should follow in displaying an Oracle 10G XE table, such as the one shown in the next picture.

Although there are 11 fields (columns) in this table we will display only a few of them.

{mospagebreak title=Create a Visual Basic Standard EXE Project}

Start a VB 6 Standard EXE project. The project is given the name Ado_OraXE in this tutorial. The caption of Form1 has been changed as shown in the next picture.

Add the ADO Data Control

Go to Project and click on it. In the drop-down click on Components to open this window. Scroll down in this window and choose the Microsoft Data Control 6.0(Sp4). This adds the ActiveX control whose file name is msadodc.ocx as shown. The control is now added to the toolbox where when you hover over it , a tool tip pops up with text which reads ‘Adodc.

Click on this control in the toolbox and place it on the form. Another way to do this is just to double click on this control when the form is open in the design view. When added, the control shows up as shown. Adodc1 is an instance of the ADODC Control. It comes with the navigational features as shown.

{mospagebreak title=Configure the ADODC Data Control}

After you add the control and right click it, you will see a drop-down as shown above. When you click on the ADODC Properties menu item, it pops up a wizard which will guide you to complete the task.

The wizard comes up with the default tab, General, where one of the most important pieces of information is configured, namely the connection. You need to choose a connection to the database. There are three options out of which we will be using an ODBC Data Source Name, known by its frequently used alias, ODBC DSN. Select this option as shown in the picture and click on the New… button.

Creating an ODBC DSN

This brings you to the Create New Data Source window. Again you have three possibilities. Read through the items in the window. We will be using a connection that applies to this machine only, the System Data Source. Click on the button Next.

This brings up the list of database drivers on this machine that allows you to connect to various databases. We will be using the Oracle XE driver that was registered when Oracle 10G XE was installed. Choose this driver by highlighting it. Then click on the button Next.

The next window which pops up is just a message as to what you did so far. Click on the button Next.

This brings up the Oracle ODBC Drive configuration. As usual you need to provide a name for this DSN. Here it is called VB_OraXe. For the TNS (Transparent Network Substrate) Service Name type in the name of the server, XE. The Description refers to the machine on which the sever is running. For UserID, you must use HR, assuming you have established a user of the HR Database by that name. If you have not taken this step, you must do before proceeding further. This is described in the Oracle 10G XE installation manual, or in the online help. You may also read the previous article on the DevShed.com site. You need not configure other items on this window. Click on the OK button to proceed to the next step.

This brings up the Oracle ODBC Driver Connect window asking for credentials. You just need to type in the password (hr) and click on the OK button.

Now in the next screen you can click on the drop-down next to Use ODBC Data Source Name and you should be able to find the ODBC DSN just configured, VB_OraXe as shown.

{mospagebreak title=Connect the ADO Data Control to the table to be displayed}

Click on the authentication tab. It should come up with the one you configured earlier. In case it does not come up with the credentials, you must use the User Name/Password to proceed to the next tab. Click on the tab RecordSource.

It comes up with the default, adCmdUnkown. Since we will be displaying the contents of a table, choose adCmdTable from this drop-down list. Click on the OK button.

This should now reveal the Table or Stored Procedure Name combo box. When you click on the drop-down, you should see all the tables in Oracle 10G XE’s HR database as shown. Click on the table Employees to choose it and then click OK.

Displaying the table using data bound controls

In this section the ADODC Data Control will serve as the source of data for the data bound controls, and we will see how it is done. You may use any of the data bound controls such as Textbox, Listbox, or Combobox and bind them to data using this control. In this tutorial, to keep it simple, only the Textbox control will be used. Place four textboxes and four labels as shown on the form. Each of these text boxes will be bound to a column in the Employees table. For example, the next couple of pictures show how you may bind the Employee_ID column on Oracle to Textbox1. Although labels in VB6 can also be bound to data, resist doing so for this tutorial.

Right click Textbox1 to bring up its Properties window as shown. You may click on the Categorized tab so that you can concentrate on data-related items. When you click on the empty space alongside the DataSource you should be able to pick up Adodc1. You could have a number of ADO Data controls on a form and all of them will show up in the drop-down list. Next, click on an empty area alongside the DataField item (shown in red, “click here”). In the empty area you will see a drop-down list from which you now choose Employee_ID. By this choice you have bound Textbox1 to the EmployeeID Column. Even at this point you may run the project and see only the Employee_ID.

Similarly configure the other textboxes. In this tutorial the third textbox has been bound to the telephone number field in Oracle 10G XE. You may also format this to show the telephone number. For this you need to click on an empty area of the DataFormat item in the bound controls Properties window. This brings up this next window where you may supply formatting to be displayed. An example for the column Telephone is shown. This custom formatting does not seem to be working, as the format string does not conform with the one configured. The way it is showing up is exactly the same as you would see in the Oracle database table.

Now you may run the project to display some of the columns in Oracle 10G XE’s Employee table in the HR database. You may click on the navigational keys to move to the first, last, next and previous positions.

Summary

Connecting to an Oracle 10G XE database table using Microsoft’s ADODC control was described in this tutorial. Once you get past the successful testing of the ODBC connection the rest of it is very easy. Although an example of binding to the textbox is shown, it is relatively easy to connect to other databound standard controls as well as the DataList (Microsoft DataList Control 6.0, MSDATALIST.ocx) and DataGrid (Microsoft DataGrid Control 6.0, MSDATAGRID.ocx) controls. In this case you may have to add these ActiveX controls to your toolbox.

[gp-comments width="770" linklove="off" ]