Unable to pass NULL values to a parameter while inserting a record to a table in C#

Question

I am trying to build some query and insert a list with 7 columns into a SQL table using C#. In my list I have few NULL values for few columns and I am not able to pass them to the following query

string strInsertListToTable = @"INSERT INTO ImpliedOutrightData (id,product,term,bid,offer,bidcp,offercp) VALUES(@id,@product,@term,@bid,@offer,@bidcp,@offercp)";
    for (int i = 0; i < resultList.Count; i++)
        {  


           SqlCommand cmdInsertList = new SqlCommand(strInsertListToTable, sqlcon);

            cmdInsertList.CommandType = CommandType.Text;
            cmdInsertList.Parameters.Clear();
            cmdInsertList.Parameters.AddWithValue("@id", resultList[i].id);
            cmdInsertList.Parameters.AddWithValue("@product", resultList[i].product);
            cmdInsertList.Parameters.AddWithValue("@term", resultList[i].term);
            cmdInsertList.Parameters.AddWithValue("@bid", resultList[i].bid);
            cmdInsertList.Parameters.AddWithValue("@offer", resultList[i].offer);

            cmdInsertList.Parameters.AddWithValue("@bidcp",resultList[i].bidcp);
            cmdInsertList.Parameters.AddWithValue("@offercp", resultList[i].offercp);
            cmdInsertList.ExecuteNonQuery();
        }

While the above query loops I get the error

The parameterized query '(@id int,@product nvarchar(2),@term nvarchar(5),@bid float,@bidc' expects the parameter '@offercp', which was not supplied.

Did you try changing the `null` values in the list to `DBNull.Value`? — Fixation, Aug 15 '14 at 14:46
Don't use `AddWithValue` but `Add`. Also, use `DBNull.Value` instead of `null`. — Tim Schmelter, Aug 15 '14 at 14:47

score 3 · Accepted Answer · answered Aug 15 '14 at 14:46

When the value of the parameter is null, you should set the corresponding value to DbNull.Value:

cmdInsertList.Parameters.AddWithValue(
    "@offercp"
,   resultList[i].offercp == null ? (object)DbNull.Value : resultList[i].offercp
);

Note the cast to object - you need this so that both sides of the conditional evaluate to the same type.

score 3 · Answer 2 · answered Aug 15 '14 at 14:46

You need to use DBNull.Value. I wrote this extension method:

public static SqlParameter AddNullSafe(this SqlParameterCollection parameters, SqlParameter value)
{
    if (value != null)
    {
        if (value.Value == null)
        {
            value.Value = DBNull.Value;
        }
        return parameters.Add(value);
    }
    return null;
}

score 2 · Answer 3 · answered Aug 15 '14 at 14:46

2

If you suspect a value could be null you can use DBNull to indicate the null value for the parameter:

cmdInsertList.Parameters.AddWithValue("@offercp",
    resultList[i].offercp ?? DBNull.Value);

answered Aug 15 '14 at 14:46

Kelsey

47,246
16
124
162

Unable to pass NULL values to a parameter while inserting a record to a table in C#

3 Answers3