Linking Objects with the Grid DTC

Parent/child relationships made simple

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.

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.