10

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?

John Saunders
  • 160,644
  • 26
  • 247
  • 397
noobplusplus
  • 177
  • 1
  • 2
  • 12
  • sorry, i had to edit the question. it returned 0 result even though in @MYNAME i supplied the correct partial string – noobplusplus Apr 07 '10 at 00:43

6 Answers6

37

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.

Aaronaught
  • 120,909
  • 25
  • 266
  • 342
  • 1
    This won't work correctly when the MyName.Text contains the characters % _ or [, assuming you want to actually search for one of those charater literals. For a 100% solution, you need to wrap those characters in square brackets []. The C# code `Regex.Replace(searchString, @"([%_\[])", @"[$1]")` does the trick. Thanks to Matt Miller for pointing this out [here](http://stackoverflow.com/questions/665129/use-of-sqlparameter-in-sql-like-clause-not-working). – Dejan Apr 18 '15 at 13:49
2

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)
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • True as long as the text you're searching for is a full word (or words) or the beginning of a word. If you're searching for text in the middle of or at the end of a word, you unfortunately still need to use `LIKE %s%`. – Aaronaught Apr 07 '10 at 01:12
0

Don't think that using a bind parameter is like inserting it's value into the SQL string! Bind parameter values are sent as separate data to the DB, so they mustn't be in quotes, and neither can they contain any optional SQL code, table or column names!

Erik Hart
  • 1,114
  • 1
  • 13
  • 28
0

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.

bryanjonker
  • 3,386
  • 3
  • 24
  • 37
-1

you are missing the % sign while passing parameter value 

command.Parameters.AddWithValue("@MYNAME"+"%", MYNAME.Text);
iTSrAVIE
  • 846
  • 3
  • 12
  • 26
  • 1
    Not correct, the parameter name has no bearing on the LIKE pattern. That should be a applied to the parameter value. – user692942 Jun 18 '13 at 08:48
-15

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.

Ray
  • 21,485
  • 5
  • 48
  • 64