none
Filtering null datetime values in in LINQ query to return datatable Cannot cast dbnull.value RRS feed

  • Question

  • I'm trying to retrive a datatable using LINQ, but there are values in the table with null datetime values, and I get "InvalidCastException: Cannot cast DBNull.Value to System.DateTime. Please use a nullable type." when I use the CopyToDataTable method.

    How do I re-word the following LINQ query so that it does not return the rows with null values in the WorkAreaStartTime column?

    Dim BumpQuery = _
         From bump In BumpTransDT.AsEnumerable _
         Where bump.Field(Of Boolean)("IsBumpComplete") = Cmd111.ShowComplete _
         And bump.Field(Of Boolean)("IsBumpDeleted") = Cmd111.ShowDeleted _
         And bump.Field(Of Nullable(Of DateTime))("WorkAreaStartTime") IsNot Nothing _
         And bump.Field(Of DateTime)("WorkAreaStartTime").Date = Cmd111.BumpDate _
         And bump.Field(Of String)("WorkArea") = Cmd111.WorkAreaName _
         Select bump
    Dim BumpsDT As DataTable = BumpQuery.CopyToDataTable
    The line "And bump.Field(Of Nullable(Of DateTime))("WorkAreaStartTime") IsNot Nothing _" came from a different forum where someone was having a similar issue, but it didn't fix the problem.
    Thursday, October 4, 2012 8:33 PM

Answers

  • It gives me a syntax error on the line:

    And bump.Field(Of DateTime)("WorkAreaStartTime") IsNot Nothing _

    "'IsNot' required operands that have reference types, but this operand has the value type 'Date'."

    When I change it to this:

    And Not bump.Field(Of DateTime)("WorkAreaStartTime") = Nothing _

    The error goes away. I think I already tried this before, but since the start time (and stop time) are required for calculating reports anyway, I ended up changing the SQL database to not allow null values in this column.

    • Proposed as answer by Alexander Sun Thursday, October 11, 2012 5:24 AM
    • Marked as answer by Alexander Sun Tuesday, October 23, 2012 8:27 AM
    Monday, October 8, 2012 1:58 PM

All replies

  • Hi,

    Please try this:

    Dim BumpQuery = _
    From bump In BumpTransDT.AsEnumerable _     
    Where bump.Field(Of Boolean)("IsBumpComplete") = Cmd111.ShowComplete _     
    And bump.Field(Of Boolean)("IsBumpDeleted") = Cmd111.ShowDeleted _     
    And bump.Field(Of Nullable(Of DateTime))("WorkAreaStartTime") IsNot Nothing _     
    And bump.Field(Of DateTime)("WorkAreaStartTime") IsNot Nothing _
    And bump.Field(Of DateTime)("WorkAreaStartTime").Date = Cmd111.BumpDate _     
    And bump.Field(Of String)("WorkArea") = Cmd111.WorkAreaName _     
    Select bump
    

    Monday, October 8, 2012 1:46 PM
  • It gives me a syntax error on the line:

    And bump.Field(Of DateTime)("WorkAreaStartTime") IsNot Nothing _

    "'IsNot' required operands that have reference types, but this operand has the value type 'Date'."

    When I change it to this:

    And Not bump.Field(Of DateTime)("WorkAreaStartTime") = Nothing _

    The error goes away. I think I already tried this before, but since the start time (and stop time) are required for calculating reports anyway, I ended up changing the SQL database to not allow null values in this column.

    • Proposed as answer by Alexander Sun Thursday, October 11, 2012 5:24 AM
    • Marked as answer by Alexander Sun Tuesday, October 23, 2012 8:27 AM
    Monday, October 8, 2012 1:58 PM