Getting query to work with parameter and "like"

asp.net, c#, sql

Solution

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);

Problem

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(); ```

Original source

Related problems