July 20, 2016

Asp.Net - Filter DataGridControl records based on DropDownList selected item

1. Introduction

A DataGrid is a column and row based table like control.  We can use this control to display data that needs to be organised in the form of rows and columns. In this article, we will explore using the Data Grid control with a combo box. OK, let us start.

2. About the example

Have a look at the below-given screenshot:

DataGrid Control and SqlDataSource
Fig 1. DataGrid Control and SqlDataSource

Here, in this example, we have two “SQL Data Source” controls and they both tied to Authors table of the Pubs database. The first data source control (Marked as 1) supplies data for the grid control (Marked as 3). The second data source control (Marked as 2) supplies the data to the combo box control (Marked as 4). When a user selects a state in the combo box, the authors listed in the DataGrid will be filtered.

3. Create Data source and Data Grid

First, we need to create the data source and data grid control to display the data from the authors table of pubs database. The Microsoft supplied pubs database can be downloaded from the SQL 2005 section of this website.

We can create both the controls in a single stretch. First, establish a connection to the pubs database through server explorer as shown in the screenshot below (Right Click the Data Connection node and create one) or Watch the video at the end of this section.

Setting-Up the Data Connection
Fig 2. Setting-Up the Data Connection

Once the connection to the database is established, drag the Authors table from the Table node and drop it to the default.aspx page. This action will create a “DataGrid Control” and an “SQLDataSource Control”. Now if we run the application, we can see the authors pulled from the Pubs database and displayed in the DataGrid Control.

Drag and Drop the Authors Table
Fig 3. Drag and Drop the Authors Table

This is the basic steps required to display data in the GridView and further customization is shown in the below video:

Video 1: Displaying data in DataGrid Control

4. Create Date source and combo box

Once DataGrid control is placed and configured on the form, Place the “SQLDataSource Control” and “DropDownList Control” on the form as shown in the below picture:

Add DropdownList to Display States
Fig 4. Add DropdownList to Display States

Remember, this second data source control is going to supply data for the DropDownList control and it queries the State column of the authors table. First, we have to configure the SQLDataSource control so that it can retrieve the State column from the Authors table of the Pubs database. The below screen shows how the SQLDataSource and DropDownList are configured:

Configure DropdownList to Display States
Fig 5. Configure DropdownList to Display States

First, the SQLDataSource control (the second one added to the form) is configured to retrieve the state from authors table. Follow the steps to configure the second SqlDataSource control:
  1. From SQLDataSource Quick Access Menu, Select “Configure Data Source …” (Marked as 1)
  2. Select the Table name as “Authors” as marked in 2
  3. Then pick the “state” column alone (Marked as 3) and click OK or move next to check the query output before hitting OK.

Once the data source is configured, the DropDownList Control can refer the data from it. To link the SqlDataSource with the DropDownList control, follow the below-given steps:
  1. From Quick Access Menu of the DropDownList, Select “Choose Data Source…” (Marked as 4)
  2. Then, select the column “State” for the both Display and actual value. (Marked as 5 and 6)
  3. Click Ok and we are done with the DropDownList configuration.

Now when we launch the web page, we can see the DropDownList populated with the State Column. However, we see a lot of repetition in the state column. To avoid, we have to use a customised query that retrieves state column without any repetition. The steps are given below for configuring the sqlDataSource2 in such a way that it will not return a state twice or more:

Setting the DataSource for DropDownList Control
Fig 6. Setting the DataSource for DropDownList Control

  1. First, Select the first Radio option to specify our own query (Marked as 1). Click the next button.
  2. A Tabbed dialog with 4 tabs displayed. Go to Select tab (Marked as 2) and write a select query (Marked as 3) that returns only unique values for the state column. Click the Next button.
  3. In this page, we can check our result (Marked as 5) by clicking the “Test Query” button (Marked as 6). Once observing the result as expected, the Finish button is clicked (Marked as 6).

Now, the drop DropDownList box shows state columns from Authors table without any redundant values. You can watch the video below to know how the DropDownList is configured with the DataSource.

Video 2: Adding Second Data Source and Linking that with DropDownList Box

5. Link the Combo box to Where clause of Data Source 1

At present, we have two SqlDataSource controls on the form and one control supplies data for the DataGrid Control and other one supplied data for the DropDownList Control. Let we link the DropDownList’s selected item to the Where Clause of the SqlDataSource1. Once this is done, the DataGrid control which receiving the data from the SqlDataSource1 will show the filtered result. For Example, if UT State is selected in the DropDownList control, then DataGrid will show only the authors who are from the CA State. Choose Configure Data Source option from the quick access menu and then click the “Where” button. Follow Screenshot Chart for the sequence of steps for linking the ComboBox to the where condition of the SqlDataSource1 which supplied data to the DataGrid Control:

Fig 7. Linking DropDownList to where clause of SqlDataSource Control

Here, we selected the Control as the Where Clause source (Marked as 4). Once we do that, the combo box (Marked as 5) is populated with controls in the form which are valid for supplying the data for Where Condition. We picked our “DropDownList Control” and then the “Add” button (Marked as 6) is clicked. Finally, the OK button is clicked to accept the changes made the DataSource Control. Now run the sample application and we will see the DataGrid Control filters the displayed author based on the selection that we make in the DropDownList Control. Configuring the DropDownList Control with the Where Clause of the SqlDataSource1 Control is shown in the below video.

Video 3: Using a Control is the Where Clause of SqlDataSource Control

Source code: Download
You need link SqlDataSource with your database after downloading the code.

No comments:

Post a Comment

Leave your comment(s) here.

Like this site? Tell it to your Friend :)