2.5. Database Operations in Visual Basic

In this section, we will discuss the connection between Visual Basic and databases.

There are different ways to access data sources from within Visual Basic, especially for remote client-server database applications. The following methods are most utilized to access a data source from the Visual Basic programming environment:

• Data Access Object (DAO)

• Data Control

• ODBCDirect

• Remote Data Object (RDO)

• Remote Data Control (RDC)

• Open Database Connectivity (ODBC)

• Visual Basic Library for SQL Server (VBSQL)

• ActiveX Data Objects (ADO)

There are so many introductions to the principles and applications of different database implementations on Visual Basic, we don't want to duplicate those materials here. We want to concentrate on the ActiveX Data Objects (ADO) in this section because it is a very popular technology today. We will illustrate how to connect and access data sources from within Visual Basic by using two examples. To make things simple, this discussion is limited to the local database.

Visual Basic uses an approach provided by Microsoft, called Universal Data Access (UDA), to access the different data sources. The UDA approach uses a set of complex interfaces, called Object Link and Embedding DataBase (OLE DB), which provides uniform access to data stored in a variety of formats. Because of the complexity of the OLE DB, Visual Basic will not directly interface to it. Instead Visual Basic will access the OLE DB through ActiveX Data Object (ADO). A functional block diagram illustrating the connection between Visual Basic and data sources is shown in Figure 2-107.

In addition to allowing you to access the different data sources, you can also use Visual Basic to create a Microsoft Access database using the Microsoft Jet Engine. The Jet Engine allows databases created in Visual Basic to be read and manipulated by Microsoft Access, and those created in Microsoft Access to be read and manipulated by Visual Basic. To use the Jet Engine, you need to open and implement the Visual Data Manager that resides in the Add-In menu item in Visual Basic. Because we want to concentrate on accessing and implementing the database in this section, we will not discuss this issue here; we will create a Microsoft Access database manually later.

We develop two examples to illustrate the steps and functionalities of accessing databases developed in Microsoft Access by using ADO from within Visual Basic. The example in Section 2.5.1 is very simple; the example in Section 2.5.2 includes a full functionality of accessing a Microsoft Access database.

2.5.1. A Simple Example of Accessing Databases Using ADO

You can use ADO data control to access data stored in different formats, such as a database, a datasheet, or a text file. In this example, we want to develop a Visual Basic project to access a database created in Microsoft Access. We want to connect the Visual Basic project with the database, query data from the database, and display the records received in textboxes on the Form of the project.

First, we need to create a database in Microsoft Access. Open Microsoft Access and create two new tables: name one Faculty and the other Course. These two tables are used to store information about the faculty members in a department, such as the faculty member's name, office number, phone number, and the courses taught.

The Design Views of the Faculty and Course tables are shown in Figure 2-108 and 2-109, respectively. We create very limited records for these tables to illustrate the application of this database. You can create as many records as you like for your application. The Datasheet Views of the two tables are shown in Figure 2-110 and 2-111.

From the Root directory in Windows Explorer, create a folder named database. Save the database we previously created to this newly created folder C:database. Use Faculty and Course as the names for the two tables, and save the database as JCSUClass.

Now, close Microsoft Access and launch Visual Basic 6.0; create a new Standard EXE project. In the newly created project, click File|Save Form1.frm As to save our Form as Success.frm to the user-defined project folder C:Vb. Also click File|Save Project As to save our project as Success.vbp to the user-defined project folder.

2.5.1.1. Connect to Database and Record Sources

In order to use ADO to access the database we created, we need to add the ADO Data Control to our Visual Basic project. Click Project|Components. . . from the Visual Basic menu bar to open the Components dialog box, shown in Figure 2-112.

Click the Controls tab (this is the default tab, so you may not need to click it), then click the scroll bar and browse the list until you find Microsoft ADO Data Control 6.0 (SP4)(OLEDB). Select this item by checking the checkbox in front of it, as shown in Figure 2-112, and click the Apply and OK buttons to add this control to our project. Close the dialog box. You will find that an ADODC icon has been added into the Toolbox window in our project.

