Using the Master Detail Page Set Server Behavior

The fastest and easiest way to create a master/detail page set in Dreamweaver MX is to use the Master Detail Page Set Server Behavior. Choosing a Dreamweaver Server Behavior from the Server Behavior panel inserts pre-existing code into your dynamic page. With a small amount of configuration accomplished through dialog boxes, this inserted code is ready to run. Further, once you configure and insert this code, Dreamweaver MX continues to see the inserted code as an object, allowing you to easily modify the behavior through the same configuration dialog boxes. That is, by choosing Insert ® Application Objects (or by clicking the plus sign on the Server Behaviors tab of the Application panel), you can automatically create both the master page and the detail page at one time from the same data connection and recordset. This eliminates the typographic errors that often accompany such repetitive tasks as scripting a page and connecting to the data connection and the recordset. Let's create a master/detail page set using ASP and then JSP.

Creating a Master/Detail Page Set Using ASP

To demonstrate how to create a a master/detail page set using ASP, let's again use our Books database from previous chapters as an example. We'll create a master page that initially displays each book category listed in the tblCategories table. Later, we'll drill down to see the contents of each category.

  1. In Dreamweaver MX, open a new page in your defined ASP site and save the file as Master.asp.

  2. Using the BookTrackingOLEDB or BookTrackingAccess database connection we created in Chapter 10, create and add a recordset of the items in the tblCategories table. To do so, choose Insert ® Application Objects ® Recordset (or click the plus sign in the Server Behaviors tab of the Application panel and select the Recordset option) to open the Recordset dialog box, as shown in Figure 21.2.

    click to expand
    Figure 21.2: The Recordset dialog box

  3. Name the recordset RS_Category and choose your data connection from the Connection drop-down list box. Notice that Dreamweaver MX populates the Table drop-down list box with the tables available in the Books database.

  4. Select the tblCategories table, and then select Category in the Sort list box.

  5. Click OK to create the recordset.

As you can see in Listing 21.1, Dreamweaver MX inserts the ASP include statement to reference the BookTrackingOLEDB data connection and the code to create the RS_Category recordset into the Master.asp script file.

Listing 21.1: THE RECORDSET

start example
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>  <!--#include file="Connections/SQLSERVER_OLEDB.asp" -->  <% Dim RS_Category Dim RS_Category_numRows     Set RS_Category = Server.CreateObject("ADODB.Recordset") RS_Category.ActiveConnection = MM_SQLSERVER_OLEDB_STRING  RS_Category.Source = "SELECT * FROM dbo.tblCategories ORDER BY Category ASC"  RS_Category.CursorType = 0 RS_Category.CursorLocation = 2 RS_Category.LockType = 1 RS_Category.Open()     RS_Category_numRows = 0  %> <html> <head>  <title>Master</title>  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">  </head> <body> </body>  </html>  <% RS_Category.Close() Set RS_Category = Nothing  %>
end example

At this point, you're ready to insert the master/detail page set. To do so, follow these steps:

  1. Choose Insert ® Application Objects ® Master Detail Page Set or click the Master Detail Page Set icon in the Application tab of the Insert bar to open the Insert Master-Detail Page Set dialog box, as shown in Figure 21.3.

    click to expand
    Figure 21.3: The Insert Master- Detail Page Set dialog box

  2. In the Recordset drop-down list box, select RS_category.

  3. Select the table column fields that you want to on the master page. For our example, we want Category to appear in a list.

  4. Choose the table column field that will link the master page to the detail page. Since we are displaying only one table column field in our example, our choice is simple—Category.

  5. Choose the record identifier that the master page will pass to the detail page through the link. In our example, we want to use CategoryID, which is the primary key of the tblCategories table, so choose CategoryID from the Pass Unique Key drop-down list box.

Finally, the Show option allows you to divide all returned records into sets or pages of records. The default is to display 10 records at a time. Choosing this option inserts page record navigation links to allow the user to move forward and backward through the recordset 10 records at a time.

  1. To keep our example relatively simple, in the Show section, click the All Records option.

At this point we've finished configuring the master page of our master/detail page set. Now let's configure our detail page. (Even though the result will be as many pages as there are categories, we only configure a single detail page.)

  1. Enter Detail.asp in the Detail Page Name text field.

  2. Much as we did for the master page, choose the table column fields you want the detail page to display for the record. Select both CategoryID and Category. Figure 21.3 shows our completed Insert Master-Detail Page Set dialog box.

  3. Click OK.

When you click OK, Dreamweaver MX creates the Master.asp and Detail.asp script pages and inserts the code shown in Listings 21.2 and 21.3. As you can see, the amount of code produced by Dreamweaver MX is considerable, and it is code you do not have to write. To understand a bit more of what Dreamweaver MX is inserting in your pages, take a look at the Server Behavior panel for Master.asp.

Notice that there is not a Master Detail Page Set Server Behavior listed. That's because the Master Detail Page Set Server Behavior is actually a collection of the following Dreamweaver MX Server Behaviors.

Recordset Creates the recordset of data

Repeat Region Loops through the recordset of data

Dynamic Text Outputs the data in the recordset

Go To Detail Page Creates the link to the Detail page

Show If Not First Record A Boolean test to output page set navigation if the first record of the recordset is not displayed in the page set of records in the current page

Move To First Record Creates a link to display the page set of records containing the first record in the recordset

Move To Previous Records Creates a link to move to the previous page set of records

Show If Not Last Record A Boolean test to output page set navigation if the last record of the recordset is not displayed in the page set of records in the current page

Move To Next Records Creates a link to move to the next page set of records

Move To Last Record Creates a link to display the page set of records containing the last record in the recordset

 On the CD-ROM   You'll find all the listing files in this chapter on the CD accompanying this book. Throughout this chapter, code has been formatted to fit the book page. The character _ appearing at a line ending marks a break introduced by the publisher to fit the page width; it is not part of the actual source code.

Listing 21.2: MASTER.ASP

