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.

Spilt

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. – Fridell

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 – Marmite

I have 11 parameters total with each being a string, DateTime, or a float. I used the generic name param to disguise business logic. – Spilt

Could you show us the complete SQL? – Collusive

I will update the question shortly. For some reason, my edit won't post. – Spilt

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. – Spilt

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. – Spilt

Try this : command.Parameters.AddWithValue("@param1", param1 ?? Convert.DBNull); – Loyceloyd

Answer 1

Try this :

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

Loyceloyd

What is the fix here? Convert.DBNull is equal to DBNull.Value – Abscission

Answer 2

hi try using the following synatx:

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

hope this helps

Penman

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. – Spilt

have you declared param as nullable? – Penman

Most of my parameters are strings and therefore nullable, but the parameters that are floats and DateTimes have been explicitly defined as nullable. – Spilt

Answer 3

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

Harewood