Using Visual Basic .NET Wizards to Interact with a Database

Let's begin by creating a new project named DBWizard. We won't concern ourselves just yet with the default form that's created. Instead, locate the Data tab in the Toolbox window and click on it. Your display should look similar to that shown in Figure 25.1.

Figure 25.1. The Data tab in the Toolbox window.

Now double-click on the OleDbDataAdapter object to move an instance of the object onto your form. Double-clicking on the OleDbDataAdapter object automatically invokes the Data Adapter Configuration Wizard, as shown in Figure 25.2.

Figure 25.2. The Data Adapter Configuration Wizard.

The wizard presents an introductory screen to tell you the purpose of this wizard. Click the Next button to proceed to the next screen, as shown in Figure 25.3.

Figure 25.3. The Data Adapter Configuration Wizard.

The purpose of this screen is for you to supply the name and path of the database you want to use. Stated another way, Figure 25.3 is asking you to establish the Connection object shown in Figure 24.3 in the previous chapter. The answer you supply here provides the link between the data adapter and the database itself.

If this is your first time using the wizard, you need to click on the New Connection button to locate the database file you want to use. We'll assume that no connection exists to the database, so click on the New Connection button. The program loads the Data Link Properties dialog box, changing the display to look like Figure 25.4.

Figure 25.4. The Data Adapter Configuration Wizard after clicking the New Connection button.

As you can see by the first line on the Connection tab, the wizard assumes you want to connect with Microsoft's SQL Server. (Hmmm I wonder why?) However, because we want to work with an Access database, you need to click on the Provider tab first. The display should now look like Figure 25.5.

Figure 25.5. The Provider tab of the Data Adapter Configuration Wizard.

The Provider tab presents a list of currently defined OLE database providers. OLE stands for Object Linking and Embedding and is a technology developed by Microsoft to support communication between software objects. The term Provider in this instance means that our system has the capability to connect with a specific database.

As you can see from the list in Figure 25.5, a number of providers are listed, including links to Access, Oracle, SQL Server, and ODBC. In our case, we want to select the Microsoft JET 4.0 OLE DB Provider, which is the device driver for Access. Click the Next button.

Your display should now look similar to Figure 25.6. Notice that the Connection tab shown in Figure 25.6 is different from the Connection tab shown in Figure 25.4. The reason is because the content of the Connection tab is context-sensitive to the selection you made on the Provider tab. (You could prove this to yourself by going back to the Provider tab, selecting the SQL Server option, and then clicking the Connection tab. The display would look just like Figure 25.4.)

Figure 25.6. The new Connection tab of the Data Adapter Configuration Wizard.

If you know the pathname to the Xtreme.mdb database, you could fill it into the first text box shown in Figure 25.6. Otherwise, click on the ellipsis button (that is, the one with the three dots) and navigate to the proper location and select the Xtreme.mdb database. The actual location depends on where you installed Visual Basic .NET. The default is C:\Program Files\Microsoft Visual Studio .NET\Crystal Reports\Samples\Database . Your selection then appears in the text box.

We'll accept the rest of the default values for the dialog box. However, as long as we're here, you might want to click on the Test Connection button to make sure that it's functioning properly. If you've filled in the dialog box correctly, you'll see a message box similar to that shown in Figure 25.7. Click on the OK button to dismiss the message box.

Figure 25.7. Testing the database connection.

Now click on the OK button to dismiss the Data Link Properties dialog box. Your screen will look similar to Figure 25.3 and should have the pathname to the Xtreme.mdb database shown in the list box. Click on the Next button. The screen should now look similar to Figure 25.8.

Figure 25.8. Choosing a query type for the Data Adapter Configuration Wizard.

Because we'll be using SQL commands, we'll accept the default choice to use SQL commands. Click the Next button. The screen changes to that shown in Figure 25.9.

Figure 25.9. The Data Adapter Configuration Wizard.

At this point, there are several ways to proceed. Because you already know how to write an SQL SELECT statement, you could type in a SELECT statement similar to the ones we examined in Chapter 24, "Database Fundamentals," and click the Next button. If you follow this route, your screen should look like Figure 25.10.

Figure 25.10. The Data Adapter Configuration Wizard after entering your own SELECT query.

As you can see from the first line in Figure 25.10, the data adapter is now configured. You could click on the Finish button to end the Data Adapter Configuration Wizard session.

If you click on the Advanced Options button in Figure 25.9, the display shown in Figure 25.11 appears.

Figure 25.11. The Advanced Options display for the Data Adapter Configuration Wizard.

The defaults shown in Figure 25.11 not only enable us to read the database, but also to add new records, edit existing records, and delete records from the database. If you click on the OK button, you're returned to Figure 25.9.

Because we haven't yet provided an SQL query, the Finish button still isn't activated. Therefore, click on the Query Builder button to present the Query Builder. The screen now looks like the one depicted in Figure 25.12.

Figure 25.12. The Query Builder dialog of the Data Adapter Configuration Wizard.

This should look somewhat familiar to you because the program we used in Chapter 24 is similar to the Query Builder. For example, select the Customer table from the list of tables shown in Figure 25.12. Click the Add button, and then click the Close button. (Some SQL queries can operate on two tables simultaneously . However, we'll use only the Customer table.) The display will look like Figure 25.13.

Figure 25.13. The Query Builder after selecting the Customer table.

Notice the partially constructed SQL query near the middle of the display. In the Customer dialog box, select the City, Contact First Name, Contact Last Name, and Contact Position fields from the Customer table. After each selection is clicked, the SQL query will be updated. When you have finished, the display will look like Figure 25.14.

Figure 25.14. The Query Builder after selecting the Customer table.

This query is similar to the query we used to generate Figure 24.10 in the previous chapter. Assuming that we're done building our query, click the OK button. Figure 25.15 now appears.

Figure 25.15. The completed SQL query.

Notice that we're actually back to Figure 25.9, but the query field has been filled in for us. (Because you're such an astute person, you could have just typed in the SQL query in Figure 25.9 and avoided the last six figures. Although I know you didn't need all this extra stuff, we did it just for completeness.) Click the Next button. You'll now see Figure 25.10 displayed. Click the Finish button, which dismisses the Data Adapter Wizard and presents the display shown in Figure 25.16.

Figure 25.16. The addition of the DataAdapter object to the project.

Notice that the OleDbDataAdapter1 and OleDbConnection1 objects have been added to our form for us. However, if you recall our discussion from Chapter 24, we still need to have a DataSet object for things to work correctly.