Getting query to work with parameter and "like"

Question

I've seen many questions regarding using parameters with Sql queries and "like," but I've tried every way I've seen to code it and still can't get my query to give results. If I put a value in the query itself, it runs fine. When I run the first query listed I get the error "Must declare the scalar variable "@Search" but I thought I did that with the cmd.Parameters.AddWithValue statement. Can anyone see what I might be doing wrong? Any help is appreciated.

            //Declare the connection object
        SqlConnection Conn = new SqlConnection();
        Conn.ConnectionString = ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString;

        //Connect to the db
        Conn.Open();

        //Define query

        //This query doesn't work
        string sql = "SELECT CustomerID, LastName, FirstName, Email, Password, Address1, Address2, City, State, Zip, Phone, Fax FROM Customer WHERE (State LIKE '%' + @Search + '%')";

        //This query doesn't work either
        string sql = "SELECT CustomerID, LastName, FirstName, Email, Password, Address1, Address2, City, State, Zip, Phone, Fax FROM Customer WHERE State LIKE @Search";

        //This query works
        string sql = "SELECT CustomerID, LastName, FirstName, Email, Password, Address1, Address2, City, State, Zip, Phone, Fax FROM Customer WHERE State LIKE 'MI'";

        //Declare the Command
        SqlCommand cmd = new SqlCommand(sql, Conn);

        //Add the parameters needed for the SQL query
        cmd.Parameters.AddWithValue("@Search", "%" + txtSearch.Text + "%");           

        //Declare a SQL Adapter
        SqlDataAdapter da = new SqlDataAdapter(sql, Conn);

        //Declare a DataTable
        DataTable dt = new DataTable();

        //Populate the DataTable
        da.Fill(dt);

        //Bind the Listview
        lv.DataSource = dt;
        lv.DataBind();

        dt.Dispose();
        da.Dispose();
        Conn.Close();

http://stackoverflow.com/questions/251276/howto-parameters-and-like-statement-sql — Robert Harvey, Dec 01 '12 at 20:22
What exception are you getting? Like what does the error message say? — Oli, Dec 01 '12 at 20:29

OneRealWinner · Accepted Answer · 2012-12-01T20:54:10.177

8

In your code above you aren't using the parameter in the SqlDataAdapter, in the code below you will use the SqlDataAdapter in the command.

    //This query doesn't work
    string sql = "SELECT CustomerID, LastName, FirstName, Email, Password, Address1, Address2, City, State, Zip, Phone, Fax FROM Customer WHERE (State LIKE @Search)";

    //Declare the Command
    SqlCommand cmd = new SqlCommand(sql, Conn);

    //Add the parameters needed for the SQL query
    cmd.Parameters.AddWithValue("@Search", "%" + txtSearch.Text + "%"); 

  //Declare a SQL Adapter
    SqlDataAdapter da = new SqlDataAdapter();

    **sa.SelectCommand = cmd**

If you would like to not use a parameterized query this will work :

 //Declare the connection object
    //This query doesn't work
    string sql = "SELECT CustomerID, LastName, FirstName, Email, Password, Address1, Address2, City, State, Zip, Phone, Fax FROM Customer WHERE (State LIKE '%" + **txtSearch.Text** + "%')";

  //Declare a SQL Adapter
    SqlDataAdapter da = new SqlDataAdapter(sql, conn);

edited Dec 01 '12 at 20:54

answered Dec 01 '12 at 20:22

OneRealWinner

657
3
10

