DataGrid Control and SqlDataSource

Filter Datagrid Data Via DropDown List Selection

1. Introduction to DataGrid Control

A DataGrid Control is a column and row based table like control. We can use this DataGrid control to display data that needs to be organized as rows and columns. In this article, we will explore using the Data Grid control with a combo box. We also see how to update the DataGrid Control’s content when the combo selection changes without writing any code. OK, let us start.

2. About The Example

Have a look at the below-given screenshot:

DataGrid Control and SqlDataSource
DataGrid Control and SqlDataSource

Here, in this example, we have two SqlDataSource 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 DataGrid will update the authors listed in the DataGrid.

3. Create Database Controls For Data Access

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
Setting-Up the Data Connection

After setting up the connection to the database, we drag the Authors table from the Table node and drop it to the default.aspx page. The below picture shows this:

Drag and Drop the Authors Table
Drag and Drop the Authors Table

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. The below picture shows how we pull the data and display it in the ASP Web Page:

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

Video 1: Displaying data in DataGrid Control

4.1 Setup DropDownList Control

After we place and configure the DataGrid control on the form (in the previous section we did that), we place one more SQLDataSource Control and a DropDownList Control on the form. You can refer to the below picture:

Add DropdownList to Display States
Add DropdownList to Display States

Note, this second data source control is will supply data for the DropDownList control. It pulls data from the State column of the author’s table.

4.2 Configure SqlDataSource For DropDownList

We must 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 we configure the SQLDataSource and DropDownList:

First, the SQLDataSource control (the second one added to the form) is configured to retrieve the state from the Authors table. Follow the steps to configure the second SqlDataSource control:

Configure DropdownList to Display States
Configure DropdownList to Display States
  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 move next to check the query output.

After the data source setup, the ASP.Net 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 ASP.Net 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 this concludes the DropDownList configuration.

4.3 Avoid State Display Repetition

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 must use a customized query that retrieves state column with no 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
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 4). Once observing the result as expected, we click the Finish button (Marked as 6).

Now, the drop DropDownList box shows state columns from Authors table with unique values. You can watch the video below to know how to setup the DropDownList with the DataSource.

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

At present, we have two SqlDataSource controls on the form and one control supplies data for the DataGrid Control and other one supplies data for the DropDownList Control. Now, we will link the DropDownList’s selected item to the Where Clause of the Grid’s SqlDataSource Control. After this, the DataGrid control which is receiving the data from the SqlDataSource1 will show the filtered result. For Example, if user selects the UT State in the DropDownList control, then DataGrid will show only the authors who are from the UT State. Choose Configure Data Source option from the quick access menu and then click the “Where” button.

Follow Screenshot here shows the array of steps for linking the ComboBox to the where condition of the SqlDataSource1 which feed data to the DataGrid Control:

Linking DropDownList to where clause of SqlDataSource Control
Linking DropDownList to where clause of SqlDataSource Control

In the DataSource (DataGrid Control) configuration dialog, we select where clause to filter the data (Marked as 1). To form the where clause, we take the State column and chose “=” as the operator (Marked as 3 and 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. The below video shows how to Configuring the DropDownList Control with the Where Clause of the SqlDataSource1 Control .

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

Do you like this Example? Please comment about it for others!!

This site uses Akismet to reduce spam. Learn how your comment data is processed.