How to handle passing DBNull into a function?

Question

I'm returning results from a stored procedure and passing them off to a function for further processing. In some cases, it's possible (and perfectly fine) for one of the fields, a date value, to return null.

However, whenever I pass the null into the function, an exception is thrown trying to convert the null into there function parameter's type. What is the best way to handle this?

Data:

Name    StartDate    EndDate
Bob     01/01/2013   NULL

Calling Function:

MyFunction(
           DataRow.Item("StartDate"), 
           DataRow.Item("EndDate")) ' <--- invalid cast exception

Function:

Public Function MyFunction(
                           ByVal StartDate as Date, 
                           ByVal EndDate as Date) As Object
    ....
    Return something
End Function

EDIT: Lots of great tips but still no dice.

Declaring the DateTime type in the function as nullable, ByVal EndDate as DateTime?, results in System.InvalidCastException: Specified cast is not valid.

Using DataRow.Field(Of DateTime)("EndDate") along with declaring the parameter as a nullable type results in System.InvalidCastException: Cannot cast DBNull.Value to type 'System.DateTime'

EDIT2: Found a source of one of my problems. I was using Iif(), and one of the values was of type System.DBNull, the other was of type Date. And both the true and false parts must be the same type. Took me a while to spot that.

Bear this in mind in the future too, many datatypes aren't nullable by default, and have to be explicitly declared as such for a null value to be allowable. — user2366842, Sep 05 '13 at 15:03

score 6 · Answer 1 · answered Sep 05 '13 at 14:54

6

Use a nullable DateTime parameter:

 ByVal EndDate AS Nullable(Of DateTime)

Full example:

Public Function MyFunction(ByVal StartDate as Date, ByVal EndDate as Nullable(Of DateTime)) As Object
    ....
    Return something
End Function

answered Sep 05 '13 at 14:54

Darren

68,902
24
138
144

Interesting, I did not know that DateTime was functionally any different from Date. I always wondered about that. Thank you. – cowsay Sep 05 '13 at 14:56
@Darren Davies I tried that and now it's giving me a more generic error on the same line.. "System.InvalidCastException: Specified cast is not valid." – cowsay Sep 05 '13 at 15:13

score 1 · Accepted Answer · answered Sep 05 '13 at 14:55

1

You try can making the parameter Nullable:

Public Function MyFunction(ByVal StartDate as DateTime, ByVal EndDate as DateTime?) As Object
    ....
    Return something
End Function

But you might need to call it using the the Field method:

MyFunction(DataRow.Field(Of DateTime)("StartDate"), DataRow.Field(Of DateTime?)("EndDate"))

answered Sep 05 '13 at 14:55

p.s.w.g

146,324
30
291
331

I never knew about the `?` shorthand for Nullable! What a great, unexpected tip. – cowsay Sep 05 '13 at 15:16
If I use your Field(Of DateTime) suggestion, I get "System.InvalidCastException: Cannot cast DBNull.Value to type 'System.DateTime'. Please use a nullable type." ... if I use just row.Item("..") then I get "System.InvalidCastException: Specified cast is not valid." Any thoughts? – cowsay Sep 05 '13 at 15:27
1

@user1003916 Note that it's `Field(Of DateTime?)` (nullable, with a `?`). This should work. According to [MSDN](http://msdn.microsoft.com/en-us/library/system.data.datacolumn.aspx): "*If the value of the specified DataColumn is Value and *T* is a reference type or nullable type, the return type will be **null**. The Field method will not return DBNull.Value.*". If `StartDate` can be nullable too, you might need to use `Field(Of DateTime?)` with both arguments. – p.s.w.g Sep 05 '13 at 16:21

score 0 · Answer 3 · edited May 23 '17 at 10:25

0

you should look into using a nullable value for the values that can be null. Take a look here for an example.... DateTime "null" value

edited May 23 '17 at 10:25

Community

1
1

answered Sep 05 '13 at 14:54

user2366842

1,231
14
23

Not sure if start time should be nullable too, but something to keep in mind. – user2366842 Sep 05 '13 at 14:56

score 0 · Answer 4 · answered Sep 05 '13 at 14:56

0

From the database side you can null check the date time and assign any date something like '1/1/1990'

answered Sep 05 '13 at 14:56

BINU VARGHESE

364
1
4
16

1

This sounds like a terrible idea. – p.s.w.g Sep 05 '13 at 16:23

How to handle passing DBNull into a function?

4 Answers4