4

I'm having problems passing parameters to a SQL string for a SqlCommand. When I use option 1 (see below), the code works. When I use option 2, it doesn't work. I'm not sure how to get the .AddWithValue method to work with the SqlCommand.

Any help would be appreciated!

private string [] GetOrderInfo (string folder)
{
    string [] order = new string [] { "date", "order#", "storeid", "storename", "username" };

    using (SqlConnection conn = new SqlConnection (_connectionString))
    {
        conn.Open ();

        // Option 1: this line works.
        //string sql = "select * from OrderProduct where OrderProductID=26846";

        // Option 2: this line doesn't work.
        string sql = "select * from OrderProduct where OrderProductID=@folder;";

        using (SqlCommand command = new SqlCommand (sql, conn))
        {
            command.Parameters.AddWithValue ("@folder", folder);

            using (SqlDataReader reader = command.ExecuteReader ())
            {
                while (reader.Read ())
                    order [1] = Convert.ToString (reader.GetInt32 (1));
            }
        }

        conn.Close ();
    } // using (SqlConnection conn = new SqlConnection (connectionString))

    return order;
}
Hadi
  • 36,233
  • 13
  • 65
  • 124
UltraJ
  • 467
  • 1
  • 10
  • 20
  • In "option 1" you're assigning something that actually looks like a product ID: 26846. In "option 2", you're assigning it a string called `folder`. That doesn't seem to make sense... – rory.ap Nov 19 '16 at 00:36
  • What does "it doesn't work" mean exactly. – JBrooks Nov 19 '16 at 00:38
  • The folder and OrderProductID are the same thing. OrderProductID is the field name in the database table, and it's also the name of the folder that is created for the order. I didn't set it up this way, it was done by company that built that app. – UltraJ Nov 19 '16 at 00:39
  • 1
    Try H. Fadlallah's answer. – rory.ap Nov 19 '16 at 00:40
  • Error is: SQL Exception unhandled: An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll Additional information: Conversion failed when converting the varchar value '@folder' to data type int. – UltraJ Nov 19 '16 at 00:40
  • `private string [] GetOrderInfo (int folder)` – LarsTech Nov 19 '16 at 00:41
  • 2
    This kind of problem is why [you really should avoid using AddWithValue](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) – Scott Chamberlain Nov 19 '16 at 02:15
  • 1
    `command.Parameters.AddWithValue ("@folder", int.Parse(folder));` will work, you need to pass the parameter value with correct type which database column type defined – Damith Nov 19 '16 at 08:00

3 Answers3

3

Try using

 Command.Parameters.Add("@folder",SqlDbType.Varchar).Value = folder; 
rory.ap
  • 34,009
  • 10
  • 83
  • 174
Hadi
  • 36,233
  • 13
  • 65
  • 124
1

AddWithValue method uses the type of value to define the correct SqlDbType. So, if your field OrderProductID is type of INT, you need to add an int.

Sample:

command.Parameters.AddWithValue ("@folder", 26846);

Another easy way is to use a Simple Object Mapper like SqlDatabaseCommand or Dapper.

using (var cmd = new SqlDatabaseCommand(_connection))
{
    cmd.CommandText.AppendLine(" SELECT * ")
                   .AppendLine("   FROM EMP ")
                   .AppendLine("  WHERE EMPNO = @EmpNo ")
                   .AppendLine("    AND HIREDATE = @HireDate ");

    cmd.Parameters.AddValues(new
            {
                EmpNo = 7369,
                HireDate = new DateTime(1980, 12, 17)
            });

    var emps = cmd.ExecuteTable<Employee>();
}
Denis Voituron
  • 288
  • 1
  • 8
0

You can try with:

using (SqlCommand command = new SqlCommand("select * from OrderProduct where OrderProductID=@folder", conn))
{
    command.Parameters.Add(new SqlParameter("@folder", folder));

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
            order[1] = Convert.ToString(reader.GetInt32(1));
    }
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Yanga
  • 2,885
  • 1
  • 29
  • 32
  • Where did you see it was deprecated ? No mention here : https://msdn.microsoft.com/en-us/library/0881fz2y(v=vs.110).aspx – Yanga Nov 19 '16 at 11:05