Append to a Select Command

Question

I have a Gridview that I want to filter. My gridview is in an update panel and my filter button is not. On button click i have the following

protected void bttnfilter_Click(object sender, ImageClickEventArgs e)
{
    if (TextBox1.Text != "")
    {
        SqlDataSource1.SelectCommand += " and field like '%' + @param + '%'";
        SqlConnection conn = new SqlConnection(SqlDataSource1.ConnectionString );
        SqlCommand cmd = new SqlCommand(SqlDataSource1.SelectCommand, conn);
        cmd.Parameters.Add(new SqlParameter("@param", TextBox1.Text));
    }

}

So i add to the select command but I'm getting an error. How should I go about this? I don't want to use dynamic sql.

The Error Reads Incorrect Syntax near 'and'

Answer 1

Most probably you are appending to a query without WHERE clause:

SELECT  *
FROM    mytable
        AND field LIKE '%test%'

will give you this error.

Make you query stub as follows:

SELECT  *
FROM    mytable
WHERE   1 = 1

, which will select everything unless you append additional conditions to it.

In your case, this will give:

SELECT  *
FROM    mytable
WHERE   1 = 1
        AND field LIKE '%test%'

, which is a valid query.

I don't want to use dynamic sql.

You are using it.

You better rewrite your query as this:

SELECT  *
FROM    mytable
WHERE   @param IS NULL
UNION ALL
SELECT  *
FROM    mytable
WHERE   field LIKE '%' + @param + '%'

and bind NULL if your text field is empty.

Answer 2

Possibly either the lack of a where clause, or SelectCommand is referencing a stored procedure.

Answer 3

Are you sure that the last clause in your SQL statement is a WHERE clause? If there is no WHERE clause or if there's another clause after the WHERE (GROUP BY, ORDER BY) then you'll get this error.

Answer 4

Something like this should do it

private void bttnfilter_Click(object sender, ImageClickEventArgs e)
{
    string filterText = TextBox1.Text.Trim().ToLower();

    if (!String.IsNullOrEmpty(filterText))
        SqlDataSource1.FilterExpression = 
            string.Format("field LIKE '%{0}%'",filterText);         
}