Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Add SqlParameter to bind LIKE '%@x%' [duplicate]

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();            
    }
like image 612
66Mhz Avatar asked Aug 12 '14 01:08

66Mhz


People also ask

What is SqlParameter in C#?

C# SqlParameter is a handy feature allows you to safely pass a parameter to a SqlCommand object in . NET. A security best practice when writing . NET data access code, is to always use parameters in SqlCommand objects (whenever parameters are required of course).

Why we use CMD parameters AddWithValue in C#?

Use AddWithValue whenever you want to add a parameter by specifying its name and value. For SqlDbType Xml enumeration values, you can use a string, an XML value, an XmlReader derived type instance, or a SqlXml object.

Which property of the SQL parameter class should you set to retrieve the output value?

For output and return value parameters, the value is set on completion of the SqlCommand and after the SqlDataReader is closed. This property can be set to null or DBNull. Value. Use DBNull.

What is SQL parameter?

Parameters are used to exchange data between stored procedures and functions and the application or tool that called the stored procedure or function: Input parameters allow the caller to pass a data value to the stored procedure or function.


1 Answers

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);
like image 135
Grant Winney Avatar answered Oct 03 '22 00:10

Grant Winney