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)

datemodified (if i comment that line out, then it immediately throws another on the next line which is maintainedby) The reason for the nulls is when people update pages they do not always fill out the details. Personally I would disallow nulls, but the db is legacy. — Phil
– Phil, Commented Feb 9, 2010 at 10:32
I asked about your SQL because as Kosala pointed out you would get an IndexOutOfRange Exception if you don't alias the column. — Kamal
– Kamal, Commented Feb 9, 2010 at 11:06

Guffa · Accepted Answer · 2010-02-09 10:33:10Z

5

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

answered Feb 9, 2010 at 10:33

Guffa

703k111 gold badges760 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Phil Over a year ago

THanks for the assistance. After trying both of these methods, the problem is still occuring.

Guffa Over a year ago

Then the problem is somewhere else... Do you call Read before trying to get data from the data reader? Check the value of r.FieldCount to see if you actually have any columns in the result, and r.GetOrdinal("datemodified") to see if the field is one of them.

It's actually me · Accepted Answer · 2010-02-09 10:56:25Z

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]

edited Feb 9, 2010 at 10:56

answered Feb 9, 2010 at 10:49

It's actually me

5022 silver badges9 bronze badges

1 Comment

Phil Over a year ago

Thanks for the help. After editing i have; select ISNULL(datemodified, '') AS datemodified, maintainedby, email, hitcount from grouping where id = @footid . Unfortunately the exception is still happening.

Collectives™ on Stack Overflow

SQLDataReader: Dealing with null values

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related