HOWTO: Create a Master/Detail Page (.asp) Using DTCs
ID: Q194018
|
The information in this article applies to:
-
Microsoft Visual InterDev, version 6.0
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
- Right-click the Global.asa and choose "Add Data Connection".
- 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.
- Highlight the DSN when found, and click OK.
- When the next dialog box opens, rename the connection from Connection1
to Pubs.
How to Write the ASP Page
- 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.
- Under the <BODY> tag in the page, drag the Recordset DTC from the
Design-Time Controls toolbox.
- 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.
- Drag a Listbox DTC from the Design-Time Controls toolbox to the page
under the Recordset.
- 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.
- 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.
- Drag the Recordset DTC from the Design-Time Controls toolbox to a spot
under the Listbox DTC.
- 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 = ?
- In the Implementation tab, clear the "Automatically open the Recordset"
property. Click CLOSE.
- Drag a Grid from the Design-Time Controls toolbox to the page under
Recordset2.
- 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
|