|
|
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.
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.
In Dreamweaver MX, open a new page in your defined ASP site and save the file as Master.asp.
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.
Figure 21.2: The Recordset dialog box
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.
Select the tblCategories table, and then select Category in the Sort list box.
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
<%@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 %>
At this point, you're ready to insert the master/detail page set. To do so, follow these steps:
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.
Figure 21.3: The Insert Master- Detail Page Set dialog box
In the Recordset drop-down list box, select RS_category.
Select the table column fields that you want to on the master page. For our example, we want Category to appear in a list.
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.
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.
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.)
Enter Detail.asp in the Detail Page Name text field.
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.
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
<%@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 %>
Listing 21.3: DETAIL.ASP
<%@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 %>
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.
Figure 21.4: Viewing the results of Master.asp
Figure 21.5: Viewing the results of Detail.asp
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:
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.
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.
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.
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
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.
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:
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.
Figure 21.7: The BookID Dynamic Text dialog box
To change the Dynamic Text to display the BookID instead of the CategoryID, choose BookID from the Field list box. Click OK.
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.
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
<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>
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:
Select the table rows and cells of the table in the display code.
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
In the Recordset drop-down list box, select RS_category and choose to show all records in the recordset.
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
<%@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 %>
Figure 21.10: Viewing the result of the Detail2.asp
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:
Activate the JSP website we've been using throughout the book.
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.
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.
Figure 21.11: The JSP Recordset dialog box
Enter RS_Category in the Name text field.
Choose your data connection from the Connection drop-down list box.
In the Table drop-down list box, select the tblCategories table, and choose Category in the Sort drop-down list box.
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
<%@ 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(); %>
Now we're all set to insert the Master Detail Page Set Server Behavior. To do so, follow these steps:
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.
Figure 21.12: The JSP Insert Master-Detail Page Set dialog box
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.
Choose the Category table column field as the link between the master page and the detail page.
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.
In the Show section, click the All Records option.
To configure the detail page, enter Detail.jsp in the Detail Page Name text field.
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.
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:
Activate Detail.jsp and double-click the RS_Category recordset in the Server Behaviors panel to open the Recordset dialog box.
Click Advanced to switch to the Advanced view of the Recordset dialog box.
Insert our joined table SQL statement into the SQL text box as shown in Figure 21.13.
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:
Double-click the Dynamic Text elements in the Server Behaviors panel to open the Dynamic Text dialog box.
Change the column fields to BookID and Title.
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.
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
<%@ 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(); %>
Listing 21.8: DETAIL.JSP
<%@ 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(); %>
Figure 21.14: The browser result for Master.jsp
Figure 21.15: The browser result for Detail.jsp
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:
Activate your ColdFusion MX site and open a new page.
Save the page as Master.cfm.
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.
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.
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.
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:
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.
In the Recordset drop-down list box, select RS_Categories. Dreamweaver MX populates the dialog box with the available data columns.
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.
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.
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.
In the Show section, click the All Records option.
To configure the settings for the detail page, enter Detail.cfm in the Detail Page Name text field.
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.
Figure 21.17: The Master Detail Page Set dialog box
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
<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>
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:
Activate Detail.cfm.
Choose Windows ® Server Behaviors to display the Server Behaviors panel.
Double-click the recordset in the Server Behavior panel to open the Recordset dialog box.
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.
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.
Figure 21.19: The Detail.cfm Recordset dialog box
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
<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>
Figure 21.20: The browser result of Detail.cfm
|
|