Problems Passing Parameters into SqlCommand

Question

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

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
– rory.ap, Commented Nov 19, 2016 at 0:36
This kind of problem is why you really should avoid using AddWithValue — Scott Chamberlain
– Scott Chamberlain, Commented Nov 19, 2016 at 2:15
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
– Damith, Commented Nov 19, 2016 at 8:00

rory.ap · Accepted Answer · 2016-11-19 00:39:01Z

3

Try using

 Command.Parameters.Add("@folder",SqlDbType.Varchar).Value = folder;

edited Nov 19, 2016 at 0:39

rory.ap

35.7k15 gold badges104 silver badges199 bronze badges

answered Nov 19, 2016 at 0:36

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Denis Voituron · Accepted Answer · 2016-11-19 12:31:28Z

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

marc_s · Accepted Answer · 2016-11-19 11:07:13Z

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

edited Nov 19, 2016 at 11:07

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Nov 19, 2016 at 3:20

Yanga

3,0121 gold badge33 silver badges32 bronze badges

1 Comment

Yanga Over a year ago

Where did you see it was deprecated ? No mention here : msdn.microsoft.com/en-us/library/0881fz2y(v=vs.110).aspx

Collectives™ on Stack Overflow

Problems Passing Parameters into SqlCommand

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related