start example
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>  <!--#include file="Connections/BookTrackingOLEDB.asp" -->  <% Dim RS_category Dim RS_category_numRows     Set RS_category = Server.CreateObject("ADODB.Recordset")  RS_category.ActiveConnection = MM_BookTrackingOLEDB_STRING  RS_category.Source = "SELECT * FROM dbo.tblCategories"  RS_category.CursorType = 0 RS_category.CursorLocation = 2 RS_category.LockType = 1 RS_category.Open()     RS_category_numRows = 0  %> <% Dim Repeat1__numRows Dim Repeat1__index Repeat1__numRows = 10     Repeat1__index = 0 RS_category_numRows = RS_category_numRows + Repeat1__numRows  %> <%  ' *** Recordset Stats, Move To Record, and Go To Record: declare stats variables     Dim RS_category_total Dim RS_category_first Dim RS_category_last     ' set the record count RS_category_total = RS_category.RecordCount     ' set the number of rows displayed on this page If (RS_category_numRows < 0) Then   RS_category_numRows = RS_category_total Elseif (RS_category_numRows = 0) Then   RS_category_numRows = 1 End If     ' set the first and last displayed record RS_category_first = 1 RS_category_last = RS_category_first + RS_category_numRows - 1     ' if we have the correct record count, check the other stats  If (RS_category_total <> -1) Then  If (RS_category_first > RS_category_total) Then    RS_category_first = RS_category_total  End If  If (RS_category_last > RS_category_total) Then    RS_category_last = RS_category_total  End If  If (RS_category_numRows > RS_category_total) Then    RS_category_numRows = RS_category_total  End If End If  %> <%  ' *** Recordset Stats: if we don't know the record count, manually count them     If (RS_category_total = -1) Then      ' count the total records by iterating through the recordset  RS_category_total=0  While (Not RS_category.EOF)    RS_category_total = RS_category_total + 1    RS_category.MoveNext  Wend      ' reset the cursor to the beginning  If (RS_category.CursorType > 0) Then    RS_category.MoveFirst  Else    RS_category.Requery  End If      ' set the number of rows displayed on this page  If (RS_category_numRows < 0 Or RS_category_numRows > RS_category_total) Then    RS_category_numRows = RS_category_total  End If      ' set the first and last displayed record  RS_category_first = 1  RS_category_last = RS_category_first + RS_category_numRows - 1  If (RS_category_first > RS_category_total) Then    RS_category_first = RS_category_total  End If  If (RS_category_last > RS_category_total) Then    RS_category_last = RS_category_total  End If     End If  %> <% Dim MM_paramName   %> <%  ' *** Move To Record and Go To Record: declare variables     Dim MM_rs Dim MM_rsCount Dim MM_size Dim MM_uniqueCol Dim MM_offset Dim MM_atTotal Dim MM_paramIsDefined     Dim MM_param Dim MM_index     Set MM_rs = RS_category MM_rsCount = RS_category_total MM_size = RS_category_numRows MM_uniqueCol = "" MM_paramName = "" MM_offset = 0 MM_atTotal = false MM_paramIsDefined = false If (MM_paramName <> "") Then   MM_paramIsDefined = (Request.QueryString(MM_paramName) <> "")  End If %> <%  ' *** Move To Record: handle 'index' or 'offset' parameter     if (Not MM_paramIsDefined And MM_rsCount <> 0) then      ' use index parameter if defined, otherwise use offset parameter  MM_param = Request.QueryString("index")  If (MM_param = "") Then    MM_param = Request.QueryString("offset")  End If  If (MM_param <> "") Then    MM_offset = Int(MM_param)  End If      ' if we have a record count, check if we are past the end of the recordset  If (MM_rsCount <> -1) Then    If (MM_offset >= MM_rsCount Or MM_offset = -1) Then ' past end or move last      If ((MM_rsCount Mod MM_size) > 0) Then ' last page not a full repeat region          MM_offset = MM_rsCount - (MM_rsCount Mod MM_size)      Else          MM_offset = MM_rsCount - MM_size      End If    End If  End If      ' move the cursor to the selected record  MM_index = 0  While ((Not MM_rs.EOF) And (MM_index < MM_offset Or MM_offset = -1))    MM_rs.MoveNext    MM_index = MM_index + 1  Wend  If (MM_rs.EOF) Then     MM_offset = MM_index ' set MM_offset to the last possible record  End If     End If  %> <%  ' *** Move To Record: if we don't know the record count, check the display range     If (MM_rsCount = -1) Then      ' walk to the end of the display range for this page    MM_index = MM_offset  While (Not MM_rs.EOF And (MM_size < 0 Or MM_index < MM_offset + MM_size))    MM_rs.MoveNext    MM_index = MM_index + 1  Wend      ' if we walked off the end of the recordset, set MM_rsCount and MM_size  If (MM_rs.EOF) Then    MM_rsCount = MM_index    If (MM_size < 0 Or MM_size > MM_rsCount) Then       MM_size = MM_rsCount    End If  End If if we walked off the end, set the offset based on page size  If (MM_rs.EOF And Not MM_paramIsDefined) Then    If (MM_offset > MM_rsCount - MM_size Or MM_offset = -1) Then      If ((MM_rsCount Mod MM_size) > 0) Then         MM_offset = MM_rsCount - (MM_rsCount Mod MM_size)      Else         MM_offset = MM_rsCount - MM_size      End If    End If  End If      ' reset the cursor to the beginning  If (MM_rs.CursorType > 0) Then    MM_rs.MoveFirst  Else    MM_rs.Requery  End If  ' move the cursor to the selected record  MM_index = 0  While (Not MM_rs.EOF And MM_index < MM_offset)    MM_rs.MoveNext    MM_index = MM_index + 1  Wend End If  %> <%  ' *** Move To Record: update recordset stats     ' set the first and last displayed record RS_category_first = MM_offset + 1 RS_category_last = MM_offset + MM_size If (MM_rsCount <> -1) Then  If (RS_category_first > MM_rsCount) Then    RS_category_first = MM_rsCount  End If  If (RS_category_last > MM_rsCount) Then    RS_category_last = MM_rsCount  End If End If ' set the boolean used by hide region to check if we are on the last record  MM_atTotal = (MM_rsCount <> -1 And MM_offset + MM_size >= MM_rsCount) %> <% ' *** Go To Record and Move To Record: create strings for  ' *** maintaining URL and Form parameters     Dim MM_keepNone Dim MM_keepURL Dim MM_keepForm Dim MM_keepBoth     Dim MM_removeList Dim MM_item Dim MM_nextItem     ' create the list of parameters which should not be maintained  MM_removeList = "&index=" If (MM_paramName <> "") Then   MM_removeList = MM_removeList & "&" & MM_paramName & "="  End If MM_keepURL="" MM_keepForm="" MM_keepBoth="" MM_keepNone=""     ' add the URL parameters to the MM_keepURL string For Each MM_item In Request.QueryString  MM_nextItem = "&" & MM_item & "="  If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then    MM_keepURL = MM_keepURL & MM_nextItem &_    Server.URLencode(Request.QueryString(MM_item))  End If Next     ' add the Form variables to the MM_keepForm string For Each MM_item In Request.Form  MM_nextItem = "&" & MM_item & "="  If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then    MM_keepForm = MM_keepForm & MM_nextItem &_    Server.URLencode(Request.Form(MM_item))  End If Next     ' create the Form + URL string and remove the intial '&' from each of the strings  MM_keepBoth = MM_keepURL & MM_keepForm If (MM_keepBoth <> "") Then    MM_keepBoth = Right(MM_keepBoth, Len(MM_keepBoth) - 1)  End If If (MM_keepURL <> "") Then   MM_keepURL = Right(MM_keepURL, Len(MM_keepURL) - 1) End If If (MM_keepForm <> "") Then   MM_keepForm = Right(MM_keepForm, Len(MM_keepForm) - 1)  End If     ' a utility function used for adding additional parameters to these strings  Function MM_joinChar(firstItem)  If (firstItem <> "") Then    MM_joinChar = "&"  Else    MM_joinChar = ""  End If End Function  %> <%  ' *** Move To Record: set the strings for the first, last, next, and previous links     Dim MM_keepMove Dim MM_moveParam Dim MM_moveFirst Dim MM_moveLast Dim MM_moveNext Dim MM_movePrev     Dim MM_urlStr Dim MM_paramList Dim MM_paramIndex Dim MM_nextParam     MM_keepMove = MM_keepBoth MM_moveParam = "index"     ' if the page has a repeated region, remove 'offset' from the maintained parameters  If (MM_size > 1) Then  MM_moveParam = "offset"  If (MM_keepMove <> "") Then    MM_paramList = Split(MM_keepMove, "&")    MM_keepMove = ""    For MM_paramIndex = 0 To UBound(MM_paramList)      MM_nextParam = Left(MM_paramList(MM_paramIndex),        InStr(MM_paramList(MM_paramIndex),"=") - 1)      If (StrComp(MM_nextParam,MM_moveParam,1) <> 0) Then        MM_keepMove = MM_keepMove & "&" & MM_paramList(MM_paramIndex)      End If    Next    If (MM_keepMove <> "") Then      MM_keepMove = Right(MM_keepMove, Len(MM_keepMove) - 1)    End If  End If End If     ' set the strings for the move to links If (MM_keepMove <> "") Then    MM_keepMove = MM_keepMove & "&" End If     MM_urlStr = Request.ServerVariables("URL") & "?" &_ MM_keepMove & MM_moveParam & "="     MM_moveFirst = MM_urlStr & "0" MM_moveLast = MM_urlStr & "-1" MM_moveNext = MM_urlStr & CStr(MM_offset + MM_size)  If (MM_offset - MM_size < 0) Then   MM_movePrev = MM_urlStr & "0" Else   MM_movePrev = MM_urlStr & CStr(MM_offset - MM_size)  End If %> <html> <head> <title>Master.asp</title>  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">  </head>     <body>  <table align="center" border="1">  <tr>    <td align="default" width="50%"> CategoryID </td>   <td align="default" width="50%"> Category </td>  </tr>  <%  While ((Repeat1__numRows <> 0) AND (NOT RS_category.EOF))   %>  <tr>    <td align="default" width="50%">     <a href="detail.asp?<%= MM_keepBoth & _ MM_joinChar(MM_keepBoth) & "CategoryCategoryID").Value %>">_  <%=(RS_category.Fields.Item("CategoryID").Value)%></a> </td>   <td align="default" width="50%">     <%=(RS_category.Fields.Item("Category").Value)%> </td>  </tr>  <%   Repeat1__index=Repeat1__index+1  Repeat1__numRows=Repeat1__numRows-1  RS_category.MoveNext() Wend  %>  </table>  <br>  <table border="0" width="50%" align="center">      <tr>    <td width="23%" align="center"> <% If MM_offset <> 0 Then %>    <a href="<%=MM_moveFirst%>">First</a>     <% End If ' end MM_offset <> 0 %> </td>  <td width="31%" align="center"> <% If MM_offset <> 0 Then %>    <a href="<%=MM_movePrev%>">Previous</a>     <% End If ' end MM_offset <> 0 %> </td>  <td width="23%" align="center"> <% If Not MM_atTotal Then %>    <a href="<%=MM_moveNext%>">Next</a>     <% End If ' end Not MM_atTotal %> </td>  <td width="23%" align="center"> <% If Not MM_atTotal Then %>    <a href="<%=MM_moveLast%>">Last</a>     <% End If ' end Not MM_atTotal %> </td>  </tr>  </table> Records <%=(RS_category_first)%> to <%=(RS_category_last)%> of    <%=(RS_category_total)%>   </body> </html> <% RS_category.Close() Set RS_category = Nothing  %> 
end example

