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

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 SqlC开发者_JS百科ommand(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?

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.

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)

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.

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!

you are missing the % sign while passing parameter value

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

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.