When you think ASP, think...
Recent Articles xml
All Articles
ASP.NET Articles
Related Web Technologies
User Tips!
Coding Tips
spgif spgif

Book Reviews
Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
Web Hosts
Author an Article
spgif spgif

ASP ASP.NET ASP FAQs Feedback topnav-right
Print this Page!
Published: Wednesday, April 2, 2003

An Extensive Examination of the DataGrid Web Control: Part 14, Part 2

By Scott Mitchell

  • Read Part 1

  • In Part 1 we examined one way to create a master/detail report, but this approach sufferred from some inefficiency. In this final part, we'll examine how to surmount these inefficiencies using a DataSet and DataView.

    - continued -

    Filtering Data with a DataView

    Our new challenge is to be able to read in all of the FAQs and then selectively bind FAQs that meet certain criteria to a DataGrid. For example, when the ItemDateBound event fires for the row corresponding to the Arrays FAQ category (which has a FAQCategoryID of 1), we want to bind just those FAQs whose FAQCategoryID column equals 1 as well. Fortunately, this task is quite easy to accomplish with the use of the DataSet and a DataView.

    The DataView object in the .NET Framework allows for data to be viewed in a manner other than the way it's presented. For example, if we had a DataSet with a list of FAQs, we might want to view the data as if it was sorted upon some particular column. Or we might want to only view those FAQs that meet a certain criteria. We can accomplish this using a DataView. To get a filtered view of data, we use the DataView's RowFilter property. Specifically, we specify the WHERE clause we want to apply to the data. So, to see just the FAQs whose FAQCategoryID equals 1, we could set the RowFilter property to: FAQCategoryID = 1.

    A DataSet is made up of one or more DataTables. Each DataTables has a DefaultView, which is a DataView object instance. We can create a new DataView, assign it to the DataTable's DefaultView, set this new DataView's RowFilter property, and then bind the DataView to the programmatically created DataGrid. Following these sequence of steps, the "details" DataGrid created for each "master" row will contain just those "detail" subitems than belong to the "master" item.

    The updated code for the buildFAQsDataGrid event handler looks as follows:

    <script language="VB" runat="server">
      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
          'Add the DataGrid to the 2nd Column
        End If  End Sub

    The text in bold is the text that has changed since the previous incarnation of this event handler. Note that we create a DataView, properFAQs, and assign it to the DefaultView of the FAQs table in the FAQsDS DataSet. (We will examine how to populate this DataSet shortly.) Next, the DataView's RowFilter property is set according to the value of the FAQCategoryID. Lastly, the DataView is assigned to the DataGrid's DataSource property, the DataGrid's DataBind() method is called, and the DataGrid is programmatically added to the master DataGrid's TemplateColumn.

    All that remains unanswered is where the FAQsDS DataSet comes from and how it is populated. With this improved approach to a master/detail report, we need to also rework our BindData function. Rather than simply getting just the FAQ categories, we need to get all of the FAQ categories as well as all of the FAQs. Furthermore, we need to create a DataSet instance (FAQsDS) that is accessible to all functions on this ASP.NET Web page. These aims are accomplished with the following code:

    <script language="VB" runat="server">
      Dim FAQsDS as New DataSet()
      Sub BindData()
        '1. Create a connection
        Dim myConnection as New 
        '2. Create the command object, passing in the SQL string
        Dim strCategorySQL, strFAQSQL as String 
        strCategorySQL = "SELECT 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
        myCatDA.Fill(FAQsDS, "Categories")
        myFAQDA.Fill(FAQsDS, "FAQs")
        'Bind the Categories DataSet to the DataGrid
        dgFAQsByCategory.DataSource = FAQsDS.Tables("Categories")
      End Sub
    [View a Live Demo!]

    First, notice that a DataSet object instance, FAQsDS, is created in global scope, so that all functions in this ASP.NET Web page can refer to the DataSet. In the BindData() function, two SQL queries are formulated: one to retrieve all of the FAQ categories, and the other to retrieve all of the FAQs. Two DataTables are added to the FAQsDS DataSet, one denoted Categories and the other denoted FAQs. The Categories DataTable is then bound to the "master" DataGrid.

    Be sure to check out the live demo to see the master/detail report in action.


    In this article we examined how to create a master/detail report using a DataGrid. This involved creating an event handler for the "master" DataGrid's ItemDataBound event, and then programmatically creating and adding a "detail" DataGrid for each row of the "master" DataGrid. To improve performance, rather than issue a separate SQL query for each row, we retrieved all of the FAQs from the tblFAQ table in the BindData() function, and then used a DataView to display only the appropriate FAQs for each FAQ category row.

    Happy Programming!

  • By Scott Mitchell

  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article