When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML
Information:
Advertise
Feedback
Author an Article

ASP ASP.NET ASP FAQs Message Board Feedback
 
Print this Page!
Published: Wednesday, September 25, 2002

An Extensive Examination of the DataGrid Web Control: Part 10

By Scott Mitchell


The Tenth Part in a Multi-Part Series
This article is the tenth piece of a multi-part series on using the DataGrid Web control that will span several months. The ASP.NET DataGrid Web control, which displays database information in an HTML table, is highly versatile. The basics of the DataGrid were discussed in Part 1; information on specifying display properties of the DataGrid was discussed in Part 2. In Part 3 we examined how to associate custom events with the DataGrid. In Part 4 we looked at how to extend Part 3 to provide custom sorting on the results of a DataGrid. In Part 5 we examined how to use templates to further customize the DataGrid's appearance. Part 6 examined how to use the DataGrid's built-in editing capabilities, while Part 7 looked at customizing the editing interface using the EditItemTemplate. In Part 8 we looked at how to add client-side code to a ButtonColumn's client-side onclick event. In Part 9 we examined how to enhance the DataGrid's editing interface by having the editing interface's TextBox receive focus when the page is loaded. In this part we'll look at how to (automatically) add filtering buttons so that one can filter the data in a DataGrid!

- continued -

  • Read Part 11
  • Read Part 12
  • Read Part 13
  • Read Part 14
  • Read Part 15
  • Read Part 16
  • Read Part 17
  • Read Part 18
  • ASP.NET Data Web Controls Kick Start

    ASP.NET Data Web Controls Kick Start

    ASP.NET Data Web Controls Kick Start is author Scott Mitchell's most recent book, which thoroughly examines three of the most commonly used ASP.NET Web controls: the DataGrid, DataList, and Repeater. These three Web controls can be difficult to master due to their numerous features and capabilities. With this book, you'll quickly become an expert, learning the gritty details and true capabilities of each. This 400+ page book explores the topics in this article series in much greater depth, along with examining various topics and techniques not covered here.

    Scott Mitchell is the editor and founder of 4GuysFromRolla.com, author of the An Extensive Examination of the DataGrid Web Control article series, and author of numerous other ASP and ASP.NET books.

    [Buy this Book]
    [Visit the Book's Companion Web Site]

    Introduction


    In the previous nine parts of this article series we have looked at a number of live demos that work with the ASPFAQs.com database. Specifically, we have been calling a stored procedure, sp_Popularity, that returns the 25 most popular FAQs. In today's article we will be changing the SQL just slightly, instead using a SQL statement to get all of the FAQs that fit into a certain category.

    Realize that the FAQs are broken down into categories, such as: "Arrays," "Database, Errors," "Getting Start," "Security," etc. In fact, in Part 7 of this series we discussed the ASPFAQs.com database schema, presenting the following tables:

    tblFAQ
    FAQIDPrimary Key, integer
    Descriptionvarchar(255) - the "question" part of the FAQ
    FAQCategoryIDAn integer foreign key to the tblFAQCategory table
    Remaining rows omitted for brevity...
    tblFAQCategory
    FAQCategoryIDPrimary Key, integer
    Namevarchar(255) - the "title" of the category (Array, ASP.NET, etc.)

    To retrieve only a subset of rows from the tblFAQ table corresponding to a particular FAQCategoryID, we can do something like:

    SELECT Columns FROM tblFAQ WHERE FAQCategoryID = SomeFAQCategoryID

    In this article we will examine how to allow the user to decide what category of FAQs they wish to have displayed. This article will touch upon techniques discussed in the earlier parts of this article series, as well as information contained in the articles Dynamic Controls in ASP.NET and Working with Dynamically Created Controls. If you have not read the earlier parts to this article series, or have yet to read the other two articles, you are highly encouraged to do so before tackling this article.

    Changing the BindData Subroutine to Handle Filtering


    If you have worked through the live demos throughout this article series you will have noted that each demo has a BindData() subroutine that is responsible for binding the appropriate data to the DataGrid. Specifically, this subroutine performs the following tasks:

    1. Retrieves the data to display in the DataGrid from the SQL database that houses the ASPFAQs.com database, placing this data in a SqlDataReader.
    2. Specifies that the DataGrid's DataSource property should be assigned the SqlDataReader from step (1).
    3. Calls the DataGrid's DataBind() method.

    In our previous demos the SQL query performed by the BindData() subroutine was typically a call to the sp_Popularity stored procedure.

    In order to have the DataGrid display only those FAQs that belong to a certain FAQ category, we will need to have a parameterized SQL query of the following form:

    SELECT Columns
    FROM tblFAQ
    WHERE FAQCategoryID = @FAQCategoryIDParameter

    where the @FAQCategoryIDParameter is a SqlParameter whose value will be set programmatically.

    Since we already have this handy BindData() subroutine, let's concentrate on slightly altering this subroutine to provide the above SQL form (as opposed to writing a new subroutine from scratch). Since we want to only display FAQs from a particular FAQ category, let's alter the BindData() subroutine so that it accepts an input parameter: FilterOnFAQCategoryID, which will be an integer that signifies the FAQ category whose FAQs should be displayed in the DataGrid.

    After this small change, all that's left to do is adjust the SQL query from a constant that always issues the sp_Popularity stored procedure to one that uses a parameterized SQL query whose parameter is based on the FilterOnFAQCategoryID input parameter. The code below shows the new BindData(FilterOnFAQCategoryID) subroutine:

    Sub BindData(FilterOnFAQCategoryID as Integer)
      '2. Create the command object, passing in the SQL string
      Dim strSQL as String 
      strSQL = "SELECT FAQID, F.FAQCategoryID, F.Description, " & _
                      "FC.Name AS CategoryName " & _
               "FROM tblFAQ F " & _
                 "INNER JOIN tblFAQCategory FC ON " & _
                    "F.FAQCategoryID = FC.FAQCategoryID " & _
               "WHERE F.FAQCategoryID = @FAQCatID " & _
               "ORDER BY FAQID"
    
      'Set the datagrid's datasource to the datareader and databind
      Dim myConnection as New _
          SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
      Dim myCommand as SqlCommand = New SqlCommand(strSQL, myConnection) 
        
      Dim FAQCatIDParam as New SqlParameter("@FAQCatID", SqlDbType.Int, 4)
      FAQCatIDParam.Value = FilterOnFAQCategoryID
      myCommand.Parameters.Add(FAQCatIDParam)
        
      myConnection.Open()
      dgPopularFAQs.DataSource = myCommand.ExecuteReader()
      dgPopularFAQs.DataBind()
      myConnection.Close()
    End Sub
    

    The germane pieces of the code above are bolded. With this change to the BindData(FilterOnFAQCategoryID) subroutine, we can have the DataGrid display a list of FAQs belonging to FAQ category n by calling: BindData(n).

    Ok, so we have a nifty BindData(FilterOnFAQCategoryID) subroutine that, when called, will display FAQs of a particular FAQ category in the DataGrid. However, we need some way for the user to choose which FAQ category's FAQs she wishes to view. One user interface that would allow for this would be to list, at the top of the page, each of the FAQ categories as a hyperlink or button. If the user clicks one of these FAQ category hyperlinks or buttons, the ASP.NET page should be posted back and the DataGrid should be redisplayed, but this time with a list of the FAQs from the particular category whose hyperlink or button was clicked by the user.

    In Part 2 of this article we'll examine just how to do this!

  • Read Part 2!



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