up vote 4 down vote favorite

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)

share|improve this question
IOORE where? – Anton Gogolev Feb 9 '10 at 10:28
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 Feb 9 '10 at 10:32
Can you add your SQL that you are using to your question? – Kamal Feb 9 '10 at 10:44
Yes have done, thanks – Phil Feb 9 '10 at 10:59
I asked about your SQL because as Kosala pointed out you would get an IndexOutOfRange Exception if you don't alias the column. – Kamal Feb 9 '10 at 11:06

2 Answers

up vote 4 down vote accepted

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, '')
share|improve this answer
THanks for the assistance. After trying both of these methods, the problem is still occuring. – Phil Feb 9 '10 at 10:38
1  
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. – Guffa Feb 9 '10 at 12:47
Thanks Guffa, Its now working. Thanks a lot for the help! – Phil Feb 10 '10 at 7:30
up vote 2 down vote

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]
share|improve this answer
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. – Phil Feb 9 '10 at 11:05

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.