There are quite a few different concepts shown in this sample. Here’s the meaning of the title. This sample shows how to choose from a list of categories, therefore producing a list of products inside that category. Then, from the list of products, you can get the details of that particular product.
Once again, this sample uses the Northwind database, but it uses both the Categories and the Products tables. It’s a SQL Server example, but as always, you can take the SQL code and run it through the Database Code Conversion Tool to get it converted to an OleDb example.
Among the many concepts this sample shows are:
- How to select and refer to an item in a DataGrid
- Use of the DataGrid’s ItemCommand and DataGridCommandEventArgs
- DataLists with Header and Item Templates
- DataGrids with ItemTemplates, using a LinkButton
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<script language="VB" runat="server">
Dim sCategory as String
Dim sProduct as String
Sub Page_Load(Source as Object, E as EventArgs)
if not Page.IsPostBack then
Dim strConn as string = "server=YourServer;uid=YourUID;pwd=YourPwd;database=Northwind"
Dim MySQL as string = "Select CategoryID, CategoryName from Categories"
Dim MyConn as New SQLConnection(strConn)
Dim objDR as SQLDataReader
Dim Cmd as New SQLCommand(MySQL, MyConn)
MyConn.Open()
objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
dg.DataSource = objDR
dg.DataBind()
MyConn.Close
end if
End Sub
Sub dg_ItemCommand(sender As Object, e As DataGridCommandEventArgs)
Dim strConn as string = "server=YourServer;uid=YourUID;pwd=YourPwd;database=Northwind"
if e.commandname="doCats" then
sCategory=CType(e.Item.FindControl("lb1"), LinkButton).Text
pnl1.visible=false
pnl2.visible=true
Dim intID as integer = dg.DataKeys(e.Item.ItemIndex)
Dim MySQL as string = "Select Products.ProductID, Products.ProductName from " & _
"Products where CategoryID=" & dg.DataKeys(e.Item.ItemIndex) & " Order by Products.ProductID"
Dim MyConn as New SQLConnection(strConn)
Dim objDR as SQLDataReader
Dim Cmd as New SQLCommand(MySQL, MyConn)
MyConn.Open()
objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
dg2.DataSource = objDR
dg2.DataBind()
MyConn.Close
elseif e.commandname="doProds" then
sProduct=CType(e.Item.FindControl("lb2"), LinkButton).Text
Dim intID as integer = dg2.DataKeys(e.Item.ItemIndex)
pnl1.visible=false
pnl2.visible=true
Dim MySQL as string = "Select * from Products where ProductID=" & dg2.DataKeys(e.Item.ItemIndex)
Dim MyConn as New SQLConnection(strConn)
Dim objDR as SQLDataReader
Dim Cmd as New SQLCommand(MySQL, MyConn)
MyConn.Open()
objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
dl.DataSource = objDR
dl.DataBind()
MyConn.Close
End If
End Sub
</script>
<html>
<head>
<meta name="GENERATOR" Content="ASP Express 3.0">
<title>Hierarchical Master/Detail<</TITLE>
<Link REL=STYLESHEET HREF="/basicArial.css" TYPE="text/css">
</head>
<body><form id="form1" runat="server">
<asp:panel ID="pnl1" runat="server">
<div align="center">
<asp:Datagrid runat="server"
Id="dg"
GridLines="None"
cellpadding="0"
cellspacing="0"
Headerstyle-BackColor="#FFFFFF"
Headerstyle-Forecolor="#FFFFFF"
Headerstyle-Font-Name="Arial"
Headerstyle-Font-Size="12"
HeaderStyle-Font-Bold="True"
BackColor="#FFFFFF"
Font-Name="Arial"
Font-Size="10"
Width="100%"
DataKeyField="CategoryID"
AutoGenerateColumns="False"
OnItemCommand="dg_ItemCommand">
<columns>
<asp:TemplateColumn>
<HeaderTemplate>
<b><i><font Size="4" Color="#0000FF">Categories</font></i></b>
</HeaderTemplate>
<ItemTemplate>
<asp:LinkButton id="lb1" HeaderText=''<%# Container.DataItem("CategoryName") %>''
Text=''<%# Container.DataItem("CategoryName") %>''
CommandName="doCats" runat="server" />
</ItemTemplate>
</asp:TemplateColumn>
</columns>
</asp:DataGrid>
</div>
</asp:panel>
<asp:panel ID="pnl2" Visible="false" runat="server">
<div align="center"><table width="100%"> <tr>
<td align="Left" valign="Top">
<asp:DataGrid runat="server"
Id="dg2"
GridLines="None"
cellpadding="0"
cellspacing="0"
Headerstyle-BackColor="#FFFFFF"
Headerstyle-Forecolor="#0F0F0F"
Headerstyle-Font-Name="Arial"
Headerstyle-Font-Size="12"
HeaderStyle-Font-Bold="True"
BackColor="#FFFFFF"
Font-Name="Arial"
Font-Size="10"
BorderColor="Black"
RepeatColumns="4"
RepeatDirection="Horizontal"
AutoGenerateColumns="False"
OnItemCommand="dg_ItemCommand"
DataKeyField="ProductID">
<columns>
<asp:TemplateColumn>
<HeaderTemplate>
<b><font Size="4" Color="#0000FF"><i>Products (Category -
<%# sCategory%>)</i></font></b>
</HeaderTemplate>
<ItemTemplate>
<asp:LinkButton id="lb2" Text=''<%# Container.DataItem("ProductName") %>''
CommandName="doProds" runat="server" />
</ItemTemplate>
</asp:TemplateColumn>
</columns>
</asp:DataGrid>
</td>
<td align="Left" valign="Top">
<asp:DataList runat="server"
Id="dl"
GridLines="None"
cellpadding="0"
cellspacing="0"
RepeatColumns="1"
RepeatDirection="Horizontal"
Headerstyle-BackColor="#FFFFFF"
Headerstyle-Forecolor="#0F0F0F"
Headerstyle-Font-Name="Arial"
Headerstyle-Font-Size="8"
BackColor="#C6C3FF"
Font-Name="Arial"
Font-Size="10">
<HeaderTemplate>
<b><font Size="4" Color="#0000FF"><i>Product Name - <%# sProduct%></i></font></b>
</HeaderTemplate>
<ItemTemplate>
<b>CategoryID: </b><%# Container.DataItem("CategoryID") %><br>
<b>Quantity Per Unit: </b><%# Container.DataItem("QuantityPerUnit") %><br>
<b>Unit Price: </b><%# String.Format("{0:c}", Container.DataItem("UnitPrice")) %><br>
<b>Units In Stock: </b><%# Container.DataItem("UnitsInStock") %><br>
<b>Reorder Level: </b><%# Container.DataItem("ReorderLevel") %><br>
</ItemTemplate>
</ASP:DataList>
</td>
</tr>
</table></div>
<div align="center"><hr>
<A HREF="/dw/choose.aspx">Choose a Category</A></div>
</asp:panel>
</form>
</body>
</html>