HOWTO: Create a Master/Detail Page (.asp) Using DTCs

ID: Q194018

The information in this article applies to:

SUMMARY

Individuals commonly want to display a list of items/records associated with a particular item/record selected by the user. The sample in this article uses a table with a one-to-many relationship with a second table. A list box displays one field from the "one-sided" table, and shows all the records associated with that item. This may be referred to as a Master/Detail relationship, or a one-to-many form.

Specifically, this sample puts a list of Authors in a drop-down list box. When you choose an author, the books written by that author are populated into a bound grid Design-time control (DTC). It uses the Authors table from Pubs to populate the list box and the TitleAuthor table to populate the books by that author into the grid.


MORE INFORMATION

To do this, the sample requires four DTCs. The drop-down list box to contain the authors, the Recordset DTC connected to the authors table for populating the drop-down, a recordset that looks up that author's name in the Titleauthor table when an author is chosen, which then puts the results in the Grid DTC.

NOTE: This article uses the SQL Server database of Pubs. It assumes that you have used the ODBC icon in Control Panel to create a valid DSN to Pubs. It also assumes that you have created a project, which is open in Visual InterDev.

How to Add a Data Connection

  1. Right-click the Global.asa and choose "Add Data Connection".


  2. Find the name of the DSN created in Control Panel. Depending on the type of DSN, it may be under the File tab or Machine tab.


  3. Highlight the DSN when found, and click OK.


  4. When the next dialog box opens, rename the connection from Connection1 to Pubs.


How to Write the ASP Page

  1. Right-click the project name in Project Explorer and choose "Add >" and then select "Active Server Page". Click Open. A new .asp page opens in the editor. This article was done while in the Source mode tab.


  2. Under the <BODY> tag in the page, drag the Recordset DTC from the Design-Time Controls toolbox.


  3. Right-click the recordset DTC, and choose the Properties option. Set the Connection to the name you gave your data connection when you added it to your Global.asa, the one that points to Pubs. In the "Source of Data" option group, click the "Database Object" option button. In the drop- down list, select "Tables". In the "Object Name" drop-down, select the "authors" table.


  4. Drag a Listbox DTC from the Design-Time Controls toolbox to the page under the Recordset.


  5. Right-click the Listbox DTC, and choose the Properties option. In the Data option group, drop-down the Recordset: property and choose the Recordset on the page. In the Field: drop-down property, choose "au_id". In the Lookup tab, select the Recordset option button, set the Row Source: property and select the recordset, in the Bound Column property. Drop-down and select the au_id field, and in the List Field property, drop-down and select the au_lname field. Click OK.


  6. At this point, you have a drop-down list box that is bound to the Recordset DTC and is populated with all the authors. Now you need to finish the sample by adding a recordset to search for the chosen author's books, and put the results in the Grid DTC.


  7. Drag the Recordset DTC from the Design-Time Controls toolbox to a spot under the Listbox DTC.


  8. Right-click the recordset DTC, and choose the Properties option. Set the Connection to the name you gave your data connection when you added it to your Global.asa, the one that points to Pubs. In the "Source of Data" option group area, click the "SQL Statement" option button. In text area, type the following SQL code: 
    
      SELECT * FROM TITLES T, TITLEAUTHOR TA WHERE TA.TITLE_ID = T.TITLE_ID
      AND TA.AU_ID = ?


  9. In the Implementation tab, clear the "Automatically open the Recordset" property. Click CLOSE.


  10. Drag a Grid from the Design-Time Controls toolbox to the page under Recordset2.


  11. Right-click the Grid DTC, and choose the Properties option. In the Data tab, click the "Recordset:" property drop-down and select the recordset that contains the SQL statement (Recordset2). In the "Available Fields" property, check some fields to place in the grid (select three or four fields). Click OK.


Now it is time to write the code that takes the author chosen, passes that value to the other Recordset, and have the other recordset plug it in as a parameter. We will write it as a function up in the <HEAD></HEAD> area. So just before the </HEAD> tag, write the following code (this sample is in JavaScript): 

   <SCRIPT ID=serverEventHandlersJS LANGUAGE=javascript runat=server>
   function setRS2() {
   if (Recordset2.isOpen())  {


       Recordset2.close();

   }

   lstAuthor = Listbox1.getValue();
   Recordset2.setParameter (0, lstAuthor);
   Recordset2.open();
   }
   </SCRIPT>

Explanation of Code

The first "if" statement is checking to see if the recordset is already open. If so, it closes it. The "if" statement is unnecessary, because you simply code "Recordset2.close", the Close method of the recordset DTC checks to see if the recordset is already closed and does not issue a close if it is, to prevent a failure by closing an already closed object. So, feel free to code a Recordset<x>.close() statement when you need to make sure the recordset is closed, even if you are not sure if the object is open. It will not fail. However, it was coded here for clarity. "lstAuthor" holds the name of the author chosen from the list box. It is then used as a parameter to Recordset2. Then the Recordset2 is opened, and it runs the SELECT * FROM TITLES T, TITLEAUTHOR TA WHERE TA.TITLE_ID = T.TITLE_ID AND TA.AU_ID = ? with the appropriate author inserted where the "?" was. The bound grid then shows those records.

Now you need to call this setRS2 function at the appropriate time, which is when an author is chosen. So, add this code into the current <SCRIPT></SCRIPT> area in the HEAD (this sample is JavaScript syntax): 

   function Listbox1_onchange() {
   setRS2();
   }
Save the page and "View in Browser". You will notice that once the list box is touched, then the books for the author chosen appear in the grid. However, when the page is first starts, the grid is not showing, because only the list box_onchange event triggers the Recordset2 to run and fills the grid. So let's add one more small piece of code, which populates the grid when the page first shows. So, add this code into the current <SCRIPT></SCRIPT area in the HEAD(this sample is JavaScript syntax): 

   function Recordset1_ondatasetcomplete() {
   setRS2();
   }
When the page first opens and Recordset1 brings back the data to populate the list box, it also runs the "setRS2" function to populate the grid.

Additional query words: kbDSIASPGrp kbVisID600 kbSample kbCtrl 


Keywords          : kbsample kbVisID600 kbGrpASP 
Version           : WINDOWS:6.0
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: May 5, 1999