Filtering GridView Table Rows using a Drop-Down List in ASP.NET 3.5

In ASP.NET, tables whose information is pulled from the database are developed using the GridView web control. Sometimes it is customary to pull all of the table data from the database and show it to the browser. This will show the complete information and will require simple coding. However, things can get complicated for the user if the objective is to sort information by categories, or group relevant information. This is where the filtering of table rows, particularly for those using GridView, is important.

This tutorial will illustrate this process with a complete project.

Case Example illustration

Suppose you want to create an ASP.NET project that will display a table of great guitarists, along with this information:

1. Playing style

2. Associated acts

3. Famous performance

And you will allow users to filter guitarist rows in terms of their playing style, for example:

1. Blues

2. Classical

3. Jazz

4. Rock

The complete web application will look like this:

Create the Guitarist database and Visual Web Developer Project

Any ASP.NET 3.5 project is easily created using Visual Web Developer Express. You need to create this project using that application with the following given information:

Project name: gridviewtablefiltering

Visual Studio Installed Templates: ASP.NET Web Site

Path: this depends on your computer; I save files for ASP.NET projects in my Drive E hard disk, inside the folder named "aspdotnetproject." So the path will be:

E:aspdotnetprojectgridviewtablefiltering

The path should include the project name.

File System: Visual Basic

Finally, if everything is set, click OK to create the default application files. Now read this important reference when creating the database, its tables and the associated data. 

The most important steps in that tutorial, which is titled "Creating an ASP.NET Database using MS SQL 2008 in Visual Web Developer 2008," are step 5 and beyond.

Refer to the database specification below. It is to be created using MS SQL server 2008:

a. Database name: guitarist (after database creation, the filename should be: guitarist.mdf)

b. Table name: topguitarist

c. Fields and specification

First Column Name: guitaristid

• Data type: int
  
• Allow nulls: not checked
  
• Others: Primary Key, Identity specification set to "Yes," Increment of 1.

Second Column Name: playername

• Data type: nvarchar(50)
  
• Allow nulls: not checked
  
• Others: none 

Third Column Name: playerstyle

• Data type: nvarchar(50)
  
• Allow nulls: not checked
  
• Others: none

Fourth Column Name: associatedacts

• Data type: nvarchar(50)
  
• Allow nulls: not checked
  
• Others: none

Fifth Column Name: famousperformance

• Data type: nvarchar(50)
  
• Allow nulls: not checked
  
• Others: none

d. Table data, which you can download at the link.

After creating the database and its tables, it should look like this:

Adding SqlDataSource Controls: DropDownlists and GridView

In yesterday's tutorial, on event handlers and automatic postback in ASP.NET 3.5 web controls, a dynamic dropdownlist web control used SqlDataSource controls to fetch data from the database. In another tutorial, gridview web controls also used SqlDataSource controls to retrieve data table from MS SQL server database.

This means you need to add two SqlDataSource controls (in default.aspx) with the IDs SqlDataSource1 and SqlDataSource2. SqlDataSource1 is used for the drop-down list, while SqlDataSource2 will be used for the Gridview web controls.

To configure SqlDataSource web controls, please refer to this tutorial.

For the DropDownList:

• Data Connection/Database to be connected: guitarist
  
• Specify columns from a table or view: Checked
  
• Name: topguitarist
  
• Columns: playerstyle (The information on the drop down menu field as shown in the browser: "Select appropriate guitarists playing style... is getting information from playerstyle field in the topguitarist table).
  
• Return only unique rows: Checked (this needs to be checked so that the drop down menu as shown in the web browser will not show repetitive information. For example, instead of rock, rock, jazz, blues, jazz, classical. The drop down menu will only show rock, jazz, blues, classical)

After clicking "Finish," the SqlDataSource code in the Default.aspx source view will be automatically updated to:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT DISTINCT [playerstyle] FROM [topguitarist]"></asp:SqlDataSource>

As for Gridview, the Gridview table SqlDataSource configuration will be slightly complicated for two reasons. First, Gridview table rows are dependent on the user's choices in the drop down menu (playerstyle). So it needs to be configured so that its results will depend on the user choices. Second, to filter information based on user choices, a WHERE statement must be included in the MS SQL SELECT syntax (but you don't need to worry about this any further as no "hard coding" is necessary in your part).

The specifications are divided into two parts:

