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

I actually tried @vendor first and couldn't get it to work. This example is what made me think to try without the @ - http://www.nullskull.com/faq/979/different-ways-of-passingadding-sqlparameter-to-sqlcommand-parameter-property-in-adonet-c.aspx — 66Mhz, Aug 12 '14 at 01:51
Related: http://stackoverflow.com/questions/6581525/is-this-sql-injection-proof-asp-net-code?rq=1 , http://stackoverflow.com/questions/665129/resolve-use-of-sqlparameter-in-sql-like-clause-not-working — user2864740, Aug 12 '14 at 01:52

score 14 · Accepted Answer · answered Aug 12 '14 at 01:50

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

Add SqlParameter to bind LIKE '%@x%'

1 Answers1