Linking Objects with the Grid DTC

Downloads
5338.zip

In "Getting to Know Grid DTC," May 1999, I explained how useful the Grid Design Time Control (DTC) is for displaying information from a SQL Server table on a Web page. This month, I'll show you how to extend that functionality by linking a Grid DTC to another data-bound object to display parent/child relationships.

Parent/child relationships occur often in SQL Server implementations. The frequent occurrence of these relationships is a primary reason why relational databases such as SQL Server are so useful. Candidates for parent/child display include any table linked to another by a foreign key. Table 1 shows such a relationship—a table of contacts and another table of phone numbers belonging to those contacts. The table Phones lets each contact in Contacts have an unlimited number of phone numbers and provides a column for a description of each number. Listing 1, page 54, is the SQL script to create this table.

Other examples of parent/child relationships include line items on an order, shipping and billing addresses of customers, accounts of a single bank customer, and employees' dependents. Parent/child relationships occur so often that you can probably think of three more for every one I listed. Surprisingly little code is required to provide parent/child viewing functionality, primarily because of the excellent support data-bound DTCs afford. You need one control from which to select the parent of interest and a second with which to display the children.

For this example, I'll use a listbox for the parents and a Grid DTC for the children. The Grid DTC is especially suited for displaying children because of its inherent support for displaying a variable number of records and paging through those records. For large child datasets, this ability is especially valuable.

Because use of a foreign key frequently facilitates (and often enforces) parent/child relationships, I'll demonstrate how to display one field in the listbox while keeping track of another. Usually, the appropriate field to display for user selection is something other than the key. In this example, I'll display a name in the listbox (parent) and fill a grid (child) with the corresponding foreign key values.

Because the relationship between the tables is based on the key value, you need to keep track of that value to use it in the parameterized query to populate the Grid DTC. You need to pass the key value, not the name displayed.

Building the Page

After adding a new page to your Visual InterDev project, you'll begin placing the necessary DTCs on the page. As you add DTCs, you might get a prompt about enabling the Scripting Object Model, as shown in Screen 1. For a DTC to work, you must respond affirmatively.

Last month, I used VBScript in my examples; this month I'll try JScript. For those not familiar with JScript, remember this: It is case-sensitive, so be careful when referring to the names of variables, objects, methods, properties, or functions.

You'll find three functions in Listing 1. The passContact function updates the Grid DTC with information for the currently displayed parent. The ondatasetcomplete function calls the passContact function whenever Recordset1 has been updated. This processing is necessary to populate the Grid DTC the first time the page is drawn. The listbox's onchange function calls the passContact function when you make a new parent selection in the listbox.

Follow these detailed steps to add DTCs, set their properties, and place the necessary code on the page.

1. Create a new Web project.
2. Add a Data Connection. Rightclick on Global.asa in Project Explorer, and select Add Data Connection.
3. Select the Data Source Name (DSN) that points to the SQL Server database that contains your sample Contacts and Phones tables.
4. Add a new Active Server Page (ASP) on which to place data controls. Right-click the project in Project Explorer, and select Add, then Active Server Page.
5. Add a Recordset DTC to the page. Drag a Recordset DTC from the Design-Time Controls Toolbox to the page, immediately following the <BODY> tag.
6. Add a Listbox DTC to the page. Drag a Listbox DTC from the Toolbox to the page, immediately following the Recordset DTC just added. The listbox must be named lstContacts, to match the name of the List Box in the code. If these names don't match, the JScript event-handling functions won't be called.
7. Add another Recordset DTC just below the Listbox DTC.
8. Place a Grid DTC below the second Recordset DTC.
9. Set the Connection property of both Recordset DTCs to the Data Connection that you added in Step 2 above.
10. Make sure the Database Object selected for Recordset1 is Tables and set the Object Name to the Contacts table.
11. Right-click the Listbox DTC, and select Properties. On the General tab, set the Recordset to Recordset1 and the Field to the field containing the foreign key for the linked child table. In my example, this field was ContactID.
12. To display a meaningful value in the listbox while retaining the key associated with that value, on the Lookup tab set the rowsource to Recordset1, the Bound column to the linked field for the foreign key in the child table, and the List field to the column you want to display. In this example, the Bound column is ContactID and the List Field is Name.
13. Recordset2 requires you to enter a query, which you can do from the Properties dialog box. Right-click the Recordset2 DTC, and select Properties to view the dialog box. Screen 2 shows the settings for Recordset2's General tab. The SQL query you enter links the tables; returns the necessary fields for display; and is parame- terized, through use of the question mark, for selecting a record of interest. On the Implemen-tation tab, deselect the checkbox to Automatically Open the Recordset.
14. Right-click to obtain the Grid Properties dialog box, then select the Data tab to choose the fields you want to display in the grid representing the child table's information.
15. Between the <Head> and </Head> tags, add the code shown in Listing 2.

Finishing Up

Screen 3 shows the result when you browse the finished page. Selecting a different name in the listbox of parents will result in the key value associated with that name being supplied as the parameter to the query that populates the grid, thereby showing the children records for that parent. Parent/child relationships occur with such frequency that it's a welcome relief to be able to display them dynamically by linking one data-bound control to another through very few lines of code.