Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
352 views
in Technique[技术] by (71.8m points)

c# - 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 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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...