Listing 21.3: DETAIL.ASP

start example
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>  <!--#include file="Connections/BookTrackingOLEDB.asp" --> <% Dim RS_category__MMColParam RS_category__MMColParam = "1" If (Request.QueryString("CategoryID") <> "") Then    RS_category__MMColParam = Request.QueryString("CategoryID")  End If %>     <% Dim RS_category Dim RS_category_numRows Set RS_category = Server.CreateObject("ADODB.Recordset")  RS_category.ActiveConnection = MM_BookTrackingOLEDB_STRING  RS_category.Source = "SELECT * FROM dbo.tblCategories WHERE CategoryID = " +      Replace(RS_category__MMColParam, "'", "''") + ""  RS_category.CursorType = 0 RS_category.CursorLocation = 2 RS_category.LockType = 3 RS_category.Open()     RS_category_numRows = 0  %>     <html>  <head>  <title>Detail.asp</title>  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">  </head>     <body>  <table align="center" border="1">  <tr>   <td align="default" width="50%">CategoryID</td>   <td align="default" width="50%"><%=(RS_category.Fields.Item("CategoryID")      .Value)%></td>  </tr>  <tr>    <td align="default" width="50%">Category</td>    <td align="default"       width="50%"><%=(RS_category.Fields.Item("Category").Value)%></td>  </tr>  </table>     </body>  </html> < % RS_category.Close() Set RS_category = Nothing  %>
end example

Figures 21.4 and 21.5 show the results of Master.asp and Detail.asp in a web browser. As you can see, Master.asp displays the list of categories. Each category has an HTML hyperlink to pass its primary key identifier to the Detail.asp page through a url variable. As the Detail.asp receives the primary key, it displays all information for the Category record with the passed primary key. Pretty slick. However, our example doesn't exactly do our drill-down principle justice. At this point, our detail page simply shows the user the item they click in the master page. A useful detail page shows much more relevant information, such as the books belonging to the clicked category.

