About a week ago I posted a request for help with nested DataGrids. Since I received only one response, I'm thinking that it might be a problematic area for many list members. Through my research efforts and much trial & error, I was eventually successful
in creating a drill down effect with nested datagrids. I will paste the source code below so that other list members may benefit from my efforts. Remember to change your connection string, your SQL statements, and the column names in the datagrids accordingly.
You may also want to add some more elaborate formatting to the datagrids. I purposefully kept it simple to demonstrate the principle. Here's the code:
'******************************************
Sub Page_Load
If Not Page.IsPostBack Then
txtStart.text = (DateAdd("d", -30, Today)).tostring("d")
txtEnd.text = (DateAdd("d", +1,Today)).tostring("d")
dgOuter.DataSource = GetOuter()
dgOuter.DataBind()
End If
End Sub
'******************************************
Function GetOuter As SQLDataReader
Dim sReportsSQL As String
End Function
'******************************************
Function GetInner(iReport as Integer) As SQLDataReader
Dim sUsersSQL as String
Dim sStartDate as String
Dim sEndDate as String
End Function
'******************************************
Function GetReader(sSQL as String) As SQLDataReader
Dim cn as SQLConnection
cn = New SQLConnection("SERVER=ServerName;" & _
"uid=userid;" & _
"pwd=password;" & _
"database=DatabaseName")
cn.Open()
Dim cmdSelect as New SQLCommand (sSQL, cn)
Return cmdSelect.ExecuteReader(CommandBehavior.CloseConnection)
End Function
'******************************************
Sub BindInnerGrid(sender As Object, e As System.Web.UI.WebControls.DataGridItemEventArgs)
' see what type of row (header, footer, item, etc.) caused the event
Dim oType As ListItemType = CType(e.Item.ItemType, ListItemType)
' only process it if it's the Selected Row
If oType = ListItemType.SelectedItem Then
' get a reference to the ReportId in the Selected row
Dim iKey as Integer
iKey = dgOuter.DataKeys(e.Item.ItemIndex)
' get a reference to the DataGrid control in this row
Dim dg as DataGrid
dg = Ctype(e.Item.FindControl("dgInner"), DataGrid)
' bind nested "Users" DataGrid to child DataView
dg.DataSource = GetInner(iKey)
dg.DataBind()
End If
End Sub
'*********************************************
Sub DoItemSelect(source As Object, e As System.Web.UI.WebControls.DataGridCommandEventArgs)
If e.CommandName = "Select" Then
dgOuter.SelectedIndex = e.Item.ItemIndex
dgOuter.DataSource = GetOuter()
dgOuter.DataBind()
End If
End Sub
'*********************************************
</script>
<center>
<p><Asp:label ID="lblOne" runat="Server"><b><font size="6" color="indianred">Users Of Integrity Reports</font></b></asp:Label></p>
<p><b><font size= '2' color = 'red'>Enter a date range then click the Get User List button to obtain a list of users!</b></font></p>
None
0 Points
36 Posts
Source code for nested data grids
Feb 21, 2006 09:47 AM|francowell|LINK
About a week ago I posted a request for help with nested DataGrids. Since I received only one response, I'm thinking that it might be a problematic area for many list members. Through my research efforts and much trial & error, I was eventually successful in creating a drill down effect with nested datagrids. I will paste the source code below so that other list members may benefit from my efforts. Remember to change your connection string, your SQL statements, and the column names in the datagrids accordingly. You may also want to add some more elaborate formatting to the datagrids. I purposefully kept it simple to demonstrate the principle. Here's the code:
<%@ Page Language="VB" Debug="true" EnableViewState="true"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<script runat="server">
'******************************************
Sub Page_Load
If Not Page.IsPostBack Then
txtStart.text = (DateAdd("d", -30, Today)).tostring("d")
txtEnd.text = (DateAdd("d", +1,Today)).tostring("d")
dgOuter.DataSource = GetOuter()
dgOuter.DataBind()
End If
End Sub
'******************************************
Function GetOuter As SQLDataReader
Dim sReportsSQL As String
sReportsSQL = "SELECT DISTINCT r.ReportName, r.ReportID " & _
"FROM Reports r " & _
"WHERE r.ReportID <> 0 " & _
"ORDER BY r.ReportID"
Return GetReader(sReportsSQL)
End Function
'******************************************
Function GetInner(iReport as Integer) As SQLDataReader
Dim sUsersSQL as String
Dim sStartDate as String
Dim sEndDate as String
sStartDate = txtStart.text
sEndDate = txtEnd.text
sUsersSQL = "Select ru.Name 'User', r.ReportName, Date, r.ReportID " & _
"FROM ReportUsers ru " & _
"Inner Join Reports r " & _
"ON ru.ReportId = r.ReportID " & _
"WHERE Date >= '" & sStartdate & "'" & _
"And Date <= '" & sEndDate & "'" & _
"And r.TypeID = 1 " & _
"And r.ReportID = " & iReport
Return GetReader(sUsersSQL)
End Function
'******************************************
Function GetReader(sSQL as String) As SQLDataReader
Dim cn as SQLConnection
cn = New SQLConnection("SERVER=ServerName;" & _
"uid=userid;" & _
"pwd=password;" & _
"database=DatabaseName")
cn.Open()
Dim cmdSelect as New SQLCommand (sSQL, cn)
Return cmdSelect.ExecuteReader(CommandBehavior.CloseConnection)
End Function
'******************************************
Sub BindInnerGrid(sender As Object, e As System.Web.UI.WebControls.DataGridItemEventArgs)
' see what type of row (header, footer, item, etc.) caused the event
Dim oType As ListItemType = CType(e.Item.ItemType, ListItemType)
' only process it if it's the Selected Row
If oType = ListItemType.SelectedItem Then
' get a reference to the ReportId in the Selected row
Dim iKey as Integer
iKey = dgOuter.DataKeys(e.Item.ItemIndex)
' get a reference to the DataGrid control in this row
Dim dg as DataGrid
dg = Ctype(e.Item.FindControl("dgInner"), DataGrid)
' bind nested "Users" DataGrid to child DataView
dg.DataSource = GetInner(iKey)
dg.DataBind()
End If
End Sub
'*********************************************
Sub DoItemSelect(source As Object, e As System.Web.UI.WebControls.DataGridCommandEventArgs)
If e.CommandName = "Select" Then
dgOuter.SelectedIndex = e.Item.ItemIndex
dgOuter.DataSource = GetOuter()
dgOuter.DataBind()
End If
End Sub
'*********************************************
</script>
<html>
<head><title>Integrity Report Users</title></head>
<body>
<center>
<p><Asp:label ID="lblOne" runat="Server"><b><font size="6" color="indianred">Users Of Integrity Reports</font></b></asp:Label></p>
<p><b><font size= '2' color = 'red'>Enter a date range then click the Get User List button to obtain a list of users!</b></font></p>
<form Runat="Server">
<ASP:Label Id="lblMsg"
forecolor="red"
font-Bold="true"
runat="Server" /><br>
<table valign = 'top'>
<tr>
<td><ASP:Label Id="lblStart"
width="100"
text="Start Date"
forecolor="red"
font-Bold="true"
runat="Server" /></td>
<td><ASP:Label Id="lblEnd"
width="100"
text="End Date"
forecolor="red"
font-Bold="true"
runat="Server" /></td>
</tr>
<tr>
<td><ASP:TextBox Id="txtStart"
width="100"
runat="Server" /></td>
<td><ASP:TextBox Id="txtEnd"
width="100"
runat="Server" /></td>
</tr>
</table>
<center>
<p><b><asp:label id="lblReportName" Runat="Server" /></b>
</center>
<table align = 'left' >
<tr>
<td valign = 'top'>
<asp:DataGrid
ID='dgOuter'
AutoGenerateColumns='false'
DataKeyField = 'ReportID'
OnItemCommand='DoItemSelect'
OnItemDataBound='BindInnerGrid'
runat = 'Server' >
<Columns>
<asp:BoundColumn
HeaderText='Rpt ID'
DataField='ReportID'/>
<asp:ButtonColumn
HeaderText='Report Name'
CommandName = 'Select'
DataTextField ='ReportName'/>
<asp:TemplateColumn
HeaderText='User Details'>
<ItemTemplate>
<asp:DataGrid
ID='dgInner'
AutoGenerateColumns='false'
Runat='Server' >
<Columns>
<asp:BoundColumn
HeaderText='User'
DataField='User'/>
<asp:BoundColumn
HeaderText='Date'
DataField='Date'
DataFormatString='{0:MM/dd/yyyy}'/>
</columns>
</asp:DataGrid>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:DataGrid>
</tr>
</table>
</form>
</body>
</html>
Happy Coding.................Franco
Member
50 Points
158 Posts
Re: Source code for nested data grids
Feb 28, 2006 05:12 AM|JohnySmithy2k5|LINK
Franco:
Why don't you use Dino's stuff on Nested DataGrid as following URL
http://msdn.microsoft.com/msdnmag/issues/03/10/CuttingEdge/default.aspx
Source Code: http://download.microsoft.com/download/6/8/b/68b5ba46-1586-4cf9-a345-ea5a80b51192/CuttingEdge0310.exe
Hope this helps.
Smith
http://spaces.msn.com/johnysmith