Now, we can set the connection between our Visual Basic project and the database JCSUClass.mdb that we created by using ADO Data Control. Click the newly added ADODC icon from the Toolbox window, drag it to our Form, put it in the upper-right corner and enlarge it to a suitable size. The added ADODC looks like the one that is shown in Figure 2-113.

Perform the following steps to finish this connection:

• Right-click the newly added ADODC icon on the Form and select ADODC Properties from the pop-up menu to open its Property Pages dialog box, which is shown in Figure 2-114.
  
  
  

  Figure 2-114.

  

  
  
• In the opened dialog box under the General tab, keep the Use Connection String radio button selected, then click the Build. . . button to open the Data Link Properties dialog box. In the opened dialog box, keep the Provider tab selected, click Microsoft Jet 4.0 OLE DB Provider from the list, as shown in Figure 2-115, and click the Next>> button to open the Connection page to set the database to be connected.
  
  
  

  Figure 2-115.

  

  
  
• On the opened Connection page, click the . . . button next to the Select or enter a database name: text field and browse through folders in our user-defined database directory, C:database, then select our database JCSUClass.mdb and click the Open button to add this database to this link page. Also, keep the Blank password checkbox checked on this page to make sure that we didn't use any password for this database. Of course, you can set a password for this database to limit access to it by unchecking this checkbox and entering a password if you like. In the User name: text field, keep the original Admin unchanged. Your finished Connection page should match the one that is shown in Figure 2-116.
  
  
  

  Figure 2-116.

  

  
  
• Now click the Test Connection button, which is located on the bottom-right corner of this page, to test our database connection. A successful message box should be displayed on screen if our connection is error-free, as shown in Figure 2-117.
  
  
  

  Figure 2-117.

  

  
  
• Click the Advanced tab to open the Advanced property page for this connection, which is shown in Figure 2-118.
  
  
  

  Figure 2-118.

  

  
  Make sure that the Share Deny None item is selected from the Access permissions: list. This will allow us to perform both read and write operations on this database.
  
  

Now, click the OK button to save these settings and close the Data Link Properties page. Control now returns to the Property Pages dialog box. You will find that our connection string:

Microsoft.Jet.OLEDB.4.0;Data Source = C:databaseJCSUClass.mdb

has been added into the Use Connection String text field.

In addition to setting the ConnectionString property, you also need to set the ADO Data Control's RecordSource property if you want to perform the data operations to the database, because the data unit that is fetched out from the database is the record.

From the Property Pages dialog box, click the RecordSource tab to view the RecordSource page, shown in Figure 2-119.

Select 2 − adCmdTable from the Command Type list box, and select Faculty from the Table or Stored Procedure Name list box. In this way, we set a connection between the RecordSource in this ADODC (ADODC1) and the Faculty table in our database JCSUClass.mdb.

Click the Apply and OK buttons to save these connections and close this dialog box. Now we have connected our Visual Basic project to one of the tables in our database, the Faculty table. We need to set another connection between our project and the Course table in our database. Do this in the same manner. Add ADODC2 to our Form from the Toolbox, and set the ConnectionString and RecordSource for this ADO. When you set the RecordSource from the RecordSource page, you should select the Course table for this record source.

2.5.1.2. Develop a User Interface

After adding and connecting two ADOs to our Form, we need to add other components to the Form to finish our user interface design. Add the following components to the Form, so that the screen looks like that shown in Figure 2-120.

After you set the connection between the RecordSource and our Visual Basic project, you can also set a one-to-one relationship between a component in a Visual Basic Form and a Field in a database table; and this mapping relationship can be dynamically connected and updated as the project runs. For example, if you set a one-to-one mapping between the txtName textbox and the NAME field in the Faculty table, ADO can automatically connect the txtName textbox with the NAME data field in the Faculty table and update this mapping as the project runs. To set a one-to-one mapping between a component in Visual Basic and a data field in a database, follow these steps (assume that we set a mapping between the txtName textbox and the NAME field in the Faculty table):

