Add SqlParameter to bind LIKE '% @ x%'

Question

I had a problem getting the following code to correctly add the SqlCommand @vendor parameter. For some reason, the transmitted request always looks like this:

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

It works if I set up such a query, 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 that @vendor treated as a literal in your request instead of 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 as follows:

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