Master/Detail Report Demo

This demo illustrates how to create a master/detail report using a DataGrid. Note that the SQL query to retrieve the FAQ categories is limited to only retrieving the first five categories - this was done to limit the size of the report (so it wasn't several pages long...).

Also, you may be wondering how to "pretty up" the FAQs "detail" DataGrid. You can programmatically set its aesthetic properties (such as the Font, HorizontalAlign, BackColor, etc.) in the ItemDataBound event handler. For example, if you wanted the "detail" DataGrid to have a red background you could add:

   dg.BackColor = System.Drawing.Color.Red

after the following line:

   Dim dg as New DataGrid()


CategoryFAQs
Application Object
FAQIDFAQCategoryIDDescriptionViewCount
1297Can I create an application-level Dictionary object?76235
1017How can I list all of the Application variables on a Web page?77615
1427How can I use application-level variables to cache information?97894
Arrays
FAQIDFAQCategoryIDDescriptionViewCount
1611How can I convert a Recordset into an array? Also, how can I convert an array into a Recordset?238089
261How can I convert the contents of an array into a string?69457
921How can I determine the position of a particular string in an Array?58403
211How can I determine the upper or lower bounds of an array?55032
841How can I display all of the contents of a single-dimension array?60990
971How can I get a count of the number of dimensions in an array?58949
931How can I quickly determine if a string exists within an array?73272
831How can I quickly sort a VBScript array? 230033
891How can I sort a VB Script array in ways other than by case-sensitive alphanumeric order, like numeric value, length of string, or even randomly?105385
1901How do I display data on a web page using arrays instead of Do...While...MoveNext...???...186006
71How do I dynamically resize an array?71897
861How do I extract the contents of an array without knowing the size of the array?53906
631How do I put an array into a Session or Application "variable"? How do I then use it on the next ASP page?108962
1461How is a two-dimensional laid out in memory?53696
1451How is an array stored in memory? I am interested in learning about the internals of an array!56577
ASP.NET
FAQIDFAQCategoryIDDescriptionViewCount
18122How can I format numbers and date/times using ASP.NET? For example, I want to format a number as a currency.964702
16322How do I convert a string into an Integer?123762
17722How much will ASP.NET (the .NET Framework) cost once Microsoft is finished with Beta testing and ready to roll a "final" version?46764
20222I have installed the .NET Framework, but my Web server won't serve ASP.NET Web pages. Help!84035
18722If I deploy an ASP.NET Web application will the sysadmin of the Web server where the code in installed be able to see my ASP.NET Web page source code?73651
17522Is ASP.NET output compatible with all browsers? If not, what browsers is the output compatible with? 53711
16222What are the differences between ASP.NET Beta 1 and Beta 2?52117
18222What are the differences between ASP.NET Beta 2 and Version 1.0?43770
18522What are the differences between C# and VB.NET, and which language should I use to create my ASP.NET Web pages?64405
15822What do I need to start working with ASP.NET?51057
15722What is ASP.NET?49574
15922Where can I learn more about ASP.NET?55617
Cookies
FAQIDFAQCategoryIDDescriptionViewCount
4111How can I delete an existing cookie?82522
2911How can I determine if a visitor has cookies support enabled in his/her browser?94329
5211How can I display all of the cookies/cookie values for a user through a Web page?59803
19611How can I share cookies between my server-side scripts (ASP) and my client-side scripts (e.g., JavaScript in the browswer)?47592
Databases, Errors
FAQIDFAQCategoryIDDescriptionViewCount
10721** UPDATED 24 June 2003 **: Why am I getting "Operation must use an updateable query" errors?119416
11521I am using Access and getting a 80004005 error (or a [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)' error) when trying to open a connection! How can I fix this problem?233530
13821I'm getting an error (80020009) when trying to work with a value from a Memo (or text or blob) column. For example, I get this error when I do: Response.Write objRS(MemoColumnName)54771
5821Why do I get a type mismatch error from my query? Why do I get the message Syntax error (missing operator) from my query? Why do I get a Too few parameters expected 1 error from my query? 61107
17421Why do I get a closed recordset from my stored procedure? Why don't I get the expected fields from the stored procedure?57541
13721Why do I get an error when I try to insert a value into a database that contains an apostrophe?49332
16021Why do I get the error "The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another" when I try to open a recordset?91729
8121Why do I get the message Microsoft JET Database Engine error ' 80040e14' Syntax error in INSERT INTO statement?? 61560
13921Why does the .RecordCount property return a value of -1?63999
  


Source Code
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.SQLClient" %>
<script language="VB" runat="server">
  Dim FAQsDS as New DataSet()

  Sub Page_Load(sender as Object, e as EventArgs)
    BindData()
  End Sub
	
	
  Sub BindData()
    '1. Create a connection
    Dim myConnection as New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))

    '2. Create the command object, passing in the SQL string
    Dim strCategorySQL, strFAQSQL as String 
    strCategorySQL = "SELECT TOP 5 FAQCategoryID, Name FROM tblFAQCategory ORDER BY Name"
    
    strFAQSQL = "SELECT FAQID, FAQCategoryID, Description, ViewCount " & _
                "FROM tblFAQ ORDER BY Description"
                
    Dim myCatCommand as New SqlCommand(strCategorySQL, myConnection)
    Dim myCatDA as New SqlDataAdapter(myCatCommand)
    
    Dim myFAQCommand as New SqlCommand(strFAQSQL, myConnection)    
    Dim myFAQDA as New SqlDataAdapter(myFAQCommand)
    
    'Fill the dataset
    myConnection.Open()
    myCatDA.Fill(FAQsDS, "Categories")
    myFAQDA.Fill(FAQsDS, "FAQs")    
    myConnection.Close()
    
    'Bind the Categories DataSet to the DataGrid
    dgFAQsByCategory.DataSource = FAQsDS.Tables("Categories")
    dgFAQsByCategory.DataBind()
  End Sub


  Sub buildFAQsDataGrid(sender as Object, e as DataGridItemEventArgs)
    If e.Item.ItemType = ListItemType.Item OR _
            e.Item.ItemType = ListItemType.AlternatingItem then
      'Build the DataGrid
      Dim dg as New DataGrid()
      
      'Find out the CategoryID
      Dim CatID as Integer = e.Item.DataItem("FAQCategoryID")
      
      'Create a DataView that has only the applicable FAQs
      Dim properFAQs as DataView = FAQsDS.Tables("FAQs").DefaultView
      properFAQs.RowFilter = "FAQCategoryID=" & CatID
      
      'Bind the Data to the DataGrid
      dg.DataSource = properFAQs
      dg.DataBind()
      
      'Add the DataGrid to the 2nd Column
      e.Item.Cells(1).Controls.Add(dg)
    End If
  End Sub

</script>

  <asp:DataGrid runat="server" id="dgFAQsByCategory"
         AutoGenerateColumns="False"
         Font-Name="Verdana" Width="85%"
         Font-Size="11pt" HorizontalAlign="Center"
         ShowFooter="True"
         OnItemDataBound="buildFAQsDataGrid">
         
      <HeaderStyle BackColor="Navy" ForeColor="White" HorizontalAlign="Center"
            Font-Size="14pt" Font-Bold="True" />
            
      <FooterStyle BackColor="Navy" ForeColor="White" Font-Size="12pt" 
            Font-Bold="True" HorizontalAlign="Right" />
    
    <Columns>
      <asp:BoundColumn DataField="Name" HeaderText="Category" />
      <asp:TemplateColumn HeaderText="FAQs">
        <ItemTemplate>
          
        </ItemTemplate>
      </asp:TemplateColumn>
    </Columns>
  </asp:DataGrid>

[Return to the article]