Click here to download the source code for this lesson.
The SqlDataSource server control can make searching your database easy without writing any C# code. For example, if you are showing the results of a query on a web page and you want the user to be able to search based on what the user types in a TextBox control. You can link the TextBox control and other input controls to your SqlDataSource query using parameters. In this example, on my web page, I want the user to be able to search the Customer database table based on either a last name or an e-mail address so I created two TextBox controls accordingly.
First, you should have established a binding between the GridView control and an SqlDataSource control on your web form. Refer to the prior lesson Retrieving database data with the ASP.NET SqlDataSource and GridView controls to learn how to do that if you haven’t already.
Next, modify the SelectQuery property of the SqlDataSource control. You have to add the “where” clause to the Select statement so that the database can search based on information that was typed in by the user. You can accomplish this by putting named parameters in your select statement “where” clause like this: “where last_name = @LastName OR email_address = @Email”. In this example I searched two different columns last_name and email_address. The at(@) symbol variables are the parameters: I have two parameters nameed LastName and Email.
When the query runs it will replace the @LastName and @Email with values from my web page TextBox controls. In order to connect those parameters to my TextBox controls I have to click the “Add Parameter” button in the SelectQuery editor popup window and add two parameters. The names of these two parameters have to match the names that I used in the “where” clause: LastName and Email. For each parameter that I add, I have to set the “Parameter source” property to “Control” and the “ControlID” property to the ID of the corresponding TextBox control on the web form (e.g. txtLastName). I also set the “DefaultValue” property for each parameter to “nullvalue” in case the user doesn’t type anything in. I put “nullvalue” because I want to force the user to type something in order for the query to work. You can put an actual default value for your scenario if one applies or you can use a bogus value like I did and that way the user has to enter something in order for the query to work.
