Assign Null to a SQLparameter

Assign null to a SqlParameter

The problem is that the ?: operator cannot determine the return type because you are either returning an int value or a DBNull type value, which are not compatible.

You can of course cast the instance of AgeIndex to be type object which would satisfy the ?: requirement.

You can use the ?? null-coalescing operator 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 is a quote from the MSDN documentation for the ?: operator that explains the problem

Either the type of first_expression and second_expression must be the same, or an implicit conversion must exist from one type to the other.

Best method of assigning NULL value to SqlParameter

Yes, for the value of the parameter, just use DBNull.Value. For example:

SqlParameter sqlError = 
    new SqlParameter("@errors", errors == 0 ? (object)DBNull.Value : errors);

Or write a little helper:

private object ValueOrDBNullIfZero(int val) {
   if ( val == 0 ) return DBNull.Value;
   return val;
}

Then:

SqlParameter sqlError = 
    new SqlParameter("@errors", ValueOrDBNullIfZero(errors));

Assigning null values to SqlParameter DateTime

You need to pass DBNull.Value to your stored procedure:

new SqlParameter("@DLExpiration", DBNull.Value)

How do I pass a null value into a parameter for a SqlCommand

Since my solution from comment worked - will post it here. Basically the problem as already described (and even already answered in your previous question) is that you need to use IS NULL to compare values with null in sql. Since you can have two cases (your parameter is either null or not) - you have to test for both conditions like this:

where parent_pk = @parent_pk or (@parent_pk IS NULL and parent_pk IS NULL)

How to assign a value to sqlparameter NULL values without checking for null or 0?

For nullable types, I'd just use an extension method:

public static object CoalesceNullToDBNull(this object input)
{
    return input == null ? DBNull.Value : input;
}

Then use it as:

cmd.Parameters["Foo"].Value = someExpression.CoalesceNullToDBNull();

(You could give it a shorter name, of course.)

That won't help for your case though, because you're trying to coalesce a non-null value to null. I would personally consider redesigning your model - use a nullable type for values which can be null, rather than using a magic number. However, you could still use a generic extension method:

public static object CoalesceDefaultToDBNull<T>(this T input)
{
    return EqualityComparer<T>.Default.Equals(input, default(T))
        ? DBNull.Value : (object) input;
}

That will convert 0, '\0', false, null etc to DBNull.Value. You'd have to use that with extreme care though, as I assume there are plenty of places where a 0 should really be a 0.

SqlParameter disappears when assigned a value of null?

Don't set the value to null if you want to pass a NULL to the stored procedure

The docs are almost right. In the phrase :

Use Value to send a NULL value as the value of the parameter.

The link points to DbNull.Value. That's a documentation error

Passing null instead of DbNull.Value means you want to use the stored procedure's default parameter. That part of the docs is correct :

Use null or do not set Value to use the default value for the parameter.

Update

I just added a Github issue for this

Passing null as SQLParameter DateTime value

SqlParameter moFrom1Param = new SqlParameter("@MoFrom1", dTOForwarding.MoFrom1 == null ? DBNull.Value : dTOForwarding.MoFrom1);
            moFrom1Param.IsNullable = true;
            moFrom1Param.Direction = ParameterDirection.Input;
            moFrom1Param.SqlDbType = SqlDbType.DateTime;
            cmd.Parameters.Add(moFrom1Param);

Why SqlParameter always set parameter is null?

IntelliTrace currently only supports this kind of type information for log files from MMA scenarios. In your scenario, the type information from SqlParameter isn't collected; as a result all the variables in the query default to SQL_VARIANT with null values.

how to pass null value to database from cmd.Parameters.AddWithValue in asp.net

cmd.Parameters.Add(new SqlParameter("@RedirectUris", string.IsNullOrEmpty(RedirectUris.Text) ? (object)DBNull.Value : RedirectUris.Text));

SqlParameter : Insert a row with a null float value

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.

---

Related Topics