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 named 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.
Ted, firstly excellent articles well done!
A quick query for you regarding the above content - I have a dropdownlist, a text box, and a gridview. The dropdown list contains the gridviews column names. The user selects a column name they wish to filter in the dropdownlist and then as they type in the textbox the gridview’s content should filter down accordingly. The issue I am having is that it doesnt seem I can use a paramter for both column name and its value:
Is there anyway I can enable filtering of a gridview based on a particular column by data entered into a textbox?
Comment by Sal — December 22, 2009 @ 9:15 am
Ted, my code seems to have been omitted from my above post, have redone below but without the closing/enclosing brackets (in case they are the cause).
aspDatasource SelectCommand= Select * FROM Employee WHERE @ColVal LIKE @filterVal
Parameters
aspControlParamter Name=ColVal ControlID=ddlColSelect
aspParameter Name=filterVal ControlID=txtFilter
Parameters
Comment by Sal — December 22, 2009 @ 9:22 am
Sal, to do something like this, I suggest that you put some code in your code behind. Probably the best place to put it is in the button click event. Try this code to dynamically change the name of the column you are searching on:
CSharpDataSource.SelectCommand = string.format(”Select * FROM Employee WHERE {0} LIKE @filterVal”,ddlColSelect.SelectedValue);
Comment by ted — December 28, 2009 @ 10:50 am
Cheers Ted, will give that a go.
Thanks!
Comment by Sal — December 28, 2009 @ 4:57 pm