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
Niels
