how to get LIKE clause to work in ADO.NET and SQL Server

Question

I am doing a really simple query in ASP.NET, but after I inserted the LIKE clause it stops working.

Example:

String sql = " SELECT * 
                 FROM Products 
                WHERE ID = @MYID 
                  AND Name LIKE '%@MYNAME%' ";
SqlCommand command = new SqlCommand(sql, cn);


command.Parameters.AddWithValue("@MYID", MYID.Text);

command.Parameters.AddWithValue("@MYNAME", MYNAME.Text);

If I removed the LIKE it works. Hence I am thinking its to do with the '' quotes?

Answer 1

The sql statement should look like this:

String sql = " SELECT * FROM Products WHERE ID = @MYID AND Name LIKE '%' + @MYNAME + '%'"; 

I am not sure I understood your comment completely, but it seems you want to use the value from a text box in your query - maybe this is what you are trying to do:

String sql = " SELECT * FROM Products WHERE ID = @MYID AND Name LIKE '%' + text_box.Text + '%'";

"text_box" would be the actual id of your textBox control.

Answer 2

Or

 String sql = " SELECT * FROM Products WHERE ID = @MYID AND Name LIKE @MYNAME ";

and when you set the @MYNAME parameter, add the "%" characters appropriately (%SMITH%). I don't think you need the single quotes when you're dealing with parameters.

Answer 3

Just a note to say that using LIKE with an initial wildcard is almost always a very bad idea, because the query won't use any indexes on that column. In this case you can probably get away with because it looks like the filter on the ID column will limit you to one record, but generally what you need to do instead is put a full-text index on the name column and write the query like this:

... WHERE CONTAINS(name, @MyName)

Answer 4

The original code is confusing the text of the SQL statement with the content of the parameter. Your code should actually look like this:

string sql = "SELECT * 
              FROM Products 
              WHERE ID = @MyID
              AND Name LIKE @MyName";
using (SqlCommand command = new SqlCommand(sql, cn))
{
    command.Parameters.AddWithValue("@MyID", MyID.Text);
    command.Parameters.AddWithValue("@MyName", "%" + MyName.Text + "%");
    // Etc.
}

The % signs need to be part of the parameter value, and you don't need the single quotes at all when using binding parameters.