title: Getting query to work with parameter and "like"
body: <p>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.</p>
  
  <pre><code>            //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();
  </code></pre>