Using Data Commands

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, Fax
FROM 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, Fax
FROM 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: