Set a SqlParameter to null.

Problem

“No implicit conversion from DBnull to int,” says the following code.

SqlParameter[] parameters = new SqlParameter[1];    
SqlParameter planIndexParameter = new SqlParameter("@AgeIndex", SqlDbType.Int);
planIndexParameter.Value = (AgeItem.AgeIndex== null) ? DBNull.Value : AgeItem.AgeIndex;
parameters[0] = planIndexParameter;

Asked by Relativity

Solution #1

The issue is that the?: operator can’t figure out what type you’re returning because you’re either returning an int or a DBNull value, which aren’t compatible.

Of course, you can cast the instance of AgeIndex to a type object to meet the?: criterion.

The?? null-coalescing operator can be used as follows:

SqlParameter[] parameters = new SqlParameter[1];     
SqlParameter planIndexParameter = new SqlParameter("@AgeIndex", SqlDbType.Int);
planIndexParameter.Value = (object)AgeItem.AgeIndex ?? DBNull.Value;
parameters[0] = planIndexParameter; 

Here’s a quotation from the?: operator’s MSDN documentation that illustrates the issue.

Answered by Chris Taylor

Solution #2

Using a cast, according to the acceptable answer. Most SQL types, however, contain a particular Null field that can be used to prevent the cast.

SqlInt32, for example. “Represents a DBNull that can be allocated to this instance of the SqlInt32 class,” says the documentation.

int? example = null;
object exampleCast = (object) example ?? DBNull.Value;
object exampleNoCast = example ?? SqlInt32.Null;

Answered by Brian

Solution #3

Unless a default value is supplied within the stored procedure, you must pass DBNull.Value as a null parameter within SQLCommand (if you are using stored procedure). For any missing argument, the ideal way is to assign DBNull.Value before query execution, and foreach will take care of the rest.

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

Change the following line if necessary:

planIndexParameter.Value = (AgeItem.AgeIndex== null) ? DBNull.Value : AgeItem.AgeIndex;

As follows:

if (AgeItem.AgeIndex== null)
    planIndexParameter.Value = DBNull.Value;
else
    planIndexParameter.Value = AgeItem.AgeIndex;

Because DBNull and int are distinct, you can’t use different types of values in a conditional expression. I hope this has been of assistance.

Answered by ShahidAzim

Solution #4

Here’s an example of what you can do with just one line of code:

var piParameter = new SqlParameter("@AgeIndex", AgeItem.AgeIndex ?? (object)DBNull.Value);

Answered by Adrian

Solution #5

Try this:

SqlParameter[] parameters = new SqlParameter[1];    
SqlParameter planIndexParameter = new SqlParameter("@AgeIndex", SqlDbType.Int);

planIndexParameter.IsNullable = true; // Add this line

planIndexParameter.Value = (AgeItem.AgeIndex== null) ? DBNull.Value : AgeItem.AgeIndex== ;
parameters[0] = planIndexParameter;

Answered by decyclone

Post is based on https://stackoverflow.com/questions/4555935/assign-null-to-a-sqlparameter