CODEWITHSUNDEEP
c#sqlsql-servert-sqlsqlcommand
UltraJ
UltraJ

Reputation: 487

Problems Passing Parameters into SqlCommand

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

Upvotes: 4

Views: 1681

Answers (3)

Denis Voituron
Denis Voituron

Reputation: 298

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

Upvotes: 1

Yanga
Yanga

Reputation: 3012

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

Upvotes: 0

Hadi
Hadi

Reputation: 37358

Try using

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

Upvotes: 3

Related Questions