Question Conversion from type 'DBNull' to type 'Date' is not valid.

[Antone.evans]

I'm getting the error "Conversion from type 'DBNull' to type 'Date' is not valid."
here's the line generating the error.

dtReady.Value = dSet.Tables("Db").Rows(Inc).Item("Fixed_Date")

I'm using a date/time picker control and sending the db value to it. I know that the value is null, but I don't understand why the Picker can't be blank. Is there a way to let it be blank? Or should I set up a way to check for nulls and assign today's date or something just in case?

Thanks

 

[Antone.evans]

Never mind. Apparently .ToString() works on everything.

 

[jmcilhinney]

Simply calling ToString is definitely not the solution to your problem. A DateTimePicker can't be blank because its Value property type DateTime, which is a value type and, therefore, must have a value. The solution to this situation is usually to set the ShowCheckBox property to True and then have the check box indicate whether the control contains a value or not. You would set it like so:

If myDataRow.IsNull("MyColumn") Then
    myDateTimePicker.Checked = False
Else
    myDateTimePicker.Value = CDate(myDataRow("MyColumn"))
End If

The other way would look like this:

If myDateTimePicker.Checked Then
    myDataRow("MyColumn") = myDateTimePicker.Value
Else
    myDataRow("MyColumn") = DBNull.Value
End If

Note that, when you set Checked to True, the control text is greyed out rather than cleared. If you want it cleared then you can mess with the format, setting the Format property to Custom and the CustomFormat to a single space character.

Another option is to use the ExtendedDateTimePicker found here:

Quantum Software Solutions - Windows Forms Components

It inherits the standard DateTimePicker and adds an extra property that supports Dates and DBNulls. It also encapsulates the formatting I mentioned.

 

[Antone.evans]

Thank you,

That seems to work. If I set the Format property to custom will it just accept whatever I set the value to? Or do I have to define it somewhere else?
Sorry, if that's a silly question.

 

[jmcilhinney]

Setting the Format to Custom has nothing whatsoever to do with the Value. The Value property is type DateTime and will always contain a DateTime value. The Format and the CustomFormat determine how the data is displayed, so it's about how that DateTime gets converted to a String for display. If you set the Format to Custom and the CustomFormat to a space then the control will display a space, which looks like nothing. The Value will still contain a DateTime, but the user just can't see it.

 

[Antone.evans]

Gotcha. That helped a lot thank you!

Got stuck on another problem though
http://www.vbdotnetforums.com/vb-net-general-discussion/44312-update-syntax-error.html