filter gridview using SQL "LIKE" condition

Question

User154499744 posted

I have a gridview and it uses a sqldatasource that has this.

                <asp:SqlDataSource ID="sqlds" runat="server" ConnectionString="<%$ ConnectionStrings:someDB %>"
                    SelectCommand="SELECT * FROM [sometable] WHERE [somecolumn] = @filtervalue">
                    <SelectParameters>
                        <asp:ControlParameter ControlID="textbox" Name="filtervalue" PropertyName="Text" Type="String"/>
                    </SelectParameters>
                </asp:SqlDataSource>

And I have a button with OnClick that runs gridview.Databind() to refresh the gridview with the filtered data from the textbox.

How can I change this so the select statement does a LIKE condition? somecolum LIKE '%@filtervalue%'. I am not sure how I need to edit the SelectCommand property of my sqldatasource.

Answer 1

User753101303 posted

Hi,

I would use LIKE @FilterValue and I would include myself the wildcard characters as part of the parameter. The point is that you can use a parameter where a literal value is expected (it doesn't work by just doing a "search/replace" and AFAIK you can't use a parameter inside a string (this IS a string).

Answer 2

User154499744 posted

Hi,

I would use LIKE @FilterValue and I would include myself the wildcard characters as part of the parameter. The point is that you can use a parameter where a literal value is expected (it doesn't work by just doing a "search/replace" and AFAIK you can't use a parameter inside a string (this IS a string).

Not sure what you are saying, but I was looking for what I need to type out for SelectCommand. After some trial and error, this is what I needed.

SelectCommand="SELECT * FROM [sometable] WHERE [somecolumn] LIKE '%' + @filtervalue" + '%'

Answer 3

User753101303 posted

Yes or LIKE @filtervalue and once the user entered "myword" you'll add yourself the leading and trailing % so that the value for the @filtervalue parameter is %myword%.

The point is that a parameter name can be used where a literal is expected (so not Inside a string).