Sams Teach Yourself ASP.NET 2.0 in 24 Hours, Complete Starter Kit

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.

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:

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.

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.

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.

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).