click to expand
Figure 21.4: Viewing the results of Master.asp

click to expand
Figure 21.5: Viewing the results of Detail.asp

A More Realistic Master/Detail Page Set

Now that we have the basics down, let's create a more traditional drill-down example. To demonstrate, let's alter our Detail.asp script page to display all the books that belong to the passed category. Follow these steps:

  1. Open the Detail.asp script page and choose Window ® Server Behaviors to open the Server Behaviors panel.

    Detail.asp is using the following server behaviors:

    Recordset Creates a recordset of data based on the tblCategories primary key passed from the Master.asp script page

    Dynamic Text Displays the data in the recordset

    To alter the Detail.asp script page, we'll alter the data pulled by the recordset, add a Repeat Region to loop through the returned recordset, and alter the Dynamic Text to output the data.

  2. To alter the Recordset, double-click the Recordset Server Behavior shown in the Server Behaviors panel.

    To change the recordset to select all of the book titles currently belonging to the passed category, we'll need to modify the SQL statement to use joins. If you recall from Chapter 18, a join allows you to link two tables together based on one common field. We'll join the tblCategories table to the tblBooksCategories table and then to the tblBooks table.

  3. To modify the SQL, we'll use the Advanced view of the Recordset Server Behavior. In the resulting Recordset dialog box, click the Advanced button to display the SQL statement that defines the data collected into the recordset.

  4. Replace the existing SQL statement with the following:

    SELECT      tblCategories.CategoryID,              tblCategories.Category,              tblBooks.BookID,              tblBooks.Title,              tblBooks.PageCount,              tblBooks.Lending FROM        (tblBooks  INNER JOIN  tblBooksCategories  ON          tblBooks.BookID = tblBooksCategories.Book) INNER JOIN  tblCategories  ON          tblBooksCategories.Category = tblCategories.CategoryID  WHERE       tblCategories.CategoryID =  MMColParam 

  5. The Recordset dialog box should now look similar to Figure 21.6.Click OK to close the dialog box and save the changes to the recordset.

    click to expand
    Figure 21.6: The Recordset dialog box

The next step is to loop through the results of the new recordset and output the book ID and titles. First change the Dynamic Text of Detail.asp to output the book title instead of the category ID and category name. To do so, follow these steps:

  1. Double-click Dynamic Text(RS_category.CategoryID) in the Server Behaviors panel to open the Dynamic Text dialog box, which is shown in Figure 21.7.Dreamweaver displays the available table columns from our modified recordset.

    click to expand
    Figure 21.7: The BookID Dynamic Text dialog box

  2. To change the Dynamic Text to display the BookID instead of the CategoryID, choose BookID from the Field list box. Click OK.

  3. Double-click Dynamic Text(RS_category.Category) in the Server Behaviors panel to open the Dynamic Text dialog box and choose Title from the Field list box as shown in Figure 21.8. Click OK.

    click to expand
    Figure 21.8: The Title Dynamic Text dialog box

Once you complete the Dynamic Text modifications, update "CategoryID" and "Category" in the HTML of Detail.asp to "BookID" and "Book Title" to reflect the new BookID and Title Dynamic Text. Your code should appear similar to the code in Listing 21.4.

Listing 21.4: DISPLAY CODE OF DETAIL.ASP

start example
<table align="center" border="1">  <tr>   <td align="default" width="50%">BookID</td>   <td align="default" width="50%"><%=(RS_category.Fields.Item("BookID").Value)%></td>   </tr> <tr>    <td align="default" width="50%">Book Title</td>    <td align="default" width="50%"><%=(RS_category.Fields.Item("Title").Value)%></td>  </tr> </table>
end example

Now you're ready to add a Repeat Region to the Detail.asp page to loop through the recordset results. To do so, follow these steps:

  1. Select the table rows and cells of the table in the display code.

  2. Choose Insert ® Application Objects ® Repeated Region to open the Repeat Region dialog box, which is shown in Figure 21.9.


    Figure 21.9: The Repeat Region dialog box

  3. In the Recordset drop-down list box, select RS_category and choose to show all records in the recordset.

  4. Click OK to save the Repeat Region to Detail.asp.

Now our Detail.asp script page will display the books under the category chosen from the Master.asp script page. Listing 21.5 shows the updated code of the Detail.asp script page. Figure 21.10 shows the web browser result of Detail.asp.

Listing 21.5: DETAIL2.ASP

start example
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>  <!--#include file="Connections/BookTrackingOLEDB.asp" -->  <% Dim RS_category__MMColParam RS_category__MMColParam = "1" If (Request.QueryString("CategoryID") <> "") Then    RS_category__MMColParam = Request.QueryString ("CategoryID") End If %> <% Dim RS_category Dim RS_category_numRows     Set RS_category = Server.CreateObject("ADODB.Recordset")  RS_category.ActiveConnection = MM_BookTrackingOLEDB_STRING  RS_category.Source = "SELECT tblCategories.CategoryID, tblCategories.Category,     tblBooks.BookID, tblBooks.Title, tblBooks.PageCount, tblBooks.Lending FROM      (tblBooks INNER JOIN tblBooksCategories ON tblBooks.BookID = tblBooksCategories    .Book) INNER JOIN tblCategories ON tblBooksCategories.Category = tblCategories    .CategoryID WHERE tblCategories.CategoryID = " + Replace(RS_category__     MMColParam, "'", "''") + "" RS_category.CursorType = 0 RS_category.CursorLocation = 2 RS_category.LockType = 3 RS_category.Open() RS_category_numRows = 0  %> <% Dim Repeat1__numRows Dim Repeat1__index     Repeat1__numRows = -1 Repeat1__index = 0 RS_category_numRows = RS_category_numRows + Repeat1__numRows  %> <html> <head> <title>Detail.asp</title>  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">  </head>     <body>      <table align="center" border="1">  <%  While ((Repeat1__numRows <> 0) AND (NOT RS_category.EOF))   %>  <tr>    <td align="default" width="50%">BookID</td>   <td align="default" width="50%"><%=(RS_category.Fields.Item("CategoryID")     .Value)%></td>  </tr>  <tr>    <td align="default" width="50%">Book       Title</td>   <td align="default" width="50%"><%=(RS_category.Fields.Item("Title").Value)%></td>  </tr>  <%   Repeat1__index=Repeat1__index+1  Repeat1__numRows=Repeat1__numRows-1  RS_category.MoveNext() Wend %> </table>     </body>  </html>  <% RS_category.Close() Set RS_category = Nothing  %> 
end example

