ASP.NET SqlDataSource, like on SelectCommand

Question

I'm working on asp.net. I Have a SqlDataSource with a query hardcoded on selectcommand:

<asp:SqlDataSource ID="DataSource1" runat="server" CancelSelectOnNullParameter="False"
    ConnectionString="<%$ ConnectionStrings:S.Properties.Settings.ConnectionString %>" 
    SelectCommand="SELECT * FROM [table]
    WHERE ([col1] like Case @col1_param When null Then col1 Else @col1_param End)
    and  ([col2] like Case @col2_param When null Then col2 Else @col2_param End)"
    SelectCommandType="Text">
    <SelectParameters>
        <asp:ControlParameter ControlID="TextBox1" Name="col1_param" PropertyName="Text"
            Type="String" />
        <asp:ControlParameter ControlID="TextBox2" Name="col2_param" PropertyName="Text"
            Type="String" />
    </SelectParameters>

What I want is that if you enter data on one textbox only, the data will display according with that textbox value only on the where clause. And if no values are placed for neither of the textboxes, the the query executes as if there is no where.

Right now with this code,what happens is if you put on one textbox only no data is displayed. The same if all textboxes are empty.

I don't want to use sql stored procedure.

How can I solve this?

Thanks...

Answer 1

Assuming it passes null when there is no text entered, otherwise you will need to check for the empty string

SelectCommand="SELECT * FROM [table]
    WHERE ([col1] like '%@col1_param%' or @col1_param is null)
    and  ([col2] like '%@col2_param%' or @col2_param is null)"

Answer 2

It sounds like you want your query to optionally search a column.

You can use the format

WHERE @col1_param IS NULL OR [col1] LIKE '%@col1_param%'

to property handle the case where the parameter is not specified.

See my question on the issue for a full answer. Granted it was done as a stored procedure, but the concept will hold the same for your SQLDataSource.