Store DBnull in database for date column that accepts null

Question

I have a DateTime value that I will like to save to the database. The column allows null in the database. When I try to run the code below I get this error message.

Type of conditional expression cannot be determined because there is no implicit conversion between system.dbnull and system.datetime

How can I store null or empty value for a date column?

public class ExcelData
{
  public DateTime? DateValue1 { get; set; }
}

DateValue1 = col28Value == null ? DBNull.Value : Convert.ToDateTime(col28Value)

you dont need to set DbNull to parameter.Value, just set parameter.Value = null that should be enough — Sriram Sakthivel
– Sriram Sakthivel, Commented Jul 12, 2013 at 20:30
If the column is nullable on the db DateValue1 = null should do it — Giorgio Minardi
– Giorgio Minardi, Commented Jul 12, 2013 at 20:34

Icarus · Accepted Answer · 2013-07-12 20:31:52Z

5

It's telling you that you can't declare a Nullable<DateTime> and assign it a DBNull.Value which is not the same as null

Your code should simply be:

DateValue1 = col28Value ==null? (DateTime?)null: Convert.ToDateTime(col28Value)

answered Jul 12, 2013 at 20:31

Icarus

64k14 gold badges102 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SLaks Over a year ago

Yes; this is exactly what I was describing.

Guvante Over a year ago

@SLaks: Ah yes, the whole "C# can't look at the output type when resolving types" annoyance.

user645280 Over a year ago

Probably how I'd wind up doing it, but wow so ugly... Almost better to stick with if/else in this case.

SLaks · Accepted Answer · 2013-07-12 20:36:17Z

3

The conditional operator requires that one of its types be convertible to the other.
It will not try to find the closest common base type.

Since DBNull and DateTime have nothing in common, it fails.

You need to cast either side to object.

However, that won't work either.
DBNull.Value is a special object used for certain APIs in System.Data.
It has nothing to do with nullable types.

Instead, you need use null itself.
Since null is not convertible to DateTime, you will also need to cast it to DateTime?. Alternatively, you can write new DateTime?(), which will create an empty (null) Nullable<DateTime> value.

edited Jul 12, 2013 at 20:36

answered Jul 12, 2013 at 20:30

SLaks

891k182 gold badges1.9k silver badges2k bronze badges

1 Comment

Guvante Over a year ago

A working example would make your answer complete, talking around nullables just makes it more complicated.

banging · Accepted Answer · 2013-07-12 20:33:55Z

0

Work with SqlParameter and not their value like so..

SqlParameter sp = col28Value == null ? new SqlParameter("@whatever", DBNull.Value) : new SqlParameter("@whatever", col28Value);

answered Jul 12, 2013 at 20:33

banging

2,5601 gold badge23 silver badges26 bronze badges

1 Comment

SLaks Over a year ago

This is unnecessarily verbose, unnecessary, and exactly the opposite of the question.

Jonesopolis · Accepted Answer · 2013-07-12 20:34:16Z

0

when you do this:

DateValue1 = col28Value == null ? DBNull.Value : Convert.ToDateTime(col28Value)

you may be doing :

DateValue1 = DbNull.Value;

which is not possible. So replace DbNull.Value with null

answered Jul 12, 2013 at 20:34

Jonesopolis

25.4k12 gold badges72 silver badges115 bronze badges

Collectives™ on Stack Overflow

Store DBnull in database for date column that accepts null

4 Answers 4

3 Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related