• Click the textbox txtName from Visual Basic to select this component.
  
  
• Go to the Property window and click the DataSource property, then select Adodc1, which is connected to the Faculty table in our database.
  
  
• While still in the Property window, click the DataField property and select the NAME data field.
  
  

Now, run the project by pressing the F5 key on the keyboard. You will find that the first item in the NAME data field, Ying Bai, is displayed in the txtName textbox. This means that the ADO has set the mapping between the txtName and the NAME field and updated the value for the component in Visual Basic automatically as the project runs.

In this application, we want to access and fetch records from the database by Visual Basic programming, so we don't want to use this automatic updating method. Click the txtName textbox, delete all settings that we just did, and recover the original textbox that is mapped to nothing.

There are two styles when connecting to a database using ADO. One is that the program codes are composed of pure ADO methods or properties, and the other one is that the program uses a combination of ADO and SQL codes. In our application, we use the first style, pure ADO methods and properties.

There is an issue that you should pay attention to when using this style: You must set a connection between one component in Visual Basic and an ADO if you want to use that ADO to access the table associated with that ADO in the database. Otherwise, the ADO cannot recognize any component in the Visual Basic domain because there is no relationship between the ADO and components in the Visual Basic program. This is very important when you want to access a database via ADO by using programming codes. You can set this mapping between an ADO and some pseudo-components in the Visual Basic domain. The so-called pseudo-component is not exactly used in the program; the only purpose of this pseudo-component is to set a mapping relationship between an ADO and components used in Visual Basic programming.

In this application, we add one more textbox as our pseudo-component that will be mapped to ADODC1, which is connected to the Faculty table in our database. For the Course table, which is connected to ADODC2, we will set a direct mapping to the txtCredit textbox and use ADODC2 to automatically update that textbox.

Select a textbox component from the Toolbox window and add it into the upper-left corner on our Form, and set the following properties:

In this way, we create a pseudo-component and map it to ADODC1. Furthermore, we set a relationship between components in Visual Basic and ADODC1 to the Faculty table in our database. After this mapping, any other components in this Form can be recognized by ADODC1 and can access the Faculty table from within Visual Basic.

The Visible property is set to False, which means that we don't want this textbox to be seen as the project runs. So it is a pseudo-component. Also, set the Visible properties to False for two ADODCi (i = 1, 2) because we want them to be invisible while the project runs.

Now, set the mapping between the txtCredit textbox and ADODC2. Click the txtCredit textbox to select it, and go to the Properties window to set the following properties for this component:

In this way, txtCredit is mapped to ADODC2 and its content will be automatically updated by ADODC2 when the project runs.

2.5.1.3. Code the Application

Open the code window and select General from the Object list to open the Form's General Declaration section, type the Option Explicit statement in this section, and press the Enter key. A Form's level variable found, which is a Boolean variable, is also declared here. This variable is used to monitor and hold the status of accessing the database from within Visual Basic.

Next, select the Form item from the Object list to open the Form_Load event procedure, and enter one initialization code in this procedure: found = False. Your finished code for these two sections is shown in Figure 2-121.

Notice in Figure 2-121 that we added an End statement to the cmdExit event procedure to terminate the program. Now, select cmdSearch from the Object list to open its event procedure and enter the code shown in Figure 2-122 into this procedure.

One point we want to emphasize is that we utilized the Fields() method and Value property to fetch all matching records in the coding. This is a very efficient method for finding matching records in the ADO style's application.

The Content textbox and the Write button are reserved for the next example. At this point, we have finished coding for this project. Press F5 to run our project. Our user interface is displayed on screen, as shown in Figure 2-123.

Type a faculty member's name into the Name textbox, such as James Bond, and then click the Search button. All information concerning this faculty member will be displayed in the associated textboxes, and all courses taught by this faculty member will also be displayed in the list box, lstCourse, as shown in Figure 2-123.

If you type a wrong name or you enter nothing in the Name textbox, as you click the Search button, a message box with an error message will be displayed on screen to remind you “No Match Found”. You need to double-check the name you typed and try again. Our first project is successful. Click Exit to terminate the project.

