How to check if DataReader value is not null?

Question

I'm writing a VB.Net code that reads an Oracle Table through an SQL query.

The SQL query may return some null columns. I'm trying to check if these columns are null or not but I'm receiving the error An exception of type 'System.InvalidCastException' occurred in Oracle.DataAccess.dll but was not handled in user code. The column contains some Null Data

Here is my code :

Dim Reader as OracleDataReader 
'Execute the query here...

Reader.Read()
If IsNothing(Reader.GetDateTime(0)) Then  'Error here !!
    'Do some staff 
end if

Does anyone have an idea on how to check if a column is null please ?

Thank you

Answer 1

Nothing means an object has not been initialized, DBNull means the data is not defined/missing. There are several ways to check:

' The VB Function
If IsDBNull(Reader.Item(0)) Then...

The GetDateTime method is problematic because you are asking it to convert a non value to DateTime. Item() returns Object which can be tested easily before converting.

 ' System Type
 If System.DBNull.Value.Equals(...)

You can also the DbReader. This only works with the ordinal index, not a column name:

If myReader.IsDbNull(index) Then 

Based on that, you can put together functions either as Shared class members or reworked into Extensions to test for DBNull and return a default value:

Public Class SafeConvert
    Public Shared Function ToInt32(Value As Object) As Integer
        If DBNull.Value.Equals(Value) Then
            Return 0
        Else
            Return Convert.ToInt32(Value)
        End If
    End Function

    Public Shared Function ToInt64(Value As Object) As Int64
        If DBNull.Value.Equals(Value) Then
            Return 0
        Else
            Return Convert.ToInt64(Value)
        End If
    End Function

    ' etc
End Class

Usage:

myDate = SafeConvert.ToDateTime(Reader.Item(0))

For a DateTime converter, you'd have to decide what to return. I prefer to do those individually.

Answer 2

You need to check if the field is null before you convert the value to date.

If (Reader.IsDBNull(0)) Then
    'Null: Do not call GetDateTime
End If

If (Not Reader.IsDBNull(0)) Then
    'Not null: Retrieve the datetime.
    Dim dt As DateTime = Reader.GetDateTime(0)
End If