Creating MasterDetail Forms

Creating Master/Detail Forms

The following sections describe an advanced application of data binding. Two methods of creating master/detail forms are discussed. You learn how to create both single-page and multipage master/detail forms.

What's a master/detail form? Imagine that you have two database tables named Products and Categories. Each product in the Products table is associated with a category in the Categories table. Now, suppose that you want the users of your Web site to be able to view different lists of products by selecting different categories. In this case, you need to build a master/detail form.

Or, imagine that you have a database table named Authors. For each author, you have such information as the author's first and last name , phone number, and so on. Suppose that you want to be able to select from a list of author last names and view details on a particular author. This is another situation in which you need to build a master/detail form.

Creating Single-Page Master/Detail Forms

One approach to building master/detail forms is to place both the master and detail information on the same page. For example, the page in Listing 10.14 contains a DropDownList control that represents product categories and a Repeater control that represents a list of products. When you select a product category, the corresponding products are displayed (see Figure 10.14).

Listing 10.14 `MasterDetail.aspx`

[View full width]

<%@ Import Namespace="System.Data.SqlClient" %>

<Script Runat="Server">

Sub Page_Load
  If Not IsPostBack Then
    Dim conNorthwind As SqlConnection
    Dim cmdSelect As SqlCommand
    Dim dtrCategories As SqlDataReader

    conNorthwind = New SqlConnection(

"Server=localhost;UID=sa;PWD=secret;Database=Northwind" )
    cmdSelect = New SqlCommand( "Select CategoryID, CategoryName From Categories",

conNorthwind )
    conNorthwind.Open()
    dtrCategories = cmdSelect.ExecuteReader()

    dropCategories.DataSource = dtrCategories
    dropCategories.DataTextField = "CategoryName"
    dropCategories.DataValueField = "CategoryID"
    dropCategories.DataBind()

    dropCategories.Items.Insert( 0, New ListItem( "none selected", -1 ) )

    dtrCategories.Close()
    conNorthwind.Close()
  End If
End Sub


Sub BindProducts( intCatID As Integer )
  Dim conNorthwind As SqlConnection
  Dim cmdSelect As SqlCommand
  Dim strSelect As String
  Dim dtrProducts As SqlDataReader

  conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" )
  conNorthwind.Open()

  strSelect = "Select ProductName, UnitPrice From Products Where CategoryID=@catID"
  cmdSelect = New SqlCommand( strSelect, conNorthwind )
  cmdSelect.Parameters.Add( "@catID", intCatID )
  dtrProducts = cmdSelect.ExecuteReader()

  rptProducts.DataSource = dtrProducts
  rptProducts.DataBind()

  dtrProducts.Close()
  conNorthwind.Close()
End Sub


Sub dropCategories_SelectedIndexChanged( s As Object, e As EventArgs )
  Dim intCatID As Integer

  intCatID = dropCategories.SelectedItem.Value
  If intCatID <> -1 Then
    BindProducts( intCatID ) 
  End If
End Sub

</Script>

<html>
<head><title>MasterDetail.aspx</title></head>
<body>
<form Runat="Server">

<b>Select Category:</b>
<asp:DropDownList
  ID="dropCategories"
  AutoPostBack="True"
  OnSelectedIndexChanged="dropCategories_SelectedIndexChanged"
  Runat="Server"/>

<p>

<asp:Repeater
  ID="rptProducts"
  EnableViewState="False"
  Runat="Server">

<ItemTemplate>
<li><%# Container.DataItem( "ProductName" ) %>
- <%# String.Format( "{0:c}", Container.DataItem( "UnitPrice" ) ) %>
</ItemTemplate>

</asp:Repeater>

</form>
</body>
</html>

The C# version of this code can be found on the CD-ROM.

Figure 10.14. Displaying a single page master/detail form.

graphics/10fig14.jpg

When you first open the page in Listing 10.14, the Page_Load subroutine is executed, and the DropDownList control is bound to the Categories table in the Northwind database. If you look closely at the Page_Load subroutine, you notice the following line of code:

dropCategories.Items.Insert( 0, New ListItem( "none selected", -1 ) )

This line inserts an additional item as the first item in the DropDownList control. When the page is first opened, you want the DropDownList control to display none selected for the current category.

When you pick a category with the DropDownList control, the dropCategories_SelectedIndexChanged subroutine is executed. If a category other than none selected has been selected, the products for the selected category are pulled from the Products database table and bound to the Repeater control.

