Filtering from a Drop-down List

Chapter 5 - Reading Data using the Dataset Object
byJohn Kauffman, Fabio Claudio Ferracchiatiet al.?
Wrox Press ?2002

For our final example in this chapter, we'll look at binding data to a drop-down list, and using the value selected from the list to filter and provide different views of that data. This quite large sample will bring together a number of the things that we've been looking at over the last two chapters, so don't let it's length put you off. For the most part, it's things you've seen before, working in slightly different contexts.

Try It Out - Binding to a Drop-down List

start example

We're going to implement a page that displays the products available from Northwind, and allows them to be filtered by their category (beverages, condiments, etc.), and by price range.

  1. In the ch05 folder, create a new file called ProductFilter.aspx, and enter the following code:

     <%@ Import namespace="System.Data" %> <%@ Import namespace="System.Data.SqlClient" %> <html>   <head><title>DataGrid Filtering</title></head>   <body>     <h3>DataGrid Filtering</h3>     <form runat="server">       <asp:Literal  runat="server"                    Text="Category:" />       <asp:DropDownList  runat="server"                         AutoPostBack="True"                         OnSelectedIndexChanged="FilterChange" />       <asp:Literal  runat="server"                    Text="Price Range:" />       <asp:DropDownList  runat="server"                         AutoPostBack="True"                         OnSelectedIndexChanged="FilterChange">         <asp:ListItem Value="0" Selected="True">Any Price</asp:ListItem>         <asp:ListItem Value="1">Cheap</asp:ListItem>         <asp:ListItem Value="2">Moderate</asp:ListItem>         <asp:ListItem Value="3">Expensive</asp:ListItem>         <asp:ListItem Value="4">Absurdly Expensive</asp:ListItem>       </asp:DropDownList>       <br/><br/>       <asp:DataGrid  runat="server">         <HeaderStyle BackColor="#C0C0FF" />         <ItemStyle BackColor="#F1F1F1" />         <AlternatingItemStyle BackColor="#E8E6E6" />       </asp:DataGrid>     </form>   </body> </html> 

    In the above, the only difference between a Literal and the Labels we've worked with before is that the former doesn't allow you to apply styles to its content.

     <script language="VB" runat="server"> Public strCategoryFilter As String = "CategoryID=1" Public strPriceFilter As String = "UnitPrice>0" Sub Page_Load(Source As Object, E As EventArgs)   If Not Page.IsPostBack Then     FillDropDownList()     DataFiller()   End If End Sub Sub FillDropDownList()   Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")   Dim objConnection As New SqlConnection (strConnection)   Dim strSqlCategories As String = "SELECT CategoryName, CategoryID " & _                                    "FROM Categories"   Dim objCommand As New SqlCommand (strSqlCategories, objConnection)   Dim objReader As SqlDataReader = Nothing   objConnection.Open()   objReader = objCommand.ExecuteReader()   ddlCategoryFilter.DataSource = objReader   ddlCategoryFilter.DataTextField = "CategoryName"   ddlCategoryFilter.DataValueField = "CategoryID"   ddlCategoryFilter.DataBind()   objConnection.Close() End Sub Sub DataFiller()   Dim strConnection As String = ConfigurationSettings. AppSettings("NWind")   Dim objConnection As New SqlConnection(strConnection)   Dim strSqlProducts As String = "SELECT ProductID, ProductName, " & _                                  "CategoryID, UnitPrice FROM Products"   Dim objAdapter As New SqlDataAdapter (strSqlProducts, objConnection)   Dim objDataSet As New DataSet()   objAdapter.Fill(objDataSet, "dtProducts")   ' Create our DataView and filter by category and price   Dim dvUK As New DataView(objDataSet.Tables("dtProducts"))   dvUK.RowFilter = strCategoryFilter & " AND (" & strPriceFilter & ")"   dgProducts.DataSource = dvUK   dgProducts.DataBind() End Sub Sub FilterChange(Source As Object, E As EventArgs) FilterByPrice(ddlPriceFilter.SelectedItem.Text.ToString())   FilterByCategory(ddlCategoryFilter.SelectedItem.Value.ToString())   DataFiller() End Sub Sub FilterByPrice(strChoice As String)   Select strChoice     Case "Any Price"       strPriceFilter = "UnitPrice>0"     Case "Cheap"       strPriceFilter = "UnitPrice<20"     Case "Moderate"       strPriceFilter = "UnitPrice>19 AND UnitPrice<50"     Case "Expensive"       strPriceFilter = "UnitPrice>=50"     Case "Absurdly Expensive"       strPriceFilter = "UnitPrice>100"   End Select End Sub Sub FilterByCategory(strChoice As String)   strCategoryFilter = "CategoryID = " & strChoice End Sub </script> 

  2. Viewing this page in a browser produces output like the following:

    click to expand

  3. We can view the confections by selecting Confections from the Category drop-down list. After that, selecting Cheap from the Price Range drop-down list displays all the cheap confections available:

    click to expand

