NULL values in CLR TableResult UDF


  • Hi everyone,


    I need my UDF (which returns a table) to be able to return NULL values.


    My function looks like this:


    <SqlFunction(FillRowMethodName:="Process_TrainingInfo", TableDefinition:=" CourseName nvarchar(80), CreditDate DateTime, " + _

                                        "CreditResult nvarchar(1), ExpiryDate DateTime ")> _

    Public Shared Function funct_GetCreditsFromTIMS(ByVal EmployeeID As String) As IEnumerable


          Dim dr() as dataRow


          …. This queries an oracle database which returns a small number of rows….  This all works well….


    Return dr


    End Function


    This is the Fill Row Method:


    Public Shared Sub Process_ TrainingInfo(ByVal row As Object, <Runtime.InteropServices.Out()>ByRef CourseName As String, _

                                                                            <Runtime.InteropServices.Out()> ByRef CreditDate As Date, _

                                                                            <Runtime.InteropServices.Out()> ByRef CreditResult As String, _

                                                                            <Runtime.InteropServices.Out()> ByRef ExpiryDate As Date)


            Dim dr As DataRow = CType(row, DataRow)


            CourseName = dr.Item(1).ToString

            CreditDate = CType(dr.Item(2), Date)

            CreditResult = dr.Item(3).ToString

             'dr.Item(5) Might be NULL!!!! 

            If Not IsDBNull(dr.Item(5)) Then

    ExpiryDate = CType(dr.Item(5), Date)

            End If


    End Sub


    The problem is that the EXPIRYDATE field may be NULL.  If I leave the field empty, or I try ExpiryDate = Nothing I get this error:


    An error occurred while getting new row from user defined Table Valued Function :

    System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.


    I have also tried:


    Dim nullDate as Date

    ExpiryDate = nullDate


    …but I get the same error.


    I can’t do ExpiryDate = Dbnull.value, as I get this error “System.dbnull can not be converted to date”


    Is there anyway that I can do this?







    Thursday, November 17, 2005 2:21 PM


  • Try and change the ExpiryDate out param to SqlDateTime (from System.Data.SqlTypes namespace) and when the value is null set the ExpiryDate to SqlDateTime.Null.

    Thursday, November 17, 2005 3:20 PM