How to avoid IndexOutOfRangeException indexing DataReader columns?

Question

I am having a little trouble with an 'IndexOutOfRangeException'. I think it is because when the code tries to get the DateModified col from the database it sometimes contains NULLS (the users haven't always updated the page since they created it).

Here is my code;

s = ("select datemodified, maintainedby, email, hitcount from updates where id =   @footid")
Dim x As New SqlCommand(s, c)
x.Parameters.Add("@footid", SqlDbType.Int)
x.Parameters("@footid").Value = footid
c.Open()
Dim r As SqlDataReader = x.ExecuteReader
While r.Read
    If r.HasRows Then
        datemodified = (r("DateModified"))
        maintainedby = (r("maintainedby"))
        email = (r("email"))
        hitcount = (r("hitcount"))
    End If
End While
c.Close()

I thought (after a bit of msdn) that adding;

If r.HasRows Then 

End If 

Would solve the problem, but so far after adding I am still getting the same old IndexOutOfRangeException

(ps, I have tried datemodified as string / datetime)

Answer 1

Does changing

datemodified = (r("DateModified")) 

to

datemodified = (r("datemodified")) 

work?

Answer 2

So far i have tried:

 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, '')

Still seeing; IndexOutOfRangeException.

Answer 3

I have tried to recreate you issue by passing in nulls, empty sets. I can't recreate it in the sample of code you provided. Would you mind posting more code, maye even the whole page? Is there a line number that the error happens on? I would like to dig in further.

Answer 4

Try r.IsDBNull(columnIndex).