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?

Thanks,

Forch

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