SqlParameter : Insert a row with a null float value

Question

I have a list of float? objects.

List<float?> myList = new List<float?>{1,null,3};

I'm trying to insert this objects in a table.

using (SqlCommand oCommand = myConnection.CreateCommand())
{
    oCommand.CommandType = CommandType.Text;
    oCommand.CommandText = "INSERT INTO myTable (col1) values(@col1)";

    foreach (var oData in myList)
    {
        oCommand.Parameters.Clear();
        oCommand.Parameters.Add((oData == null) ? new SqlParameter("@col1", DBNull.Value) : new SqlParameter("@col1", SqlDbType.Float));
        oCommand.Parameters[oCommand.Parameters.Count - 1].Value = oData;

        if (oCommand.ExecuteNonQuery() != 1)
        {
            throw new InvalidProgramException();
        }
    }
    myConnection.Close();
}

When I'm running this code I have an exception which said that the parameter @col1 is expected but it was not supplied.

Create the parameter first, *then* set its value. Don't try to access the parameter you just created by index either, [Add](https://msdn.microsoft.com/en-us/library/ht4eset1(v=vs.110).aspx) returns the new SqlParameter object. Store it in a variable and set its value — Panagiotis Kanavos, Aug 01 '18 at 15:39

score 3 · Accepted Answer · answered Aug 01 '18 at 15:43

3

oCommand.Parameters.Add(new SqlParameter("@col1", SqlDbType.Float) { Value = oData == null ? DBNull.Value : (object) oData.Value });`

Remove the line under it which resets the Value property you had set earlier.

answered Aug 01 '18 at 15:43

Igor

60,821
10
100
175

score 2 · Answer 2 · answered Aug 01 '18 at 15:43

2

Try like this

   if(oData.HasValue)
     oCommand.Parameters.AddWithValue("@col1", oData.Value);
   else
     oCommand.Parameters.AddWithValue("@col1", DBNull.Value);

answered Aug 01 '18 at 15:43

Coskun Ozogul

2,389
1
20
32

SqlParameter : Insert a row with a null float value

2 Answers2