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.

Answer 2

Based on this post,

I think you just need to mark your property with the Bindable attribute:

<System.ComponentModel.Bindable(True)> _
Public Property DateTimeOffset As DateTimeOffset?

Answer 3

The problem is that DbNull is different from Nothing and you must explicitly write that somewhere in your code. My first idea here was to use the binding events to add the value. So if you keep you code like this:

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

You can manually add the DateTimeOffset parameter in the Updating events before the binding proceeds (I can update the answer later with more details on this if you want)

Anyway, after reading your code more carefully I thought that maybe the CType isn't casting correctly. Have you tried replacing your Get with something like this?

Get
    Return If(IsDbNull(ViewState("DTO")), Nothing, CType(ViewState("DTO"), DateTimeOffset?))
End Get

Answer 4

Your code with iif ... works for me.
I have created a testing control and a page version with code behind pages ( I have tested code behind version too but this one is easier to publish). I have VS2010 target framework is 4.0. First the control(TstNullableCtrl.ascx):

<%@ Control Language="vb" AutoEventWireup="false" %>
<script runat="server">
    Public Property DateTimeOffset As DateTimeOffset?
</script>
<div ID="Label1" runat="server" >
 <%= If(DateTimeOffset.HasValue, DateTimeOffset.ToString, "Empty")%>
</div>

and the page - a table with two rows and two columns bound to datagrid (TstNullablePage.aspx):

<%@ Page Language="vb" AutoEventWireup="false"  %>
<%@ Import Namespace="System.Data"%>
<%@ Register src="TstNullableCtrl.ascx" tagname="TstNullableCtrl" tagprefix="uc1" %>
<script runat="server">
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim tbl As New DataTable
        tbl.Columns.Add(New DataColumn("id", GetType(Integer)) With {.AutoIncrement = True})
        tbl.Columns.Add(New DataColumn("myDateTimeOffsetField", GetType(DateTimeOffset)))
        Dim row As DataRow
        row = tbl.NewRow : row("myDateTimeOffsetField") = DateTimeOffset.Now
        tbl.Rows.Add(row)
        row = tbl.NewRow : row("myDateTimeOffsetField") = DBNull.Value
        tbl.Rows.Add(row)
        tstgrd.DataSource = tbl : tstgrd.DataBind()
    End Sub
</script>
<html>
<body>
    <form id="form1" runat="server">
      <asp:datagrid ID="tstgrd" runat="server">
        <Columns>
         <asp:TemplateColumn HeaderText="Offset">
           <itemtemplate>    
             <uc1:TstNullableCtrl ID="WithNullableDate1" runat="server" 
               DateTimeOffset='<%# iif(IsDbNull(Eval("myDateTimeOffsetField")), Nothing, Eval("myDateTimeOffsetField")) %>' />
            </itemtemplate>
         </asp:TemplateColumn>
         </Columns>
        </asp:datagrid>
    </form>
</body>
</html>

And expected result (a value when there is and 'Empty' when is null)

Edit

However I think that to "avoid complication" best solution is as follows:

Public _DateTimeOffset As Object
Public Property DateTimeOffset As Object
Get
    If IsDBnull(_DateTimeOffset) then Return Nothing
    Return Ctype(_DateTimeOffset, DateTimeOffset?) 
End Get
Set(value As Object)
    _DateTimeOffset = value
End Set
End Property 

Answer 5

I know this question have been already answered I just would like to document my solution which is in between those two answers as I have came across this today:

Private _AssetId As Object
<Bindable(True, BindingDirection.TwoWay)>
Public Property AssetId() As Object
    Get
        If _AssetId Is Nothing Then                
             Return Nothing                
        Else
            Return CType(_AssetId, Integer)
        End If
    End Get
    Set(ByVal value As Object)
        If value Is Nothing OrElse IsDBNull(value) OrElse CType(value, String) = "" Then
            _AssetId = Nothing                
        Else
            _AssetId = CType(value, Integer)                
        End If
    End Set
End Property