Filtering Results Using the DropDownList

In the past three chapters we've been working with the Books database table and have seen how to use a GridView to display all of the books in this table. In Hour 14, "Accessing Data with the Data Source Web Controls," we saw how to add hard-coded filtering expressions with the SqlDataSource to limit the books returned by the data source control; specifically, we added two expressions that limited the returned books to those whose BookID was less than or equal to 3 and that were published in 2005.

Specifying hard-coded filtering expressions is one way to limit the data returned by a data source control, but we might rather allow the user visiting the web page to be able to specify how, exactly, the data is filtered. When specifying the values for a filter expression through the SqlDataSource control's wizard, we can specify that the value be based on another Web control on the page, rather than a hard-coded value. That is, we can craft a SqlDataSouce control such that its WHERE clause is based on, say, the selected value of a DropDownList control.

To illustrate this, let's first add an additional column to the Books table by which we can easily filter the records. Take a moment to add a Genre column of type nvarchar(50) to the Books table. Because there are existing records in the Books table, you'll either need to give this column a default value or configure it to allow Nulls. For now, let the Genre column allow Nulls.

By the Way

If you need to review how to specify the definition of a SQL Server 2005 database table in Visual Web Developer, consult Hour 13.

Those with a database background know that rather than adding an nvarchar(50) Genre column, we should ideally create a new Genre lookup table, adding a foreign key GenreID to the Books table. Feel free to go this route, if this makes sense to you; if what I just said is Greek to you, don't worry; just create the Genre column as discussed.

After you've added this new table column, show the table data in Visual Web Developer and enter values for the genres for each of the books. I used the genre Technology for Visual Studio Hacks and Create Your Own Website, the genre Business for The Number and the genre Fiction for The Catcher in the Rye and Fight Club.

Next, create a new ASP.NET page named DropDownList.aspx. When we're done, this page will contain a DropDownList control that lists all of the available genres and a GridView control that displays those books that match the genre selected in the DropDownList. This will require two SqlDataSource controls: one to retrieve the list of genres for the DropDownList and the other to grab those books that are of the selected genre for the GridView.

Listing the Genres in a DropDownList

Let's first concentrate on adding the DropDownList of genres. The DropDownList of genres will list each of the genres defined in the Books table. The user visiting the page can then filter the GridView results depending on the selected genre from the DropDownList. To create a DropDownList control listing the available genre choices, perform the following steps:

1.	Add a SqlDataSource control that is configured to return just the Genre column from the Books table. Have the results ordered by the value of the Genre column in ascending order.
2.	After completing the wizard, change this control's ID property from SqlDataSource1 to a more descriptive genresDataSource.
3.	Enter the text Choose a genre: and then add a DropDownList control to the page. Bind the DropDownList control to the genresDataSource SqlDataSource, using the Genre column as both the text and values of the DropDownList's items.
4.	Change the DropDownList control's ID from DropDownList1 to a more descriptive genres.

At this point, test your ASP.NET page through a browser (see Figure 17.5). Notice that the drop-down list contains five itemsone for each record in the Books tableeven though the table contains only three unique genre values.

Figure 17.5. A drop-down list item is available for each record in the Books table, rather than one for each unique genre.

To remedy this, we need to configure the SqlDataSource to return only the unique genres in the Books table. To accomplish this, reopen the SqlDataSource control's wizard, and in the Configure the Select Statement screen, check the Return Only Unique Rows check box, as shown in Figure 17.6.

Figure 17.6. Only the unique genres will be returned.

Watch Out!

For this solution to work as needed, it is important that the SELECT statement return only a single column, Genre.

The Return Only Unique Rows check box looks at all columns in the SELECT clause and considers a row a duplicate only if every value in the column list is equal to some other row's. That is, if you have the SELECT statement return, say, Title and Genre, currently all five records will be returned. If there were two books in the database with the same title and genre, then, and only then, would only one of these records be returned.

After making this change, view the page through a browser again. This time the drop-down list will have only three items: Business, Fiction, and Technology.

Filtering the Data Based on the Selected Genre

Our next step is to add a GridView control that displays only those books that belong to the selected genre. Let's first just add a GridView that displays all books from the Books table. We've done this many times in the past two hours, so this should be an easy task. Just drag on a SqlDataSource and configure it to return all columns and all records from the Books table. As we did with the genre-returning SqlDataSource, change this SqlDataSource control's ID from SqlDataSource1 to a more descriptive booksDataSource. Next, add a GridView to the ASP.NET page, binding it to the booksDataSource data source control. Rename the GridView's ID to books.

Take a moment to test the page in a browser. Because the booksDataSource SqlDataSource isn't filtering the books based on the genre yet, the GridView shows all of the books, regardless of what genre is selected from the drop-down list. Our next step, then, is to implement filtering within the booksDataSource SqlDataSource. To accomplish this, return to the data source control's wizard and, from the Configure Select Statement screen, click the WHERE button to bring up the Add WHERE Clause dialog box.

We want to add a WHERE clause to the SELECT statement that looks like:

SELECT * FROM [Books] WHERE [Genre] = Genre selected in the DropDownList 

Because we want this WHERE clause to operate on the Genre column, choose this column from the Column drop-down list. Select the = operator from the Operator drop-down list and then, from the Source drop-down list, choose Control because we want the filter expression to be based on the value of a Web control on the page. Choosing the Control option updates the Parameter Properties box in the upper-right corner to provide a drop-down list titled Control ID and a text box titled Default Value. From the Control ID drop-down list, select the genres control (our DropDownList); you don't need to pick a default value, so you can leave this text box blank.

Make sure your screen looks similar to Figure 17.7 and then click the Add button to add the parameter to the SqlDataSource. Click OK to return to the Configure the Select Statement screen and then complete the wizard.

Figure 17.7. Add a WHERE clause parameter whose value is the selected value of the genres DropDownList.

View the ASP.NET page through a browser again. This time, when the page first loads, the GridView should display only one book, The Number, because that's the only book that falls within the Business genre (see Figure 17.8). Go ahead and try changing the drop-down list to another genre. Nothing happens! We still see the GridView with one record, listing The Number. The reason is that we've yet to enable AutoPostBack in the DropDownList control. Simply changing the DropDownList doesn't cause a postback unless the DropDownList's AutoPostBack property is set to TRue. Take a moment to fix this and then revisit the page. Now selecting a different genre from the drop-down list invokes a postback and causes the GridView to be updated (see Figure 17.9).

Figure 17.8. The DropDownList.aspx page, when first visited.

Figure 17.9. The page after the user has changed the drop-down list's selection from Business to Technology.

By the Way

Setting the DropDownList's AutoPostBack property to true was not necessary. We could have optionally added a Button Web control to the page after the DropDownList with a Text property like "Refresh." Clicking this would have induced a postback and refreshed the GridView based on the user's drop-down list selection. In essence, we need a postback to refresh the GridView's display, which can be accomplished either through setting the AutoPostBack property to true or by adding a Button that the user can click to instigate the postback.