The specified Cast is not valid when a DataBind in a Nullable DateTimeOffset and a NULL field

Question

I created a simple CompositeControl and discovered the Nullable DateTimeOffset property. I bind the control to SQL Server DateTimeOffset fields using

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

This works fine when the DateTimeOffset field has a value. But when the field is NULL, I get the error "The specified movie is not valid."

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

I thought this would be the default behavior!

Property Definition:

 Public Property DateTimeOffset As DateTimeOffset? 

Next comment:

I found that this works if I switch from using Bind to:

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

But then I do not get the "myDateTimeOffsetField" passed as an argument in the FormView.ItemUpdating event (yes, this is in the FormView control), since ASP.NET assumes that I am not bound to the database.

Actual code (added on request)

This property in my composite control 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 

Here is the markup for snapping. The control is located in the EditItemTemplate FormView, which is bound to an SQL DataSource that returns a field named [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 designed to handle DateTimeOffset values. All of this works fine, if the DateTimeOffset [dtoMldRejOn] field from the database is NULL, then I get an exception.

Answer 1

I have never created linked controls before, but I would like to make a suggestion. How to set the DateTimeOffset property of type Object . Thus, the property will accept any data types, including DBNull.

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

If the values ​​are not DBNull, print an error if it cannot be converted to datetime.

I have not tried this, although I do not know if this will work or not.

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

My suggestion is that 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") %>' 

In the code behind, you can use another property to read the property without having to do it.

Answer 2

Based on this post ,

It seems to me that 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 this somewhere in your code. My first idea was to use binding events to add value. Therefore, if you save the code as follows:

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

You can manually add the DateTimeOffset parameter to Updating events before continuing the binding (I can update the response later with more details about this if you want)

In any case, after reading your code more carefully, I thought that maybe CType not CType 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 created a test control and a page-by-page version of the code (I also tested code by version, but this is easier to publish). I have VS2010 target structure 4.0. First 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 is a table with two rows and two columns associated with 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 the expected result (value when there is also "Empty" when it is null)

Edit

However, I believe that the best solution to “avoid complications” is to:

 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 has already been answered. I just would like to document my solution, which is between these two answers, as I 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