none
NULL values in CLR TableResult UDF

    Question

  • 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?

     

    Thanks,

     

    Forch

     

     

    Thursday, November 17, 2005 2:21 PM

Answers

  • 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.

    Niels
    Thursday, November 17, 2005 3:20 PM
    Moderator