All program files, including the Visual Basic Form file, Project file, executable file, and database, are stored on the accompanying CD-ROM in the Chapter 2VBDB1 folder. You can load these files onto your computer to run this project. You need to create a new folder at your root drive, C:database, and copy the database file JCSUClass.mdb to that folder. Then you can directly run the executable file Success.exe.

In the next section, we will develop a more complicated program that has more functionalities to access the database.

2.5.2. A Complete Example of Accessing Databases with Full Functionality

In the last section, we developed a simple example that is used only to query records from a database. We want to add more functionalities to that program, such as the ability to write text and detect errors. Also, we plan to add more components to our program to facilitate the users.

To add the Write functionality, you need to provide some necessary information to the user, such as the table name (indicates to which table you want to write), the field name (indicates to which field you want to write), and the content that you want to write to. We want to use two ComboBoxes to provide that information to users. One is used to provide the Table name, and the other is for the Field name.

Open the project Success.frm and Success.vbp that we developed in the previous section. Make the following modifications to the Form:

• Move the txtOffice textbox and the associated label to the upper-right corner.

• Move the txtAddress textbox and the associated label to the upper-right corner, and place them below the txtOffice textbox.

• Add one ComboBox with the Name property of cmbWTable.

• Add one label with the Caption property of Table Name, and place this label in front of the ComboBox.

• Add another ComboBox with the Name property of cmbWField.

• Add one label with the Caption property of Field Name, and place this label in front of the second ComboBox.

• Move two ADODCi to the bottom of the Form.

Your modified Form should match the one that is shown in Figure 2-124.

Three components will not be displayed on the Form when the project runs. They are:

• pseudo-component, which is a textbox, txtMark, and positioned at the upper-left corner of the Form;

• ADODC1; and

• ADODC2.

Make sure to set the Visible property to False for these components from the Properties window.

Now, let's take care of coding this project. Some of the coding is identical to the last example, such as the coding for the Search command button and for the Exit command button. We will concentrate on the coding for the Write command button and for the ComboBoxes, cmbWTable and cmbWField, in this project.

Open the Code Window by clicking the View Code button from the Project Explorer, select the General item from the Object list, and enter the code shown in Figure 2-125 in this section.

One more Boolean variable, ok, is declared here. The purpose of this Form's level variable is to monitor and hold the checking result for the ComboBoxes cmbWTable and cmbWField. This checking is necessary because if there is no input to these two ComboBoxes, you cannot continue to process the Write operation. Your Write operation would fail because the ADODC would not know which table and which field in your database should be updated by the Write operation.

The coding for the Form_Load event procedure is also shown in Figure 2-125. Two Boolean variables, found and ok, are initialized here (both are set to False). Two ComboBoxes are initialized with two strings, Faculty and Course, which are the names of the two Tables in our database. In this way, the user can make selections from the two tables to write a new value to a certain table. The Exit event procedure is identical to the Exit procedure in the last example.

Now, let's code for a Click event in the cmbWTable ComboBox. During the initialization stage (Form_Load), two Table names, Faculty and Course, have been added into this ComboBox. When the user clicks this ComboBox to select one table, we want to show all fields of that table in the cmbWField ComboBox to allow the user to select one field to which a new value will be written by the user. These fields are dependent on the selected table, which means that different fields should be added and displayed in the cmbWField ComboBox based on the different table selected by the user; you cannot add and display any field to this ComboBox until the user has selected a table.

Open the Code Window and select cmbWTable from the Object list, and make sure that the event of this cmbWTable object is Click (sometimes the default event Change is selected) by selecting Click from the Procedure list to open the Click event procedure. Enter the code shown in Figure 2-126 into this procedure.

One more thing we need to do is to check both ComboBoxes, cmbWTable and cmbWField, to make sure that we have the correct Table and Field before we can execute the Write command to write new data to the database. Because this check includes a relatively long code, we prefer to create a user-defined function to enclose this check. To create a user-defined function in Visual Basic, click the Tools|Add Procedure. . . menu item from the Code Window to open the Add Procedure dialog box, as shown in Figure 2-127.

