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

Using Wildcards in a WHERE Filter Expression

In previous hours we saw how to use the SqlDataSource's wizard to add WHERE filter expressions to limit the results returned by the database. In Hour 17, "Working with Data-Bound DropDownLists, RadioButtons, and CheckBoxes," we saw how to use a DropDownList's selected value as the value for the filter expression, thereby allowing the user to display only those books that belonged to a particular genre.

When we're creating a filter expression through the SqlDataSource control's wizard, recall that we must specify three things:

• The database column the filter expression applies to

• The filter expression operator=, <>, <, <=, >, and so on

• The filter value, which can be a hard-coded value or based on some external value, such as the value of a Web control on the page

One of the operators that we've yet to look at is the LIKE operator. The LIKE operator uses wildcards around the parameter value and works only with string or date/time database columns. We can use the LIKE operator to build an interface for the user to search the Books table, returning all rows where the Title column value contains some user-entered search term.

Let's create a page to demonstrate this filter expression operator. Before adding a SqlDataSource control to this page, first enter the text Search for books by title:, followed by TextBox and Button Web controls. Set the TextBox Web control's ID property to titleSearch and the Button Web control's ID and Text properties to btnSearch and Search, respectively. The user interface we've just added will allow the user visiting our page to enter a search term into the text box. After you do so and click the Search button, the page will post back and the GridView will display those books with matching titles.

Now that we have the user interface implemented, the next step is to add the SqlDataSource and GridView controls. Configure the SqlDataSource control to return all columns from the Books table. Add a WHERE clause filter expression on the Title column using the LIKE operator based on the titleSearch control (see Figure 18.10).

After you add this filter expression and return to the wizard's Configure the Select Statement screen, the wizard's proposed SELECT statement should look like SELECT * FROM [Books] WHERE ([Title] LIKE '%' + @Title + '%'). Note that the WHERE clause uses the LIKE operator and the wildcard characters (%) to return all records whose Title column value contains the value of the @Title parameter. This @Title parameter value will be set to the value in the titleSearch TextBox control.

Complete this page by adding the GridView control and binding it to the SqlDataSource. When the page is first visited through a browser, no records will be shown because the user hasn't entered a value into the text box. Similarly, if the user enters a search term that does not appear in any of the books' titles, no books will be returned by the SqlDataSource. These two scenarios may confuse the end user because he may be wondering why no books are being shown. To help alleviate any confusion, enter a helpful message into the GridView's EmptyDataText property. As we've discussed before, this value of the EmptyDataText property is displayed when no records are returned by the GridView's associated data source control. Also feel free to tailor the GridView's columns as you see fit; I used the column configuration from our HyperLinkField discussion earlier in this hour.

After setting the EmptyDataText property, take a moment to test this page in a browser. Figure 18.11 shows the page the user searches on the term "the". This produces two results: The Number and The Catcher in the Rye. When a user enters a search term that is not in any title, such as "Rolla", no results will be returned by the SqlDataSource control, and the GridView will display the value of its EmptyDataText property (see Figure 18.12).