Working with Data Command Objects

Once you add a data connection to a project, you are ready to start manipulating the database. To accomplish this, you will usually add a data command object to your connection. A data command object provides the link between the data connection and a recordset.

A data command object is a named object living under a data connection. Where the data connection points to a database or some other source of data, a data command object contains some type of connection information that works against the target of the data connection. For example, suppose we create a data connection that points to the Pubs sample database that ships with SQL Server. We could create a data command object under this data connection that extracts all the authors from the Pubs database. Any type of command that can be executed by the database can be used with a data command object.

A nice feature of data command objects is the way they work within a project. When you create a data command object, an ASP file in the project can use it. The properties of the data command object can be changed once and will be reflected in every ASP file that uses the object, making maintenance of your database objects much easier.

You can create a data command object by following these steps:

1. Right-click DataEnvironment in Project Explorer, and select Add Data Command from the context menu. This will display the data command Properties dialog box.
2. Enter a name for this data command object in the Command Name text box.
3. Select the data connection to use from the Connection drop-down list.
4. Select the database object to use from the Database Object and Object Name drop-down lists, or enter the SQL to use for the command. Figure 5-7 shows the data command Properties dialog box with a SQL statement to select all of the records in the Authors table from the database.
5. Click OK to close the dialog box.

Figure 5-7. The data command Properties dialog box can be used to build a data command object's properties or modify them for an existing data command object.

Once you have created a data command object, it will be added to Project Explorer under the corresponding data connection. Figure 5-8 shows the new getAuthors data command object in Project Explorer.

Figure 5-8. Project Explorer exposes the fields in a data command object.

Figure 5-8 also shows how your multiple data command objects will look in Project Explorer. In this case we only have one data command object for each data connection, but we can easily create many data command objects for one data connection, as you will see in later examples.

If you want to create a data command object that contains the complete set of records in a table, query, view, stored procedure, or other type of database object, you can add the data command object by dragging the object from Data View and then dropping it on a data connection.

You can also add a data command object by copying the object and pasting it onto a data connection:

1. Right-click the object name in Data View.
2. Select Copy from the context menu.
3. Right-click the data connection you want to add the object to, and select Paste from the context menu.

This creates a new data command object based upon the database object you selected.

If you want to use an explicit SQL statement to select a set of records, select the SQL Statement option in the data command Properties dialog box and then click the SQL Builder button to start the Query Designer or enter the SQL statement in the SQL Statement text box. Later in this chapter, we will introduce the Query Designer. Chapter 12 also covers the Query Designer in more depth.

Working with Recordsets

Now that you have created a data connection and added data command objects to it, you are ready to use them in your project. The easiest way to use a data command object in your project is to add a recordset to an ASP file. This recordset object will work with the database through the data command object and use DTCs to display or change the data.

You can add a Recordset DTC to an ASP file by following these steps:

1. Open the ASP file you want to use in the editor.
2. Drag the data command you want to use from Project Explorer, and drop it in the Body section of the page.

You can also drag a Recordset DTC from the Toolbox and manually set its properties to point to a data command object.

Now that you have placed a Recordset DTC in the file, you can add DTCs for the fields you want to display. The easiest way to accomplish this with a data command object is by using drag and drop from Project Explorer:

1. Expand the data command you dropped on the page in Project Explorer. You should see the fields in the recordset, as shown in Figure 5-8.
2. Drag the fields you want to display from the data command object, and drop them on the page just after the Recordset DTC. For each field you drag onto the page, Visual InterDev creates a data-bound Textbox DTC to display the field. The page should look similar to the one shown in Figure 5-9.
3. Right-click each Textbox DTC, and change its properties to adjust the control's display. You can also add HTML formatting around the DTCs.

Figure 5-9. You can display data command fields by dragging them from Project Explorer and dropping them onto an ASP Web page.

Figure 5-9 shows a sample page after a data command object and its fields have been dropped onto it as described in the preceding steps. When you drag and drop multiple fields onto an ASP Web page, Visual InterDev creates an HTML table and inserts a Textbox DTC for each field into that table. Each of these Textbox DTCs is bound (linked) to the Recordset DTC. Figure 5-10 below shows the Textbox Properties dialog box for one of the DTCs.