Enter CheckInput in the Name: field as the name for this function. Check two radio buttons, Function and Private, as shown in Figure 2-127, to make this procedure a private function. Click OK to open this function.

This function will return a Boolean value to indicate the check result. A returned value of True means that there is a valid Table and Field in two ComboBoxes, and we can continue to process the Write operation. Otherwise, a returned False means that the value in either cmbWTable or cmbWField is invalid, and we cannot continue to execute the Write operation. In the latter situation, a message should be displayed on screen to remind the user to enter valid values in the associated ComboBox.

Attach the words As Boolean following the CheckInput function, as shown in Figure 2-128, to make this function return a Boolean value.

Now, we are ready to code for the Write event procedure. Select the cmdWrite item from the Object list in the Code window to open its event procedure, and enter the code shown in Figure 2-129 into this procedure.

Now, click Run|Start from the Visual Basic menu bar to run the project. Our user interface is displayed on the screen shown in Figure 2-130.

Type a faculty name, such as James Bond, into the Name field, then click the Search button to receive all information about this faculty member. The office num ber, address, and courses taught by this person are displayed in the associated fields and list box, as shown in Figure 2-130.

Now, click the Table Name ComboBox and select Faculty, then click the Field Name ComboBox and select ADDRESS. Type USA into the Contents textbox, and click the Write button to write this new address into the Faculty table. Now, try to click the Search button again. This time you will find that the address of this faculty member, which is the content of the txtAddress textbox, has changed from the original TURKEY to USA.

Now, delete all text from the Table Name textbox to make it empty (clean). Click Search again and there is no problem; all information about this faculty member is displayed on the Form. Now, try to click the Write button to write something into the database. What happens? A message box like that shown in Figure 2-131(a) is displayed on screen. Now, reselect Faculty from the Table Name, but delete any text in the Field Name textbox. Click the Write button again. What happens now? A message box like that shown in Figure 2-131(b) is displayed on screen. You can continue to try to reselect ADDRESS from the Field Name textbox and delete any text in the Contents textbox. Click the Write button and what happens? You can imagine it yourself.

Okay, everything seems perfect now. But some readers might have discovered a little problem in this project. When you try to write new data to the database, you need to click the Search button two times to display this new data on the Form. For example, when we tested the Write functionality earlier, we selected Faculty from the Table Name textbox and ADDRESS from the Field Name textbox. We entered USA into the Contents textbox as a new data to be written into this Faculty table when the Write button is clicked. After we clicked the Write button to write this data into the Faculty table, we clicked the Search button again to confirm this Write. But you have to click the Search button two times to make this new address display in the txtAddress textbox. Why?

The Refresh operation is the reason for this. Each time we find a matching record from a table, we always call the Refresh method to update the table. From the point of view of practical application, it is not necessary to do that. The key is that we must execute this Refresh method as soon as we have finished a Write operation. So let's comment out both Refresh methods in the Write event procedure. Move one Refresh method originally located inside the rs2 While loop and above the writing statement:

rs2.Fields(cmbWField.Text).Value = txtWContent.Text

to below this statement. Your finished modifications for this Refresh method should match those shown in Figure 2-132. The commented-out statements have been highlighted in a light gray screen, and the newly added statement is highlighted in a dark gray screen.

Now, if you test the Write functionality as we did earlier, you will find that you only need to click the Search button once, and the updated result will be displayed on the Form immediately with no problem. Click the Exit button to end our project. Now, save the project to our user-defined project directory C:Vb. First, save the Form as Success2.frm, and save our project as Success2.vbp.

A completed project, including the Form file, Project file, database file, and executable file, is stored on the accompanying CD-ROM in the Chapter 2VBDB2 folder. You can copy all files and load them onto your computer to run them. You first need to create a user-defined database folder at your root drive, such as C:database, and copy the database file JCSUClass.mdb to that folder. Then you can run the executable file Success2.exe on your computer.