SQLDataReader: Dealing with null values

Question

Some tables I am dealing with have null values and are throwing errors. So far ive tried a few solutions to deal with the nulls with no success.

Here are the code samples from my efforts so far;

If (r("datemodified").Equals(DBNull.Value)) Then
                datemodified = String.Empty
            Else
                datemodified = (r("datemodified"))
            End If

and;

If r.HasRows Then
                datemodified = (r("datemodified"))
            Else
                datemodified = String.Empty
            End If

and;

 If r("datemodified") = Nothing Then
                datemodified = String.Empty
            Else
                datemodified = (r("datemodified"))
            End If

and;

If r.IsDBNull("datemodified") Then
                datemodified = String.Empty
            Else
                datemodified = (r("datemodified"))

and via sql;

Select isnull(datemodified, '')

The end result is an IndexOutOfRangeException.

here is the sql;

select datemodified, maintainedby, email, hitcount from grouping where id = @footid

ps, i have ran the query and it is working ok (i.e all the cols exist)

Answer 1

To handle the null value in the code you can use the IsDBNull method:

Dim index As Integer = r.GetOrdinal("datemodified")
If r.IsDBNull(index) Then
   datemodified = String.Empty
Else
   datemodified = r(index)
End If

To handle the null value in the SQL you have to give the field a name, to be able to access it by name in the data reader:

select datemodified = isnull(datemodified, '')

Answer 2

The IndexOutofRangeException is becoz of the Column you are trying to access does not exists in the result set.

When you are accessing the Ordinal, or the Column via SqlDataReader, you must specify the Column Index, or the Column Name. In your scenario, you need to specify an Alias for the SQL Column returning.

SELECT ISNULL(datemodified, '') AS [datemodified]