locked
Source code for nested data grids RRS feed

  • Question

  • User396363013 posted

    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

     

    Tuesday, February 21, 2006 9:47 AM

All replies