end example

How It Works

The main points of the code in this page are:

  • The DataGrid properties that give the table its visual appeal

  • The use of two public strings to hold our filter expressions

  • Binding data from the Categories table to a drop down list

  • The use of one handler to deal with a change in either drop-down list

  • Retaining the value selected in the Category drop-down list between pages

Let's look first at the HTML for the page, which is a little more elaborate than usual:

         <form runat="server">           <asp:Literal  runat="server"                        Text="Category:" />           <asp:DropDownList  runat="server"                        AutoPostBack="True"                        OnSelectedIndexChanged="FilterChange" />           <asp:Literal  runat="server"                        Text="Price Range:" />           <asp:DropDownList  runat="server"                             AutoPostBack="True"                             OnSelectedIndexChanged="FilterChange"> 

We have a server-side < form> element, within which we have two labels that display the legends Category and Price Range in order to identify the two drop-down lists to the visitor. There are two things to note about those list controls here:

  • The ddlCategoryFilter list has no items specified for it. Instead, it will be filled from the Categories table at runtime.

  • For both list controls, the OnSelectedIndexChanged attribute is set to the same value: Filter Change. This means that whenever the user changes the selection in either drop-down list, FilterChange() will be called.

Continuing with the HTML, we have the items for the price filter drop-down list:

            <asp:ListItem Value="0" Selected="True">Any Price</asp:ListItem>            <asp:ListItem Value="1">Cheap</asp:ListItem>            <asp:ListItem Value="2">Moderate</asp:ListItem>            <asp:ListItem Value="3">Expensive</asp:ListItem>            <asp:ListItem Value="4">Absurdly Expensive</asp:ListItem>          </asp:DropDownList> 

Next, we have our DataGrid control. Notice that we use the BackColor attribute of the HeaderStyle element to set the background color of the grid's header row, that the background color of the rows of the grid is set through BackColor attribute of ItemStyle, and that the background color of every other row is set with the BackColor attribute of AlternatingItemStyle. This gives our DataGrid control a more pleasing appearance.

            <asp:DataGrid  runat="server">              <HeaderStyle BackColor="#C0C0FF" />              <ItemStyle BackColor="#F1F1F1" />              <AlternatingItemStyle BackColor="#E8E6E6" />            </asp:DataGrid>          </form> 

In our VB.NET code, the first thing we do is to define two public strings that will hold default values for our first filter.

     Public strCategoryFilter As String = "CategoryID=1"     Public strPriceFilter As String = "UnitPrice>0" 

In Page_Load(), we use Page. IsPostBack to determine whether this is the first time the page has been loaded. If so, we populate our drop-down list with FillDropDownList(), and call DataFiller() to display our data.

 Sub Page_Load(Source As Object, E As EventArgs)   If Not Page.IsPostBack Then     FillDropDownList()     DataFiller()   End If End Sub 

If it is not the first time the page has been loaded, DataFiller() will be called by FilterChange(), which itself will be called when the visitor selects an entry from one of the drop-down lists.