The Recordset property of the Textbox DTC in Figure 5-10 is set to Recordset1, the default name of our Recordset DTC. The Field property has been set to au_lname, the name of the database column linked to this DTC.

Figure 5-10. The Textbox Properties dialog box for a data-bound Textbox DTC.

Figure 5-11 shows the Recordset Properties dialog box for the Recordset DTC. You can see that the Name property is set to Recordset1. The Database Object property is set to DE Commands, while the Object Name property is set to getAuthors.

Figure 5-11. The Recordset Properties dialog box allows you to change the configuration of the Recordset DTC.

You can, of course, set the Recordset DTC to use a SQL query, stored procedure, or view. These items will be explored in much more detail in Chapter 14, where you'll learn how to use the Recordset DTC's events and how to set other properties.

Visual InterDev 6.0 provides the RecordsetNavbar DTC, which controls navigation between database records. This control provides the buttons with which a user navigates through the records in a recordset. You can insert the RecordsetNavbar DTC in a page by following these steps:

1. Open the ASP file in the editor.
2. Drag the RecordsetNavbar DTC from the Toolbox, and drop it onto the page.
3. Right-click the control, and select Properties from the context menu. This will open the RecordsetNavbar Properties dialog box, as shown in Figure 5-12.
4. Select the Recordset DTC to use from the Recordset drop-down list. In Figure 5-12, this is set to Recordset1.

The RecordsetNavbar DTC provides First, Next, Previous, and Last buttons. You can use these buttons to move among the records displayed on the page. You can also customize the functionality of these buttons by modifying the script that they create or by changing certain properties in the RecordsetNavbar Properties dialog box.

Figure 5-12. The RecordsetNavbar Properties dialog box is used to bind the RecordsetNavbar DTC to the Recordset DTC.

If the recordset is updatable and you want users to be able to change the data, you might want to set the Update On Move property to True by checking the Update On Move check box. If Update On Move is True, the fields in the recordset will be updated with the new data in the data-bound DTCs whenever the user moves to another record. If Update On Move is False, the data in the data-bound DTCs will be discarded when the user moves to a new record.

The RecordsetNavbar's Scripting Platform property defaults to Inherit From Page and should reflect the value set for the page's Scripting Platform property. Figure 5-12 shows this property set to Server (ASP), which is the page's setting. Once you have selected a Recordset DTC in the Recordset property, the Scripting Platform property becomes read-only. If you want to change the Scripting Platform property after you have selected a Recordset DTC, highlight and delete the Recordset DTC name and then change the Scripting Platform property. Once you have changed the Scripting Platform property, you can reselect the Recordset DTC.

Figure 5-13 demonstrates how the RecordsetNavbar DTC works in the browser. I created a new page and placed four fields from the GetCustomers data command object on the page. Then I added the RecordsetNavbar DTC as we did in the previous steps. Next I saved the page and displayed it in the browser. You can see the results in Figure 5-13.

Figure 5-13. The RecordsetNavbar DTC provides HTML buttons for navigation between records.

Chapter 14 covers the Recordset DTC in detail, including its methods, properties, and events, as well as building complete applications with it and other controls.

Working with the Data Command Object Controls

You can adjust the way the data command objects work with the DTCs that are dropped onto a page. The Field Properties dialog box, shown in Figure 5-14, can be used to configure the properties of the fields within a data command object. You can change the DTC used when you drop the field onto a page and change the caption used in the HTML for the field.

Figure 5-14. The Field Properties dialog box is used to set the characteristics of a data command object's fields and how they behave when dropped onto a page.

You change the default DTC for a field by following these steps:

1. Right-click the field in Project Explorer.
2. Select Properties from the context menu.
3. Select the DTC to use from the Control drop-down list.

You change the default caption for a field by following these steps:

1. Right-click the field in Project Explorer.
2. Select Properties from the context menu.
3. Enter the new caption in the Caption text box.

To see how these properties work, I changed the properties for the GetCustomers data command object and then created a new page with them. You can see the results in Figure 5-15.

The captions for each field were changed. (Compare Figure 5-15 with Figure 5-13.) For example, you can see that Customer ID contains a space, while the field name does not (CustomerID). The CustomerID field is also displayed in a Label DTC, while the default control would have been the Textbox DTC, which is used for the other fields.

Figure 5-15. The properties for the fields of the data command object used in this page have been changed.