Source code for nested data grids RSS

• • Reply
  francowell

  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

   

• • Reply
  JohnySmithy2...

  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.

   

   

  Thanks,
  Smith
  http://spaces.msn.com/johnysmith