click to expand
Figure 21.10: Viewing the result of the Detail2.asp

Creating a Master/Detail Page Set Using JSP

Now that you've seen how the Master Detail Page Set Server Behavior functions using Microsoft ASP, you can easily implement the same behavior using JSP. To demonstrate, let's replicate our ASP example of the master category list page drilling down to the books belonging to each category. To review the process, you add a recordset to a JSP page and then insert a Master Detail Page Set Server Behavior. To do so, follow these steps:

  1. Activate the JSP website we've been using throughout the book.

  2. Open a new page and name it Master.jsp.

As before, use the data connection we created in Chapter 10 to create the RS_Category recordset.

  1. Choose Insert ® Application Objects ® Recordset (or click the plus sign in the Server Behaviors tab of the Application panel and select the Recordset option) to open the Recordset dialog box, which is shown in Figure 21.11.

    click to expand
    Figure 21.11: The JSP Recordset dialog box

  2. Enter RS_Category in the Name text field.

  3. Choose your data connection from the Connection drop-down list box.

  4. In the Table drop-down list box, select the tblCategories table, and choose Category in the Sort drop-down list box.

  5. Click OK to create the recordset.

As you can see in Listing 21.6, Dreamweaver MX inserts the JSP include statement to reference our data connection and the code to create the RS_Category recordset into the Master.jsp script file.

Listing 21.6: JSP RECORDSET CODE

start example
<%@ page contentType="text/html; charset=iso-8859-1" language="java"    import="java.sql.*" errorPage="" %>   <%@ include file="Connections/BookTrackingMsSQL.jsp" %>  <% Driver DriverRS_Category =     (Driver)Class.forName(MM_BookTrackingMsSQL_DRIVER).newInstance();  Connection ConnRS_Category=DriverManager.getConnection_  (MM_BookTrackingMsSQL_ STRING,_ MM_BookTrackingMsSQL_USERNAME,_ MM_BookTrackingMsSQL_PASSWORD); PreparedStatement StatementRS_Category = ConnRS_Category.prepareStatement("SELECT     tblCategories.CategoryID, tblCategories.Category, tblBooks.BookID,  tblBooks.Title,    tblBooks.PageCount, tblBooks.Lending FROM (tblBooks INNER JOIN     tblBooksCategories ON tblBooks.BookID = tblBooksCategories.Book) INNER JOIN     tblCategories ON tblBooksCategories.Category = tblCategories.CategoryID ORDER BY   tblCategories.Category "); ResultSet RS_Category = StatementRS_Category.executeQuery();  boolean RS_Category_isEmpty = !RS_Category.next();  boolean RS_Category_hasData = !RS_Category_isEmpty; Object RS_Category_data;  int RS_Category_numRows = 0;  %> <html> <head> <title>master.asp</title>  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">  </head>     <body>  </body>  </html>  <% RS_Category.close(); StatementRS_Category.close(); ConnRS_Category.close(); %>   
end example

Now we're all set to insert the Master Detail Page Set Server Behavior. To do so, follow these steps:

  1. Choose Insert ® Application Objects ® Master Detail Page Set to open the Insert Master- Detail Page Set dialog box, which is shown in Figure 21.12.

    click to expand
    Figure 21.12: The JSP Insert Master-Detail Page Set dialog box

  2. In the Recordset drop-down list box, choose RS_Category. As before, Dreamweaver MX lists the available recordset columns in the Master Page and Detail Page Fields box.

  3. Choose the Category table column field as the link between the master page and the detail page.

  4. Choose CategoryID from the Pass Unique Key drop-down list box to set it as the record identifier that the master page passes to the detail page through the link.

  5. In the Show section, click the All Records option.

  6. To configure the detail page, enter Detail.jsp in the Detail Page Name text field.

  7. Select both Category and CategoryID from the Detail Page Fields list box.

    Remember, we are going to pass the CategoryID value of each record in the master page to a detail page. That detail page will display the book titles belonging to that passed category. Obviously, the Category column field is incorrect, but we'll fix that in a moment.

  8. Click OK to close the Insert Master-Detail Page Set dialog box.

Once Dreamweaver MX has added code to Master.jsp and Detail.jsp, we can modify Detail.jsp to display the book titles of the passed category. To do so, follow these steps:

  1. Activate Detail.jsp and double-click the RS_Category recordset in the Server Behaviors panel to open the Recordset dialog box.

  2. Click Advanced to switch to the Advanced view of the Recordset dialog box.

  3. Insert our joined table SQL statement into the SQL text box as shown in Figure 21.13.

    click to expand
    Figure 21.13: The JSP Recordset dialog box

    Notice that we added an ORDER BY statement to sort book titles alphabetically.

     SELECT      tblCategories.CategoryID,             tblCategories.Category,             tblBooks.BookID,             tblBooks.Title,             tblBooks.PageCount,             tblBooks.Lending FROM        (tblBooks INNER JOIN  tblBooksCategories ON          tblBooks.BookID = tblBooksCategories.Book) INNER JOIN  tblCategories ON          tblBooksCategories.Category = tblCategories.CategoryID WHERE       tblCategories.CategoryID= MMColParam ORDER BY    tblBooks.Title

Now we change the dynamic text to display book titles instead of categories. To do so, follow these steps:

  1. Double-click the Dynamic Text elements in the Server Behaviors panel to open the Dynamic Text dialog box.

  2. Change the column fields to BookID and Title.

  3. To loop through the recordset, add a Repeat Region around the rows and cells of the table in the HTML display code. To do so, select the rows and cells and choose Insert ® Application Objects ® Repeated Region to open the Repeat Region dialog box.

  4. Select the RS_Category recordset and click OK.

That's it. We've completed the master/detail page set that displays a list of categories with links to view the books that belong to each category. Listings 21.7 and 21.8 show the final code for the Master.jsp master page and the Detail.jsp detail page. Figures 21.14 and 21.15 show the web browser results of Master.jsp and Detail.jsp.

Listing 21.7: MASTER.JSP