In FillDropDownList(), we use a data reader object to bind the categories from the Categories table to the ddlCategoryFilter drop-down list. We only have to populate the drop-down list once, when the page is first loaded - the values will persist between postbacks, so there's no need to refill it when they occur. FillDropDownList() begins with the usual connection and command objects, opens a connection, and uses ExecuteReader() to begin reading the data. This should all look quite familiar.

    Sub FillDropDownList()      Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")      Dim objConnection As New SqlConnection (strConnection)      Dim strSqlCategories As String = "SELECT CategoryName,CategoryID " & _                                       "FROM Categories"      Dim objCommand As New SqlCommand(strSqlCategories, objConnection)      Dim objReader As SqlDataReader = Nothing      objConnection.Open()      objReader = objCommand.ExecuteReader() 

Next, we bind the incoming data from the Categories table of Northwind to ddlCategoryFilter. You'll recall that the DataTextField property sets the field that will actually be displayed in the list, while DataValueField sets the field that will be used to provide the values that will be returned.

       ddlCategoryFilter.DataSource = objReader       ddlCategoryFilter.DataTextField = "CategoryName"       ddlCategoryFilter.DataValueField = "CategoryID"       ddlCategoryFilter.DataBind() 

Finally (and very importantly!), we close the connection:

      objConnection.Close()    End Sub 

Next up for investigation is DataFiller(). The first part of this is pretty standard: we create the relevant objects, and fill a DataTable with data from the Products table of Northwind.

    Sub DataFiller()      Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")      Dim objConnection As New SqlConnection(strConnection)      Dim strSqlProducts As String = "SELECT ProductID, ProductName, " & _                                     "CategoryID, UnitPrice FROM Products"      Dim objAdapter As New SqlDataAdapter(strSqlProducts, objConnection)      Dim objDataSet As New DataSet()      objAdapter.Fill(objDataSet, "dtProducts") 

Then we create our DataView object and set its RowFilter property to filter with category information and price information, using strCategoryFilter and strPriceFilter.

       ' Create our DataView and filter by category and price       Dim dvUK As New DataView(objDataSet.Tables("dtProducts"))       dvUK.RowFilter = strCategoryFilter & " AND (" & strPriceFilter & 

Finally, we bind the DataView to our DataGrid.

      dgProducts.DataSource = dvUK      dgProducts.DataBind()    End Sub 

In FilterChange(), we call both FilterByPrice() and FilterByCategory() .To the former, we pass the string that has been selected from ddlPriceFilter; to the latter, we pass a string that has been chosen from ddlCategoryFilter. These two procedures will set up the correct strings for filtering our data:

    Sub FilterChange(Source As Object, E As EventArgs)      FilterByPrice(ddlPriceFilter.SelectedItem.Text.ToString())      FilterByCategory(ddlCategoryFilter.SelectedItem.Value.ToString()) 

Finally in FilterChange(), we call DataFiller() to redisplay our data.

      DataFiller()    End Sub 

In the last two procedures, we actually set up the strings for filtering the displayed data. In FilterByPrice(), we use a Select...Case statement to set up the correct value for strPriceFilter:

    Sub FilterByPrice(strChoice As String)      Select strChoice        Case "Any Price"          strPriceFilter = "UnitPrice>0"        Case "Cheap"          strPriceFilter = "UnitPrice<20"        Case "Moderate"          strPriceFilter = "UnitPrice>19 AND UnitPrice<50"        Case "Expensive"          strPriceFilter = "UnitPrice>=50"        Case "Absurdly Expensive"          strPriceFilter = "UnitPrice>100"      End Select    End Sub 

In FilterByCategory(), we simply set strCategoryFilter to the string passed to it:

    Sub FilterByCategory(strChoice As String)      strCategoryFilter = "CategoryID = " & strChoice    End Sub 

And that's all there is to it! With ASP.NET, as you can surely begin to see, you don't need to do a lot of work in order to get useful, well-presented results.



Beginning ASP. NET 2.0 and Databases
Beginning ASP.NET 2.0 and Databases (Wrox Beginning Guides)
ISBN: 0471781347
EAN: 2147483647
Year: 2004
Pages: 263

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net