Creating Multipage Master/Detail Forms

Instead of placing the master and detail information on the same page, you might want to place the information on separate pages. For example, suppose you want to create one page that contains a directory of employees who work at your company and a separate page that lists details for each employee.

The page in Listing 10.15 uses a Repeater control to display a list of employees (see Figure 10.15).

Listing 10.15 `Master.aspx`

[View full width]

<%@ Import Namespace="System.Data.SqlClient" %>

<Script Runat="Server">

Sub Page_Load
  Dim conNorthwind As SqlConnection
  Dim cmdSelect As SqlCommand
  Dim dtrEmployees As SqlDataReader

  conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" )
  cmdSelect = New SqlCommand( "Select EmployeeID,LastName From Employees", conNorthwind )
  conNorthwind.Open()
  dtrEmployees = cmdSelect.ExecuteReader()

  rptEmployees.DataSource = dtrEmployees
  rptEmployees.DataBind()

  dtrEmployees.Close()
  conNorthwind.Close()
End Sub

</Script>

<html>
<head><title>Master.aspx</title></head>
<body>
<form Runat="Server">

<h2>Employee Directory</h2>

<asp:Repeater
  ID="rptEmployees"
  Runat="Server">

<ItemTemplate>
<li>
<a href='<%# String.Format( "details.aspx?id={0}", Container.DataItem( "EmployeeID" ) )

%>'><%#
Container.DataItem( "LastName" ) %></a>
</ItemTemplate>

</asp:Repeater>

</form>
</body>
</html>

The C# version of this code can be found on the CD-ROM.

Figure 10.15. The master form.

graphics/10fig15.jpg

The employee's last names are displayed as a list of hyperlinks by the Repeater control. Each employee is a link to the Details.aspx page. The value of the EmployeeID column is passed in the link to the Details.aspx page as a query string variable.

The Details.aspx page is contained in Listing 10.16.

Listing 10.16 `Details.aspx`

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<Script Runat="Server">

Sub Page_Load
  Dim intEmployeeID As Integer
  Dim conNorthwind As SqlConnection
  Dim strSelect As String
  Dim cmdSelect As SqlCommand
  Dim dtrEmployee As SqlDataReader

  ' Retrieve Employee ID
  intEmployeeID = Int32.Parse( Request.QueryString( "id" ) )

  ' Get Employee Detail Information
  conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" )
  strSelect = "Select * From Employees Where EmployeeID=@employeeID"
  cmdSelect = New SqlCommand( strSelect, conNorthwind )
  cmdSelect.Parameters.Add( "@employeeID", intEmployeeID )
  conNorthwind.Open()
  dtrEmployee = cmdSelect.ExecuteReader( CommandBehavior.SingleRow )

  ' Display in Label Controls
  If dtrEmployee.Read Then
    lblEmployee.Text = dtrEmployee( "LastName" )
    lblEmployee.Text &= ", " & dtrEmployee( "FirstName" )
    lblPhone.Text = dtrEmployee( "HomePhone" )
    lblAddress.Text = dtrEmployee( "Address" )
    lblCity.Text = dtrEmployee( "City" ) & ", "
    lblRegion.Text = dtrEmployee( "Region" ).ToString()
    lblPostalCode.Text = dtrEmployee( "PostalCode" )
  End If

  dtrEmployee.Close()
  conNorthwind.Close()
End Sub

</Script>

<html>
<head><title>Details.aspx</title></head>
<body>
<form Runat="Server">

<h2>
<asp:Label
  ID="lblEmployee"
  Runat="Server"/>
</h2>

<p>

<b>Phone:</b>
<blockquote>
  <asp:Label
    ID="lblPhone"
    Runat="Server" />
</blockquote>

<b>Address:</b>
<blockquote>
  <asp:Label
    ID="lblAddress"
    Runat="Server" />
  <br>
  <asp:Label
    ID="lblCity"
    Runat="Server" />
  <asp:Label
    ID="lblRegion"
    Runat="Server" />
  &nbsp;&nbsp;
  <asp:Label
    ID="lblPostalCode"
    Runat="Server" />
</blockquote>

</form>
</body>
</html>

The C# version of this code can be found on the CD-ROM.

The Details.aspx page grabs whatever ID value was passed to it from the Master.aspx page and retrieves the information for the employee in the Employees database with that ID. The information is returned in a DataReader . The values for each of the columns in the DataReader are assigned to Label controls, and the selected employee's phone and address information is then displayed (see Figure 10.16).