start example
<%@ page contentType="text/html; charset=iso-8859-1" language="java"    import="java.sql.*" errorPage="" %>   <%@ include file="Connections/booktrackingAccess.jsp" %>  <%     Driver DriverRS_Category = (Driver)Class.forName(MM_booktrackingAccess_DRIVER)_ .newInstance(); Connection ConnRS_Category = DriverManager.getConnection_  (MM_booktrackingAccess_STRING,MM_booktrackingAccess_USERNAME,_ MM_booktrackingAccess_PASSWORD); PreparedStatement StatementRS_Category = ConnRS_Category.prepareStatement_  ("SELECT tblCategories.CategoryID, tblCategories.Category FROM tblCategories_  ORDER BY tblCategories.Category"); ResultSet RS_Category = StatementRS_Category.executeQuery(); boolean RS_Category_isEmpty = !RS_Category.next();  boolean RS_Category_hasData = !RS_Category_isEmpty;  Object RS_Category_data;  int RS_Category_numRows = 0; %> <% int Repeat1__numRows = 10;  int Repeat1__index = 0; RS_Category_numRows += Repeat1__numRows; %> <%  // *** Recordset Stats, Move To Record, and Go To Record: declare stats     variables     int RS_Category_first = 1;  int RS_Category_last = 1;  int RS_Category_total = -1;     if (RS_Category_isEmpty) {  RS_Category_total = RS_Category_first = RS_Category_last = 0;  }     //set the number of rows displayed on this page  if (RS_Category_numRows == 0) {    RS_Category_numRows = 1;  } %> <%  // *** Recordset Stats: if we don't know the record count, manually count them     if (RS_Category_total == -1) {      // count the total records by iterating through the recordset  for (RS_Category_total = 1; RS_Category.next(); RS_Category_total++);      // reset the cursor to the beginning  RS_Category.close();  RS_Category = StatementRS_Category.executeQuery();  RS_Category_hasData = RS_Category.next();      // set the number of rows displayed on this page  if (RS_Category_numRows < 0 || RS_Category_numRows > RS_Category_total) {    RS_Category_numRows = RS_Category_total;  }      // set the first and last displayed record  RS_Category_first = Math.min(RS_Category_first, RS_Category_total);  RS_Category_last = Math.min(RS_Category_first + RS_Category_numRows - 1,    RS_Category_total);  } %>  <% String MM_paramName = ""; %>  <%  // *** Move To Record and Go To Record: declare variables     ResultSet MM_rs = RS_Category; int            MM_rsCount = RS_Category_total;  int       MM_size = RS_Category_numRows;  String    MM_uniqueCol = "";           MM_paramName = ""; int       MM_offset = 0;  boolean   MM_atTotal = false;  boolean   MM_paramIsDefined = (MM_paramName.length() != 0 &&      request.getParameter(MM_paramName) != null); %> <%  // *** Move To Record: handle 'index' or 'offset' parameter     if (!MM_paramIsDefined && MM_rsCount != 0) {      //use index parameter if defined, otherwise use offset parameter  String r = request.getParameter("index");  if (r==null) r = request.getParameter("offset");  if (r!=null) MM_offset = Integer.parseInt(r);      // if we have a record count, check if we are past the end of the recordset  if (MM_rsCount != -1) {    if (MM_offset >= MM_rsCount || MM_offset == -1) { // past end or move last       if (MM_rsCount % MM_size != 0)  // last page not a full repeat region          MM_offset = MM_rsCount - MM_rsCount % MM_size;       else          MM_offset = MM_rsCount - MM_size;    }  }      //move the cursor to the selected record  int i;  for (i=0; RS_Category_hasData && (i < MM_offset || MM_offset == -1); i++) {    RS_Category_hasData = MM_rs.next();  }  if (!RS_Category_hasData) MM_offset = i; // set MM_offset to the last    possible record  } %> <%  // *** Move To Record: if we dont know the record count, check the display range     if (MM_rsCount == -1) {      // walk to the end of the display range for this page  int i;  for (i=MM_offset; RS_Category_hasData && (MM_size < 0 || i < MM_offset +    MM_size); i++) {   RS_Category_hasData = MM_rs.next();  }      // if we walked off the end of the recordset, set MM_rsCount and MM_size  if (!RS_Category_hasData) {    MM_rsCount = i;    if (MM_size < 0 || MM_size > MM_rsCount) MM_size = MM_rsCount;  }      // if we walked off the end, set the offset based on page size  if (!RS_Category_hasData && !MM_paramIsDefined) {    if (MM_offset > MM_rsCount - MM_size || MM_offset == -1) { //check if past      end or last     if (MM_rsCount % MM_size != 0) //last page has less records than MM_size         MM_offset = MM_rsCount - MM_rsCount % MM_size;     else         MM_offset = MM_rsCount - MM_size;     }  }      // reset the cursor to the beginning  RS_Category.close();  RS_Category = StatementRS_Category.executeQuery();  RS_Category_hasData = RS_Category.next();  MM_rs = RS_Category;      // move the cursor to the selected record  for (i=0; RS_Category_hasData && i < MM_offset; i++) {    RS_Category_hasData = MM_rs.next();  }  }  %>  <%  // *** Move To Record: update recordset stats     // set the first and last displayed record  RS_Category_first = MM_offset + 1;  RS_Category_last = MM_offset + MM_size; if (MM_rsCount != -1) {   RS_Category_first = Math.min(RS_Category_first, MM_rsCount);   RS_Category_last = Math.min(RS_Category_last, MM_rsCount); }     // set the boolean used by hide region to check if we are on the last record  MM_atTotal = (MM_rsCount != -1 && MM_offset + MM_size >= MM_rsCount); %> <%  // *** Go To Record and Move To Record: create strings for maintaining URL and Form   parameters     String MM_keepBoth,MM_keepURL="",MM_keepForm="",MM_keepNone="";  String[] MM_removeList = { "index", MM_paramName };     // create the MM_keepURL string  if (request.getQueryString() != null) {     MM_keepURL = '&' + request.getQueryString();   for (int i=0; i < MM_removeList.length && MM_removeList[i].length() != 0; i++) {   int start = MM_keepURL.indexOf(MM_removeList[i]) - 1;   if (start >= 0 && MM_keepURL.charAt(start) == '&' &&       MM_keepURL.charAt(start + MM_removeList[i].length() + 1) == '=') {    int stop = MM_keepURL.indexOf('&', start + 1);    if (stop == -1) stop = MM_keepURL.length();    MM_keepURL = MM_keepURL.substring(0,start) + MM_keepURL.substring(stop);    }  }  }     // add the Form variables to the MM_keepForm string  if (request.getParameterNames().hasMoreElements()) {   java.util.Enumeration items = request.getParameterNames();   while (items.hasMoreElements()) {     String nextItem = (String)items.nextElement();     boolean found = false;     for (int i=0; !found && i < MM_removeList.length; i++) {       if (MM_removeList[i].equals(nextItem)) found = true;     }     if (!found && MM_keepURL.indexOf('&' + nextItem + '=') == -1) {       MM_keepForm = MM_keepForm + '&' + nextItem + '=' +          java.net.URLEncoder.encode(request.getParameter(nextItem));     }  }  }     // create the Form + URL string and remove the intial '&' from each of the strings  MM_keepBoth = MM_keepURL + MM_keepForm; if (MM_keepBoth.length() > 0) MM_keepBoth = MM_keepBoth.substring(1); if (MM_keepURL.length() > 0) MM_keepURL = MM_keepURL.substring(1);  if (MM_keepForm.length() > 0) MM_keepForm = MM_keepForm.substring(1);  %> <%  // *** Move To Record: set the strings for the first, last,   //     next, and previous links     String MM_moveFirst,MM_moveLast,MM_moveNext,MM_movePrev;  {  String MM_keepMove = MM_keepBoth; // keep both Form and URL parameters for    moves  String MM_moveParam = "index=";      // if the page has a repeated region, remove 'offset' from the maintained      parameters  if (MM_size > 1) {    MM_moveParam = "offset=";    int start = MM_keepMove.indexOf(MM_moveParam);    if (start != -1 && (start == 0 || MM_keepMove.charAt(start-1) == '&')) {      int stop = MM_keepMove.indexOf('&', start);      if (start == 0 && stop != -1) stop++;      if (stop == -1) stop = MM_keepMove.length();      if (start > 0) start--;      MM_keepMove = MM_keepMove.substring(0,start) + MM_keepMove.substring(stop);    }  }      // set the strings for the move to links  StringBuffer urlStr = new StringBuffer(request.getRequestURI()).append('?')     .append(MM_keepMove);  if (MM_keepMove.length() > 0) urlStr.append('&');  urlStr.append(MM_moveParam);  MM_moveFirst = urlStr + "0";  MM_moveLast = urlStr + "-1";  MM_moveNext = urlStr + Integer.toString(MM_offset+MM_size);  MM_movePrev = urlStr + Integer.toString(Math.max(MM_offset-MM_size,0));  } %> <html> <head> <title>master.jsp</title>  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">  </head>     <body>  <table align="center" border="1">     <tr>     <td align="default" width="100%"> Category </td>  </tr>  <% while ((RS_Category_hasData)&&(Repeat1__numRows-- != 0)) { %>  <tr>    <td align="default" width="100%">     <a href="detail.jsp?<%= MM_keepBoth + ((MM_keepBoth!="")?"&":"") +     "CategoryCategoryID"))==null ||    RS_Category.wasNull())_  ?"":RS_Category_data) %>"> <%=(((RS_Category_data = RS_Category.getObject_  ("Category"))==null || RS_Category.wasNull())?"":RS_Category_data)%></a> </td>  </tr>  <%  Repeat1__index++;  RS_Category_hasData = RS_Category.next();  } %> </table> <br>  <table border="0" width="50%" align="center">      <tr>   <td width="23%" align="center"> <% if (MM_offset !=0) { %>    <a href="<%=MM_moveFirst%>">First</a>     <% } /* end MM_offset != 0 */ %> </td>  <td width="31%" align="center"> <% if (MM_offset !=0) { %>    <a href="<%=MM_movePrev%>">Previous</a>     <% } /* end MM_offset != 0 */ %> </td>  <td width="23%" align="center"> <% if (!MM_atTotal) { %>    <a href="<%=MM_moveNext%>">Next</a>     <% } /* end !MM_atTotal */ %> </td>  <td width="23%" align="center"> <% if (!MM_atTotal) { %>    <a href="<%=MM_moveLast%>">Last</a>     <% } /* end !MM_atTotal */ %> </td>  </tr>  </table> Records <%=(RS_Category_first)%> to <%=(RS_Category_last)%> of <%=(RS_Category_    total)%>  </body> </html> <% RS_Category.close(); StatementRS_Category.close();  ConnRS_Category.close(); %> 
end example

