Sending null parameters to Sql Server

Question

I have a SqlCommand object that I'm using to update a database table but it doesn't interpret my null values correctly.

Here is the SQL:

UPDATE dbo.tbl 
SET param1 = @param1, 
param2 = @param2,
param3 = @param3,
param4 = @param4, 
param5 = @param5, 
param6 = @param6, 
param7 = @param7, 
param8 = @param8, 
param9 = @param9, 
param10 = @param10 
WHERE param11 = @param11

I have tried null coalescing parameters that are nullable like this, but I haven't had any success. Unless otherwise noted, all parameters are strings:

command.Parameters.AddWithValue("@param1", param1 ?? DBNull.Value);
command.Parameters.AddWithValue("@param2", param2 ?? DBNull.Value);
command.Parameters.AddWithValue("@param3", param3 ?? DBNull.Value);
command.Parameters.AddWithValue("@param4", param4 ?? DBNull.Value);
command.Parameters.AddWithValue("@param5", param5 ?? DBNull.Value);
// param6 is nullable type DateTime?
command.Parameters.AddWithValue("@param6", param6 ?? DBNull.Value); 
command.Parameters.AddWithValue("@param7", param7 ?? DBNull.Value);
// param8 is nullable type DateTime?
command.Parameters.AddWithValue("@param8", param8 ?? DBNull.Value); 
command.Parameters.AddWithValue("@param9", param9 ?? DBNull.Value);
// param10 nullable type float?
command.Parameters.AddWithValue("@param10", param10 ?? DBNull.Value); 
command.Parameters.AddWithValue("@param11", param11 ?? DBNull.Value);

I get an exception like this:

The parameterized query '(@param1 nvarchar(4000),@param2 nvarchar(4000),@param3 nvarc' expects the parameter '@param4', which was not supplied.

I've also tried looping through each parameter after they've been added to the SqlCommand object to set DbNull.Value if the parameter value is null like this:

foreach (SqlParameter parameter in command.Parameters)
{
    if (parameter.Value == null)
    {
        parameter.Value = DBNull.Value;
    }
}

However, this approach is causing the exception:

String or binary data would be truncated.
The statement has been terminated.

What is the best practice for passing null parameters to a SqlCommand? I don't simply want to pass in default values if they're null since the database schema allows null values.

What type is `param`? You know that `AddWithValue` has to infer the type from the object passed? Since you get no compile time error is assume that `param` is object which is not a good idea. — Tim Schmelter, Sep 23 '13 at 15:31
DBNull is actually the best practice (only way) to pass NULL for a parameter. Are you sure the string truncate error is not caused by something else? Passing NULL shoudln't cause this error... Maybe try to monitor the statement which gets excecuted on your DB and try to identify the "real" error — MichaC, Sep 23 '13 at 15:33
I have 11 parameters total with each being a string, DateTime, or a float. I used the generic name param to disguise business logic. — Cameron Tinker, Sep 23 '13 at 15:33
I will update the question shortly. For some reason, my edit won't post. — Cameron Tinker, Sep 23 '13 at 15:48
I don't fully understand why I couldn't post the SQL as a concatenated string, but I've updated my question to show the full SQL. Names adjusted for not disclosing business logic. — Cameron Tinker, Sep 23 '13 at 17:12
I stepped through each of my parameters and found that one of them was too large to fit into one of the columns which was causing the `String or binary data would be truncated. The statement has been terminated.` exception. Thanks everyone for helping guide me in the right direction. — Cameron Tinker, Sep 24 '13 at 16:20
Try this : command.Parameters.AddWithValue("@param1", param1 ?? Convert.DBNull); — Gaurav123, Feb 11 '14 at 12:26

score 27 · Accepted Answer · answered Feb 11 '14 at 12:26

27

Try this :

command.Parameters.AddWithValue("@param1", param1 ?? Convert.DBNull);

answered Feb 11 '14 at 12:26

Gaurav123

5,059
6
51
81

3

What is the fix here? `Convert.DBNull` is equal to `DBNull.Value` – Nick N. Aug 01 '14 at 11:30

score 5 · Answer 2 · answered Sep 23 '13 at 15:36

5

hi try using the following synatx:

command.parameters.add("@ParameterName",DBNull.value);

hope this helps

answered Sep 23 '13 at 15:36

Sai Avinash

4,683
17
58
96

It would actually be `command.Parameters.Add("@ParameterName", DBNull.Value)` and the `SqlCommand.Parameters.Add` method has been deprecated. I'm null coalescing because sometimes the value is not null and I don't want to always pass null. – Cameron Tinker Sep 23 '13 at 15:40
have you declared param as nullable? – Sai Avinash Sep 23 '13 at 15:41
Most of my parameters are strings and therefore nullable, but the parameters that are floats and DateTimes have been explicitly defined as nullable. – Cameron Tinker Sep 23 '13 at 17:13

score 2 · Answer 3 · edited Feb 11 '14 at 12:49

2

commandObject.Parameters.AddWithValue("@parameterName",Convert.DBNull);

edited Feb 11 '14 at 12:49

RononDex

4,143
22
39

answered Feb 11 '14 at 12:28

Revan

1,104
12
27

Sending null parameters to Sql Server

3 Answers3

Linked