Part 1

Data Connection/Database to be connected: ConnectionString

Specify columns from a table or view: Checked

Name: topguitarist

Columns: * (this means all columns will be returned to the browser)

Return only unique rows: NOT Checked (but in real world applications, this might not be checked to avoid duplication in the results) because the topguitarist table shows no duplicated rows.

Part 2

Specification for WHERE (can be set by clicking WHERE button in the Configure the SELECT statement):

Column: playerstyle (because the filtering condition on the browser shown by the drop down menu uses the playerstyle column)

Operator: = (the condition coming from the user using drop down menu should match database records).

Source: Control (because the input to SqlDataSource2 depends on dropdownlist web "control").

Control ID: DropDownList1 (the ID used by the dropdownlist web control).

Default Value: leave this blank (it's not needed).

Now click ADD and click FINISH to complete the SqlDataSource2 configuration. The syntax in Source Code view looks like this:

<asp:SqlDataSource ID="SqlDataSource2" runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT * FROM [topguitarist] WHERE ([playerstyle] = @playerstyle)">

<SelectParameters>

<asp:ControlParameter ControlID="DropDownList1" Name="playerstyle"

PropertyName="SelectedValue" Type="String" />

</SelectParameters>

</asp:SqlDataSource>

Adding a Dynamic DropDownList Web Control

This web control will be "dynamic" in the sense that it pulls records out of the database. To add it you will need to click and drag dropdownlist to the source code in Default.aspx below the SqlDataSource controls, and configure it as follows:

Text to describe the dropdownlist in the browser (see first screen shot for the location): Select appropriate guitarists playing style to filter the table rows:

SqlDataSource: SqlDataSource1 (because SqlDataSource1 is for DropDownList).

Select a data field to display in the dropdownlist: playerstyle (because the choices are data in "playerstyle").

Select a data field for the value of the dropdownlist: playerstyle

Specify columns from a table or view: Checked

Name: topguitarist

Columns: playerstyle

Return only unique rows: checked

Enable postback: Checked (so that once the user selects anything on the drop-down, the gridview results/rows will be automatically filtered or updated.)

Finally, click "Finish." The markup generated in "Source" looks like this:

<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True"

DataSourceID="SqlDataSource1" DataTextField="playerstyle"

DataValueField="playerstyle">

</asp:DropDownList>

Adding a GridView Web Control

Click and drag gridview from the toolbox to be placed below the drop down and configure it as follows (using Smart tag):

Choose Data Source: SqlDataSource2

Which data connection should your application...: ConnectionString

Specify columns from a table or view: Checked

Name: topguitarist

Columns: *

WHERE: (same as the Gridview WHERE configuration settings shown earlier)

AutoFormat: Apple Orchard

Here is the complete GridView source code:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"

BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px"

CellPadding="4" DataKeyNames="guitaristid" DataSourceID="SqlDataSource2"

ForeColor="Black" GridLines="Horizontal">

<Columns>

<asp:BoundField DataField="guitaristid" HeaderText="guitaristid"

InsertVisible="False" ReadOnly="True" SortExpression="guitaristid" />

<asp:BoundField DataField="playername" HeaderText="playername"

SortExpression="playername" />

<asp:BoundField DataField="playerstyle" HeaderText="playerstyle"

SortExpression="playerstyle" />

<asp:BoundField DataField="associatedacts" HeaderText="associatedacts"

SortExpression="associatedacts" />

<asp:BoundField DataField="famousperformance" HeaderText="famousperformance"

SortExpression="famousperformance" />

</Columns>

<FooterStyle BackColor="#CCCC99" ForeColor="Black" />

<PagerStyle BackColor="White" ForeColor="Black" HorizontalAlign="Right" />

<SelectedRowStyle BackColor="#CC3333" Font-Bold="True" ForeColor="White" />

<HeaderStyle BackColor="#333333" Font-Bold="True" ForeColor="White" />

</asp:GridView>

Complete Default.aspx source code: http://www.dotnetdevelopment.net/tutorials/gridviewtablefiltering.htm

Your project view in the "Design" tab in Visual Web Developer should look like:

Also, there is no source code for Default.aspx.vb. The basic programming and development concepts presented in this tutorial can be used to create a more complex web application involving a GridView control and a drop-down list.

Below is a screen shot of the complete working application with drop down choices set to "Jazz":