Using Data Commands

Learn how data commands help you create robust, easy-to-maintain applications

This month, I'll show you how to use the Visual Basic (VB) data command in the Data Environment to build a simple application that uses parameterized queries. This technique reduces the amount of code you need to write (if you previously used ADO directly) and produces an application that's easy to maintain. The demo application I'll present uses the Northwinds database, which comes with SQL Server. The application uses the Combo control box to present a list of customers to the user. The detailed customer information fields do not appear when the application first starts.

When the user selects a customer, the application uses a parameterized data command to look up the customer and displays detailed information for that customer. Screen 1 shows the application interface with a customer selected.

Creating the Data Commands

Here's how this simple application works. First, create a new Visual Basic (VB) project. Select a Data Project from the New Project dialog box, then click OK. This action creates the new project and adds the Data Environment Designer and a single form to the project.

To create a data connection in the Data Environment Designer, open the Data Environment. When you first create a data environment for your project, VB will create a new data connection. You can open the Data Environment property pages to configure the connection.You can create additional data connections later by clicking the Add Connection button on the toolbar. Click the Connection tab, then select the Data Source or create a new one. When you finish setting the properties, click OK to close the properties. Right-click the connection name, then select Rename and rename it. The connection is ready to use.

Next, you can create the data command objects that do the database work. My example required two objects—one to retrieve all the customers to fill the list and another to retrieve a specific customer. The first command is cmdCustomer, which retrieves all customer records. You can create a new data command by right-clicking the data connection in the Data Environment or clicking the Add Command button on the toolbar. Next, open the property pages for the data command. Fill in the command name and add the following SQL text:

SELECT CustomerID, Description, CompanyName,ContactName, ContactTitle, Address, City,Region, PostalCode, Country, Phone, FaxFROM Customers

Click OK to close the Properties dialog.

Next, create a command to retrieve a specific customer called cmdCustomerByID. Open the properties for the command and change its name. Click the SQL Builder button on the Commands property page to launch Query Designer. Screen 2, page 67, shows the completed query in Query Designer. The easiest way to build this query is to drag the Customers table from Data View and drop it onto Query Designer. Then, in the top pane, check the fields you want to include in the query. The SQL text is

SELECT CustomerID, Description, CompanyName,ContactName, ContactTitle, Address, City,Region, PostalCode, Country, Phone, FaxFROM Customers WHERE (CustomerID = ?)

The question mark is a placeholder that lets Query Designer know you parameterized a query. You can use the placeholder in the criteria column to create a where clause. You can use any legal SQL operator with the placeholder. In this example, I used the = operator to find an exact match to the CustomerID. Also, you can use operators such as "like". After you complete the query, close Query Designer. At the prompt, answer Yes to save your changes. For the moment, ignore the message that Query Designer cannot determine the query parameters.

Next, display the properties for the cmdCustomerByID, then click the Parameters tab. This tab lets you change the properties for each parameter. The VB Query Designer doesn't correctly obtain parameter properties for a parameter, so you need to set them here. First, set the parameter name so you can readily identify it by name. Screen 3 shows the parameter properties for this example.

Next, set the data type properties. To find the data type properties, you can close the command properties and use Data View to display the field properties related to the parameter. Write down these properties, then open the command properties again, switch to the Parameters tab, and set the properties. Set the Data Type and Host Data Type properties and the Precision, Scale, and Size properties. Click OK to close the properties. If you make a mistake in setting the parameters, the command will bomb when it executes. The error message will show an improper data type setting, which will lead you back to the Parameters tab. Now you can close the Data Environment Designer.

Building the Interface

My sample application has only one form, the one VB automatically creates. Add a Combo box control to the form and name it cboCustomer. Next, add a label control to the left of the Combo box and set its caption to Customers.

In this example, I used a frame to contain the detail controls. To work with a set of controls as a group, you can use a container. For example, after adding the frame, you can set its Visible property to False, delete the caption, and set the name to frameCustomerDetail. You can make the frame and its controls visible by setting the Visible property in code as in Listing 1. This functionality makes the interface more intuitive for the user, who sees the detail fields only when they contain data.

Now, add the label and textbox controls shown in Screen 4. You can leave the labels with their default names, but change the names of the textbox controls (in the following order) to:

