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();
}
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).
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.
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.
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.
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);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With