Listing 21.8: DETAIL.JSP

start example
<%@ page contentType="text/html; charset=iso-8859-1" language="java"     import="java.sql.*" errorPage="" %>  <%@ include file="Connections/booktrackingAccess.jsp" %>     <% String RS_Category__MMColParam = "1";  if (request.getParameter("CategoryID") !=null) {RS_Category__MMColParam =     (String)request.getParameter("CategoryID");}  %>     <% Driver DriverRS_Category =     (Driver)Class.forName(MM_booktrackingAccess_DRIVER).newInstance(); Connection ConnRS_Category = DriverManager.getConnection_  (MM_booktrackingAccess_STRING,MM_booktrackingAccess_USERNAME,_    MM_booktrackingAccess_PASSWORD); PreparedStatement StatementRS_Category = ConnRS_Category.prepareStatement_    ("SELECT tblCategories.CategoryID,_    tblCategories.Category, tblBooks.BookID, tblBooks.Title, _  tblBooks.PageCount, tblBooks.Lending FROM (tblBooks _  INNER JOIN tblBooksCategories ON tblBooks.BookID = _    tblBooksCategories.Book) INNER JOIN tblCategories _  ON tblBooksCategories.Category = tblCategories.CategoryID _  WHERE tblCategories.CategoryID= " + RS_Category__MMColParam + " _  ORDER BY tblBooks.Title");  ResultSet RS_Category = StatementRS_Category.executeQuery();  boolean RS_Category_isEmpty = !RS_Category.next();  boolean RS_Category_hasData = !RS_Category_isEmpty;  Object RS_Category_data; int RS_Category_numRows = 0; %> <% int Repeat1__numRows = -1; int Repeat1__index = 0; RS_Category_numRows += Repeat1__numRows; %> <html> <head> <title>detail.jsp</title>  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">  </head>     <body>  <table align="center" border="1">  <% while ((RS_Category_hasData)&&(Repeat1__numRows-- != 0)) { %>  <tr>     <td align="default" width="50%">BookID</td>    <td align="default" width="50%">_  <%=(((RS_Category_data = RS_Category.getObject("BookID"))==null ||_   RS_Category.wasNull())?"":RS_Category_data)%></td>  </tr>  <tr>      <td align="default" width="50%">Book Title</td>     <td align="default" width="50%">_  <%=(((RS_Category_data = RS_Category.getObject("Title"))==null || _  RS_Category.wasNull())?"":RS_Category_data)%></td>   </tr>      <%  Repeat1__index++;  RS_Category_hasData = RS_Category.next(); } %> </table>     </body>  </html>  <% RS_Category.close(); StatementRS_Category.close();  ConnRS_Category.close(); %>
end example

click to expand
Figure 21.14: The browser result for Master.jsp

click to expand
Figure 21.15: The browser result for Detail.jsp