Ok, I tried: `string sql = "SELECT CustomerID, LastName, FirstName, Email, Password, Address1, Address2, City, State, Zip, Phone, Fax FROM Customer WHERE (State LIKE '% + @Search + %')";` with `cmd.Parameters.AddWithValue("@Search", txtSearch.Text);` and still get no results. Let me ask this: Would it make a difference if I'm using an Update Panel? I have never used one before today and am just learning about it. However, the query where I put the value in works fine. – user1576304 Dec 01 '12 at 20:42
The update panel should make no difference. If you are using the approach with the command, add the wildcards to the query and change the select statement should look like : SELECT CustomerID, LastName, FirstName, Email, Password, Address1, Address2, City, State, Zip, Phone, Fax FROM Customer WHERE (State LIKE @Search). – OneRealWinner Dec 01 '12 at 20:45
Not to be a killjoy here, but the only difference I see between your code and the OP's is that you're using a different constructor overload for the `SqlCommand` object, where the original overload should still work anyway. – Robert Harvey Dec 01 '12 at 20:47
1

@Robert I'm not seeing what you are. The issue with OP's code is that the SqlDataAdapter is using a the sql string and not declaring the parameter. – OneRealWinner Dec 01 '12 at 20:51
It's very difficult to see what you mean with all that code. Any chance you could just include the lines that need to be changed in your code example? – Robert Harvey Dec 01 '12 at 20:52
1

Here is the major difference. SqlDataAdapter da = new SqlDataAdapter(); **sa.SelectCommand = cmd**. This makes the SQLDataAdapter use the cmd that has the parameter attached. – OneRealWinner Dec 01 '12 at 20:55
Yes, changing to use the SqlDataAdapter did the trick! I have to say I was getting a little confused there for a minute, but I think I see what you mean by not calling the correct cmd. Couldn't figure out why the query with the value in it worked, but the others didn't. Thank you. – user1576304 Dec 01 '12 at 21:07

Nikola Davidovic · Answer 2 · 2012-12-01T21:07:06.367

Your main problem is that you are not using the command that you have built because of this constructor

SqlDataAdapter da = new SqlDataAdapter(sql, Conn);

therefore you are also not using parameter and the only query that works is the one that doesn't use any (third one). You should use this constructor instead (the one that is created using SqlCommand)

SqlDataAdapter da = new SqlDataAdapter(cmd);

After you change the constructor you are using, either of the following queries will apply:

string sql = "SELECT CustomerID, LastName, FirstName, Email, Password, Address1, Address2, City, State, Zip, Phone, Fax FROM Customer WHERE State LIKE @Search";
...
cmd.Parameters.AddWithValue("@Search", "%" + txtSearch.Text + "%");

or this:

string sql = "SELECT CustomerID, LastName, FirstName, Email, Password, Address1, Address2, City, State, Zip, Phone, Fax FROM Customer WHERE State LIKE '%' + @Search + '%'";
...
cmd.Parameters.AddWithValue("@Search", txtSearch.Text);

Not using the + signs in the string is what got me – Jake Gaston Dec 08 '15 at 17:30 — Jake Gaston, Dec 08 '15 at 17:30

score 2 · Answer 3 · answered Dec 01 '12 at 20:33

2

string sql = "SELECT CustomerID, LastName, FirstName, Email, Password, Address1, Address2, City, State, Zip, Phone, Fax FROM Customer WHERE (State LIKE '%' + @Search + '%')";

cmd.Parameters.AddWithValue("@Search",txtSearch.Text);

this should work

answered Dec 01 '12 at 20:33

Goran Štuc

581
4
15

score 1 · Answer 4 · 2017-07-27T13:58:01.960

1

In addition to accepted answer, don't forget to replace your _, % with square brackets. Otherwise it will still give wrong results.

txtSearch.Text.Replace("_","[_]").Replace("%","[%]")

edited Jul 27 '17 at 13:58

answered Jul 27 '17 at 08:02

score 0 · Answer 5 · answered Dec 01 '12 at 20:42

Rather than use a SqlDataAdapter you can use a SqlDataReader

SqlDataReader myReader = cmd.ExecuteReader();

DataTable dt = new DataTable();
dt.Load(myReader);

You will notice in your code, that the paramater is attached to cmd which is not actually used and hence the SqlDataAdapter does not know about the parameter.

Getting query to work with parameter and "like"

5 Answers5

Linked