Specified Cast is not valid when DataBind to Nullable DateTimeOffset and field is NULL

Question

I've created a simple CompositeControl and exposed a Nullable DateTimeOffset property. I'm binding the control to a SQL Server DateTimeOffset field using

DateTimeOffset='<%# Bind("myDateTimeOffsetField") %>'

This works great when the DateTimeOffset field has a value. But when the field is NULL I get a "Specified Cast is not valid" error.

How do I stop this error and set my property to Nothing when the field is NULL?

I thought this would be the default behaviour!

Property definition is:

Public Property DateTimeOffset As DateTimeOffset?

Later comment:

I've found that this works if I change from using Bind to:

DateTimeOffset='<%# iif(IsDbNull(Eval("myDateTimeOffsetField")), Nothing, Eval("myDateTimeOffsetField")) %>'

But then I don't get "myDateTimeOffsetField" passed as an argument in the FormView.ItemUpdating event (yes, this is in a FormView control) since ASP.NET assumes I'm not binding back to the Database.

Actual Code (Added by Request)

This is the Property in my Composite Control that I'm trying to bind to:

Public Property DateTimeOffset As DateTimeOffset?
Get
    Return CType(ViewState("DTO"), DateTimeOffset?)
End Get
Set(value As DateTimeOffset?)
    ViewState("DTO") = value
End Set
End Property

Heres the markup for the Binding. The Control is in the EditItemTemplate of a FormView which is bound to a SQL DataSource returning a field called [dtoMldRejOn] with an optional DateTimeOffset value.

<APS:DateTimeOffsetControl runat="server" id="dtocMldRejOn" TextBoxCssClass="inputdatetime" ValidationGroup="vw1" FieldName="<%$ Resources: Resource, rxgFrom %>" DateTimeOffset='<%# Bind("dtoMldRejOn") %>' WindowsTimeZoneID="<%# me.WindowsTimeZoneID %>" IsRequired="false" />

As you can see, my Composite control is for handling DateTimeOffset values. It all works great unless the DateTimeOffset field [dtoMldRejOn] from the database is NULL, then I get the exception.

Answer 1

I have never created bindable controls before, but I would like to make suggestion. How about setting your DateTimeOffset property to be of type Object. That way, the property will accept any data types including DBNull.

And once inside the Set code, check if the value passed is DBNull.Value. If so, create a new empty DataTimeOffset? object and save it in the ViewState.

If non DBNull values, throw error if it cannot be be converted to datetime.

I didn't try this though so I don't know if this will work or not.

################ UPDATED ANSWER ################

My suggestion is, you create 2 properties as follows:

Public Property DateTimeOffset() As DateTimeOffset?
    Get
        Return DirectCast(ViewState("DTO"), DateTimeOffset?)
    End Get
    Set(ByVal Value As DateTimeOffset?)
        ViewState("DTO") = Value
    End Set
End Property

<Bindable(True, BindingDirection.TwoWay)>
Public Property DbDateTimeOffset As Object
    Get
        Return Me.DateTimeOffset
    End Get
    Set(value As Object)
        If IsDBNull(value) OrElse value Is Nothing Then
            Me.DateTimeOffset = New DateTimeOffset?
        Else
            Me.DateTimeOffset = DirectCast(value, DateTimeOffset?)
        End If
    End Set
End Property

So in your markup, the binding will be to the DbDateTimeOffset property:

DbDateTimeOffset='<%# Bind("myDateTimeOffsetField") %>'

While in code behind, you can use the other property to read the property without having to cast.