Master Detail – Multiple Tables

This sample shows how to create a full Master/Detail page, using the Northwind Database. First, we choose the Customer from a DropDownList, which populates the orders for that customer in a GridView.
By Selecting one of the orders in the Orders Gridview, we can get the details of that order, populating a second GridView.

Keep in mind – this sample is using ASP.Net 2.0 and there is no code making this all happen.

<html>
<head runat="server">
<script language="VB" Runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
SQLDSDetails.SelectCommand="SELECT dbo.[Order Details].OrderID, dbo.Products.ProductName, " & _
"dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity, dbo.[Order Details].Discount FROM " & _
"dbo.[Order Details] INNER JOIN dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID " & _
"WHERE (dbo.[Order Details].OrderID = @OrderID)"

SqlDataSource1.SelectCommand="SELECT dbo.Orders.OrderID, dbo.Orders.OrderDate, " & _
"dbo.Orders.ShippedDate, dbo.Employees.LastName FROM dbo.Orders INNER JOIN dbo.Employees " & _
"ON dbo.Orders.EmployeeID = dbo.Employees.EmployeeID WHERE (dbo.Orders.CustomerID = @CustomerID)"
End Sub
</script>
<title>Master-Detail/Order Details</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DropDownList ID="ddlCustomers" runat="server" AutoPostBack="True" DataSourceID="sqlDS1"
DataTextField="CompanyName" DataValueField="CustomerID" AppendDataBoundItems="True">
<asp:ListItem>Select</asp:ListItem>
</asp:DropDownList>
<asp:SqlDataSource ID="sqlDS1" runat="server"
ConnectionString="<%$ ConnectionStrings:NWConn2 %>"
SelectCommand="SELECT [CustomerID], [CompanyName] FROM [Customers]"></asp:SqlDataSource>

<table style="width: 100%">
<tr>
<td style="width: 166px" valign="top">
<asp:GridView ID="gvMaster" runat="server"
HeaderStyle-BackColor="#A0A0D0"
BackColor="#9DADEE"
AlternatingRowStyle-BackColor="#DFDFDF"
AutoGenerateColumns="False" DataKeyNames="OrderID" DataSourceID="SqlDataSource1"
AllowPaging="True" AllowSorting="True">
<Columns>
<asp:CommandField ShowSelectButton="True" />
<asp:BoundField DataField="OrderID" HeaderText="OrderID" InsertVisible="False" ReadOnly="True"
SortExpression="OrderID" />
<asp:BoundField DataField="OrderDate" HeaderText="Ordered" SortExpression="OrderDate"
DataFormatString="{0:d}" HtmlEncode="False" />
<asp:BoundField DataField="ShippedDate" HeaderText="Shipped" SortExpression="ShippedDate"
DataFormatString="{0:d}" HtmlEncode="False" />
<asp:BoundField DataField="LastName" HeaderText="SalesPerson" SortExpression="LastName" />
</Columns>
<SelectedRowStyle BackColor="AntiqueWhite" />
</asp:GridView>
</td>
<td style="width: 422px" valign="top">
<asp:GridView ID="gvDetails" runat="server" AutoGenerateColumns="False"
HeaderStyle-BackColor="#A0A0D0"
BackColor="#9DADEE"
AlternatingRowStyle-BackColor="#DFDFDF"
DataSourceID="sqlDSDetails">
<Columns>
<asp:BoundField DataField="OrderID" HeaderText="OrderID" SortExpression="OrderID" />
<asp:BoundField DataField="ProductName" HeaderText="ProductName" SortExpression="ProductName" />
<asp:BoundField DataField="UnitPrice" DataFormatString="{0:c}" HeaderText="UnitPrice"
HtmlEncode="False" SortExpression="UnitPrice">
<ItemStyle HorizontalAlign="Right" />
</asp:BoundField>
<asp:BoundField DataField="Quantity" HeaderText="Quantity" SortExpression="Quantity">
<ItemStyle HorizontalAlign="Center" />
</asp:BoundField>
<asp:BoundField DataField="Discount" DataFormatString="{0:P2}" HeaderText="Discount"
HtmlEncode="False" SortExpression="Discount">
<ItemStyle HorizontalAlign="Right" />
</asp:BoundField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="sqlDSDetails" runat="server"
ConnectionString="<%$ ConnectionStrings:NWConn2 %>">
<SelectParameters>
<asp:ControlParameter ControlID="gvMaster" Name="OrderID" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
&nbsp;
</td>
</tr>
</table>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NWConn2 %>">
<SelectParameters>
<asp:ControlParameter ControlID="ddlCustomers" Name="CustomerID" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
</form>
</body>
</html>

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>