Now that you've completed the interface design, save the application.

Building the Database Code

Now you can add the code for database access routines. First, use the Project/Add Module command to create a new code module. Rename this module Customer.bas. This module contains the LoadCustomer function, which retrieves all the customers from the database and returns them to the calling code. This function uses the cmdCustomer data command to retrieve the customers. Add the function declaration. As the first lines of the function, add these variable definitions:

Function LoadCustomer() As    VariantDim sCustomerList() As StringDim i As Integer, j As Integer

The sCustomerList variable is an undimensioned array that will contain the records retrieved from the database. The first step in programming using data commands is to open or execute the recordset. The Data Environment automatically creates a recordset object for you. The object will be named rs plus the name of your data command. In this example, the data command is rscmdCustomer. In this routine, enter the following line to open the recordset:

envNorthwind.rscmdCustomer.Open

Next, you need to know whether the recordset returned any records. You could use the EOF property to loop through the recordset, but if you do, you also need to redimension the sCustomerList array to the exact number of records in the recordset. SQL Server will return this array from the function to the calling code. To retrieve the record count, you can use the RecordCount method. Now, enter this line to retrieve the number of records in the recordset:

   i = envNorthwind.rscmdCustomer.RecordCount

You can use the number of records returned to test for a valid recordset and to redimension the sCustomerList array:

   If i > 0 Then      ReDim sCustomerList(i - 1)

Now, you can start processing the records in the recordset. The following code uses a Do While statement to loop through the records until an EOF condition occurs. SQL Server processes each record by taking the CompanyName and CustomerID fields from the recordsets Fields collection and placing them in the sCustomerList array:

i = 0Do While Not envNorthwind.rscmdCustomer.EOFsCustomerList(i) = envNorthwind         .rscmdCustomer _.Fields
         ("CompanyName") & " (" & _         envNorthwind.rscmdCustomer
         .Fields("CustomerID") & ")"      envNorthwind.rscmdCustomer.MoveNexti = i + 1   Loop

Use the MoveNext method to move to the next record in the recordset. After the routine finishes processing the recordset, close the routine using the Close method. Then, complete the If statement:

      envNorthwind.rscmdCustomer.Close      End If

Finally, set the return value of the function to the sCustomerList array:

      LoadCustomer = sCustomerList

Now you can create the function that looks up the customer details. The function uses CustomerID as a parameter and returns the detail records in an array. You can define this function, GetCustomer, using this code:

Function GetCustomer(CustomerID As String)   As VariantDim sCustomer(5) As StringDim i As Integer, j As Integer

Next, make sure the sCustomer array contains empty records.

   For i = 0 To 4      sCustomer(i) = ""   Next

The cmdCustomerByID data command uses a parameter that contains CustomerID. You can use one of two methods to set the parameter and execute the command. I recommend you perform these actions in one step using this syntax:

   envNorthwind.cmdCustomerByID CustomerID

This command executes cmdGetCustomerByID and passes the parameter in on the command line, which makes the code easy to follow and reduces the number of lines required to perform these actions.

Next, you can retrieve the number of records in the recordset as demonstrated earlier. The current function doesn't return more than one record because you're performing the lookup by CustomerID, which is a unique field. The code to retrieve the record count and check for a valid record is:

   i = envNorthwind.rscmdCustomerByID.RecordCount   If i > 0 Then

Next, you can use a block of IF statements and assignment statements to retrieve field values. The IF statement uses the IsNull function to make sure the recordset field returns a valid value. If you don't check for a null value, VB will generate a run-time error when it encounters one. The following code prevents run-time errors. The If statement retrieves the value from the recordset and stores it in the correct position in the array, Listing 2, page 69 shows. Finally, close the recordset, and set the return value from the function:

      envNorthwind.rscmdCustomerByID.Close      GetCustomer = sCustomer

Listing 1 shows the code to wire these functions into the events behind the interface.

The Data Environment is a useful addition to the database features in VB. Although the Data Environment adds overhead to the application's execution speed, it simplifies application development and maintenance. This simplification can result in significant savings in resources during the application's life. You can use the data commands in this article as an example to create database objects and reuse them in your code. The data command makes application maintenance easy: If you change the data command, you'll affect everything in your application that uses the command.