Creating a Master/Detail Page Set Using Macromedia ColdFusion MX

Creating our Category to Books master/detail example in ColdFusion MX is similar to the creation process in ASP. You use a data connection to contact your database and create a recordset. You then attach the recordset to a Dreamweaver MX Master Detail Page Set Server Behavior. Dreamweaver MX creates the master and detail page code. You modify the detail page to return a list of book titles belonging to the URL-passed CategoryID. Simple as that. Let's run through the example at a more deliberate speed. Follow these steps:

  1. Activate your ColdFusion MX site and open a new page.

  2. Save the page as Master.cfm.

  3. Using the data connection you created in Chapter 10, create a recordset of the data in the tblCategories table in our Books database. To do so, choose Insert ® Application Objects ® Recordset to open the Recordset dialog box.

  4. Name the recordset RS_Categories and choose your data connection from the Data Source list box. Notice that Dreamweaver MX populates the Table drop-down list box with the available tables from our Books database.

  5. Choose tblCategories from the Table drop-down list box. Your Recordset dialog box should now appear similar to the one shown in Figure 21.16.

    click to expand
    Figure 21.16: The Recordset dialog box

Now we're ready for the Master Detail Page Set Server Behavior. To create the Master Detail Page Set Server Behavior, follow these steps:

  1. Choose Insert ® Application Objects ® Master Detail Page Set (or click the plus sign in the Server Behaviors tab of the Application panel and select the Recordset option) to open the Recordset dialog box.

  2. In the Recordset drop-down list box, select RS_Categories. Dreamweaver MX populates the dialog box with the available data columns.

  3. Select the table column fields that you want to appear on the master page. For our example, we want only Category to appear in a list, so remove CategoryID from the Master Page Fields list box.

  4. To set the Category table column field as the field that links the master page to the detail page, choose Category from the Link To Detail From list box.

  5. Choose the record identifier that the master page will pass to the detail page through the link. In our example, we use the primary key of the tblCategories table—CategoryID. Choose CategoryID from the Pass Unique Key drop-down list box.

  6. In the Show section, click the All Records option.

  7. To configure the settings for the detail page, enter Detail.cfm in the Detail Page Name text field.

  8. Much as we did for the master page, choose the table column fields to display in the page. Our complete Insert Master-Detail Page Set configuration is in the dialog box shown in Figure 21.17.

    click to expand
    Figure 21.17: The Master Detail Page Set dialog box

  9. Click OK to close the Master Detail Page Set dialog box and create the code for Master.cfm and Detail.cfm.

You can see the Master.cfm code in Listing 21.9. Figure 21.18 shows the browser result of Master.cfm.

Listing 21.9: MASTER.CFM

start example
<cfquery name="RS_Categories" datasource="booktrackingAccess">  SELECT * FROM tblCategories  </cfquery> <html> <head> <title>master.cfm</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">  </head> <body>  <table border="1" align="center">   <tr>     <td>Category</td>  </tr>    <cfoutput query="RS_Categories">   <tr>     <td><a href="detail.cfm?recordID=#RS_Categories.CategoryID#">    #RS_Categories.Category# </a>    </td>  </tr>  </cfoutput>  </table> <br>  <cfoutput>#RS_Categories.RecordCount# Records Total</cfoutput>   </body> </html>
end example

click to expand
Figure 21.18: The browser result of Master.cfm

Now let's reconfigure Detail.cfm to display the book titles belonging to the category passed by the master page. To do so, we'll modify the SQL statement in the recordset to join several tables.

Follow these steps:

  1. Activate Detail.cfm.

  2. Choose Windows ® Server Behaviors to display the Server Behaviors panel.

  3. Double-click the recordset in the Server Behavior panel to open the Recordset dialog box.

  4. To alter the SQL statement, click the Advanced button to switch the Recordset dialog box to Advanced view. Notice that Dreamweaver MX is using the URL-passed CategoryID parameter to filter the data selected by the SQL statement.

  5. Even though we are altering the SQL statement, we want to keep that filter. To do so, replace the SQL statement with the following code.

    SELECT      tblCategories.CategoryID,              tblCategories.Category,              tblBooks.BookID,              tblBooks.Title,              tblBooks.PageCount,              tblBooks.Lending FROM        (tblBooks  INNER JOIN  tblBooksCategories  ON          tblBooks.BookID = tblBooksCategories.Book)  INNER JOIN  tblCategories  ON          tblBooksCategories.Category = tblCategories.CategoryID  WHERE       tblCategories.CategoryID=#URL.recordID#

    The Recordset dialog box should now appear similar to Figure 21.19.

    click to expand
    Figure 21.19: The Detail.cfm Recordset dialog box

  6. Click OK to close the dialog box.

The final step is to change the dynamic text that displays the contents of our recordset. To do so, double-click the Dynamic Text Server Behaviors in the Server Behavior panel and change them to display BookID and Title. You also want to change the static text label that identifies the dynamic text. Then change the CFOUTPUT tag to loop through the recordset. Normally you would insert a Repeat Region Server Behavior. However, ColdFusion MX is so easy to work with that you can simply change the opening <CFOUTPUT> tag to <CFOUTPUT QUERY="Recordset1">. You can see the final Detail.cfm in Listing 21.10 and the browser result in Figure 21.20.

Listing 21.10: DETAIL.CFM

start example
<cfparam name="URL.recordID" default="1">  <cfquery name="Recordset1" datasource="booktrackingAccess"> SELECT tblCategories.CategoryID, tblCategories.Category, tblBooks.BookID,    tblBooks.Title,   tblBooks.PageCount, tblBooks.Lending FROM (tblBooks INNER JOIN     tblBooksCategories  ON tblBooks.BookID = tblBooksCategories.Book) INNER JOIN tblCategories ON     tblBooksCategories.Category   = tblCategories.CategoryID WHERE tblCategories.CategoryID= #URL.recordID#  </cfquery> <html> <head> <title>detail.cfm</title>  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">  </head>     <body>  <table border="1" align="center">   <CFOUTPUT QUERY="Recordset1">   <tr>    <td>BookID</td>    <td>#Recordset1.BookID#</td>   </tr>   <tr>    <td>Title</td>     <td>#Recordset1.Title#</td>   </tr>   </cfoutput>  </table>             </body>  </html>
end example

click to expand
Figure 21.20: The browser result of Detail.cfm



Mastering Dreamweaver MX Databases
Mastering Dreamweaver MX Databases
ISBN: 078214148X
EAN: 2147483647
Year: 2002
Pages: 214

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net