|
Filtering the DataGrid...
In this article we will filter the DataGrid on two columns. This adds a little code complexity but is not
really very hard to do.
By: John Kilgo
Date: June 1, 2003
Download the code.
Printer Friendly Version
In the example for this article we will use the Products table from the Northwind database. We will also access
the Categories table in order to populate one of our two DropDownLists. We will be able to select a category from
a DropDownList and have a DataGrid change to show only products from that category. The Products table contains
prices for each product. We will also be able to choose price ranges from a DropDownList and have the DataGrid
filter to show only products within the selected price range. We will use a DataView to accomplish this.
First we will take a look at the .aspx page DGFilter.aspx. It establishes the two DropDownLists as well as the
DataGrid. About the only the things you should take note of are in the two DropDownLists. Notice that we have
set AutoPostBack to True so that the grid will be filtered upon selection of one of the items in either of the
DropDownLists. Also notice that we have set up an event handler triggered by the OnSelectedIndexChanged
event. This is set to a subroutine in our code-behind file called ChangeFilter.
<%@ Page Language="vb" AutoEventWireup="false" Src="DGFilter.aspx.vb" Inherits="DGFilter"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>DGFilter</title>
<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
<meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
<meta name=vs_defaultClientScript content="JavaScript">
<meta name=vs_targetSchema content="http://schemas.microsoft.com/intellisense/ie5">
</head>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<asp:Label ID="lblCategory" Text="Category: " Runat="server" />
<asp:DropDownList ID="ddlCategoryFilter"
AutoPostBack="True"
OnSelectedIndexChanged="ChangeFilter"
Runat="server" />
<asp:Label ID="lblPriceFilter" Text="Price: " Runat="server" />
<asp:DropDownList ID="ddlPriceFilter"
AutoPostBack="True"
OnSelectedIndexChanged="ChangeFilter"
Runat="server">
<asp:ListItem Value="0" Selected="True">All Prices</asp:ListItem>
<asp:ListItem Value="1"><= $20</asp:ListItem>
<asp:ListItem Value="2">$20.01 - $40.00</asp:ListItem>
<asp:ListItem Value="3">$40.01 - $60.00</asp:ListItem>
<asp:ListItem Value="4">$60.01 - $80.00</asp:ListItem>
<asp:ListItem Value="5">Over $80.00</asp:ListItem>
</asp:DropDownList>
<p></p>
<asp:DataGrid ID="dtgProducts"
Runat="server"
HeaderStyle-BackColor="IndianRed"
HeaderStyle-ForeColor="White"
HeaderStyle-Font-Name="Verdana"
HeaderStyle-Font-Size="10"
ItemStyle-BackColor="Gainsboro"
ItemStyle-Font-Name="Verdana"
ItemStyle-Font-Size="10"
CellPadding="4"
GridLines="Both" />
</form>
</body>
</html>
Now for the code-behind file, DGFilter.aspx.vb. This will be shown in two parts to ease explanation of its
various sections. In part one, below, notice that set the category filter equal to 1 (Beverages) and the
price filter to UniitPrice > 0 meaning all prices will be shown. This will be the default setting when the
program first opens up. In the Page_Load event we call FillCategoryDropDown (the next subroutine) which fills
the Category DropDownList with the various product categories. After that is the subroutine BindTheData which
connects to the database, fills a DataSet with the products table data, and then creates a DataView with
strCategoryFilter and strPriceFilter as the row filter. This is analagous to adding a WHERE clause reading
"WHERE CategoryID=1 AND UnitPrice>0" for the first pass through the data. As we see in part two of the code,
strCategoryFilter or strPriceFilter may change based upon selections made by the user from the DropDownLists.
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Public Class DGFilter
Inherits System.Web.UI.Page
Public strCategoryFilter As String = "CategoryID=1"
Public strPriceFilter As String = "UnitPrice>0"
Public strSql As String
Protected ddlCategoryFilter As System.Web.UI.WebControls.DropDownList
Protected ddlPriceFilter As System.Web.UI.WebControls.DropDownList
Protected WithEvents lblCategory As System.Web.UI.WebControls.Label
Protected WithEvents lblPriceFilter As System.Web.UI.WebControls.Label
Protected dtgProducts As System.Web.UI.WebControls.DataGrid
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then
FillCategoryDropDown()
BindTheData()
End If
End Sub
Sub FillCategoryDropDown()
Dim objConn As SqlConnection
objConn = New SqlConnection(ConfigurationSettings.AppSettings("NorthwindConnection"))
strSql = "Select CategoryName, CategoryID From Categories"
Dim objCmd As New SqlCommand(strSql, objConn)
Dim dataReader As SqlDataReader = Nothing
Try
objConn.Open()
dataReader = objCmd.ExecuteReader()
ddlCategoryFilter.DataSource = dataReader
ddlCategoryFilter.DataTextField = "CategoryName"
ddlCategoryFilter.DataValueField = "CategoryID"
ddlCategoryFilter.DataBind()
Catch exc As System.Exception
Response.Write(exc.ToString())
Finally
objConn.Dispose()
End Try
End Sub
Sub BindTheData()
Dim objConn As SqlConnection
objConn = New SqlConnection(ConfigurationSettings.AppSettings("NorthWindConnection"))
strSql = "Select ProductID, ProductName, CategoryID, UnitPrice From Products"
Try
Dim dataAdapter As New SqlDataAdapter(strSql, objConn)
Dim dataSet As New DataSet()
dataAdapter.Fill(dataSet, "Products")
Dim dvProducts As New DataView(dataSet.Tables("Products"))
dvProducts.RowFilter = strCategoryFilter & " AND " & strPriceFilter
dtgProducts.DataSource = dvProducts
dtgProducts.DataBind
Catch exc As System.Exception
Response.Write(exc.ToString())
Finally
objConn.Dispose()
End Try
End Sub
Part two of the code shown below contains the more interesting code as far as the subject of the article is
concerned. Remember that in the .aspx file we set up an event handler called ChangeFilter in both of the
DropDownLists. It is call everytime a selection is made in either DropDownList. Here in the code-behind file
ChangeFilter calls two additional routines, FilterByCategory and FilterByPrice, before rebinding the data. When
these two routines are called they are passed the SelectedItem.Value and SelectedItem.Text respectively from
the appropriate DropDownLists. FilterByCategory simply builds our filter string strCategoryFilter by appending
the CategoryID from the value chosen by the user from the category dropdown. FilterByPrice gets passed the
text value from its DropDownList and the UnitPrice "WHERE clause" (strPriceFilter) is set appropriately.
Sub ChangeFilter(Source As System.Object, e As System.EventArgs)
FilterByCategory(ddlCategoryFilter.SelectedItem.Value.ToString())
FilterByPrice(ddlPriceFilter.SelectedItem.Text.ToString())
BindTheData()
End Sub
Sub FilterByCategory(strCategory As String)
strCategoryFilter = "CategoryID=" & strCategory
End Sub
Sub FilterByPrice(strPriceRange As String)
Select Case strPriceRange
Case "All Prices"
strPriceFilter = "UnitPrice>0"
Case "<= $20"
strPriceFilter = "UnitPrice<=20"
Case "$20.01 - $40.00"
strPriceFilter = "UnitPrice>20 AND UnitPrice<=40"
Case "$40.01 - $60.00"
strPriceFilter = "UnitPrice>40 AND UnitPrice<=60"
Case "$60.01 - $80.00"
strPriceFilter = "UnitPrice>60 AND UnitPrice<=80"
Case "Over $80.00"
strPriceFilter = "UnitPrice>80"
End Select
End Sub
End Class
I hope you found this code concise enough to follow without much trouble. Essentially, we are passing the
Category's DropDownList value and the Price's DropDownList Text to routines which build strings containing our
filters (strCategoryFilter and strPriceFilter) which then get passed back to the BindTheData routine to be
incorporated in the DataView's row filter. Best of luck.
You may download the code here.
|