Add SqlParameter to bind LIKE '%@x%'

Question

I am having an issue getting the following code to correctly add the SqlCommand parameter @vendor. For some reason, the query being passed seems to always be:

select TOP 500 * 
from [mike_db].[dbo].[na_pe_sql_import] 
where vendname like '%@vendor%';

It works if I setup the query like this, but I know this is bad practice.:

string strQuery = "select TOP 500 * from [mike_db].[dbo].[na_pe_sql_import] where vendname like '%"+txt_search.Text.ToString()+"%';";

Here is the code:

    protected void Search_Click(object sender, EventArgs e)
    {   
        string search = txt_search.Text.ToString();
        String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["mike_db"].ConnectionString;

        SqlConnection con = new SqlConnection(strConnString);
        con.Open();

        string strQuery = "select TOP 500 * from [mike_db].[dbo].[na_pe_sql_import] where vendname like '%@vendor%';";

        cmd = new SqlCommand(strQuery, con);
        cmd.Parameters.AddWithValue("vendor", search);

        txt_search.Text = string.Empty;

        DataSet ds = new DataSet();

        da = new SqlDataAdapter(cmd);
        da.Fill(ds);

        My_Repeater.DataSource = ds;
        My_Repeater.DataBind();

        con.Close();            
    }

Answer 1

I think @vendor is being treated as a literal in your query instead of a parameter.

Try defining your query as follows:

string strQuery =
   "select TOP 500 * from [mike_db].[dbo].[na_pe_sql_import] where vendname like '%' + @vendor + '%'";

Then add the parameter like this:

cmd.Parameters.AddWithValue("@vendor", search);