.Net SqlDataReader Item is Null [VB]

Question

Ok, this is strange. I ran into this issue tonight when I was adding a feature for our corporate site.

I'm building a custom calendar control that queries our database to display corporate events. Here is the situation, there is an EndDate value stored and on the dev system one of the events have a NULL value. No big deal since it's only a test system, but might as well check before trying to use it anyway on the safe side. I figured the following code would work:

While dr.Read()
  corporateTable.Rows.Add(New Object() { _
    Convert.ToDateTime(dr("EventBeginDate")) _
    , IIf(dr("EventEndDate") Is DBNull.Value, Convert.ToDateTime(dr("EventBeginDate")).AddDays(1), Convert.ToDateTime(dr("EventEndDate"))) _
    , Convert.ToString(dr("EventType")) _
    , Convert.ToString(dr("EventDescription")) _
    , Convert.ToString(dr("EventMessage")) _
  })
End While

But it didn't, I still kept getting the Object cannot be cast from DBNULL error. So I thought it over and came up with this code which works successfully, although I don't like it and think it's ugly.

While dr.Read()
  Dim column As Integer = 0
  While column < dr.FieldCount - 1
    If dr.GetName(column) = "EventEndDate" Then
      Exit While
    End If

    column += 1
  End While
  corporateTable.Rows.Add(New Object() { _
    Convert.ToDateTime(dr("EventBeginDate")) _
    , IIf(dr.IsDBNull(column), Convert.ToDateTime(dr("EventBeginDate")).AddDays(1), dr.Item(column)) _
    , Convert.ToString(dr("EventType")) _
    , Convert.ToString(dr("EventDescription")) _
    , Convert.ToString(dr("EventMessage")) _
  })
End While

The thing that really gets me is, at one point I had this:

, IIf(dr.IsDBNull(column), Convert.ToDateTime(dr("EventBeginDate")).AddDays(1), Convert.ToDateTime(dr("EventEndDate"))) _

Thinking that it should work because it should only evaluate the dr() if it's not NULL. However, it kept erroring out at the end because the value was in fact NULL.

So to finally get to my question, sorry for the long explanation.

Why is it that even though I'm checking if it's NULL before using the value, it errors out at the part that doesn't get called unless it's not NULL? Does it have to do with the fact that I'm using the IIF() and it's evaluating the whole statement? Or, using the dr(), it evaluates at runtime?

I'm just stumped and would like to know what exactly is going on, so if it's possible, come up with a cleaner solution.

Thanks in advance!

Will A · Accepted Answer · 2011-06-21 22:24:30Z

5

IIf always evaluates both the true and false parts - use inline If instead if you want to avoid this:

While dr.Read()
  corporateTable.Rows.Add(New Object() { _
    Convert.ToDateTime(dr("EventBeginDate")) _
    , If(dr("EventEndDate") Is DBNull.Value, Convert.ToDateTime(dr("EventBeginDate")).AddDays(1), Convert.ToDateTime(dr("EventEndDate"))) _
    , Convert.ToString(dr("EventType")) _
    , Convert.ToString(dr("EventDescription")) _
    , Convert.ToString(dr("EventMessage")) _
  })
End While

answered Jun 21, 2011 at 22:24

Will A

25k5 gold badges54 silver badges61 bronze badges

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

11 Comments

turtlepick Over a year ago

You might also try to make an extension method(s) using this approach: bradwilson.typepad.com/blog/2008/01/c-30-extension.html

Yuck Over a year ago

@Will: I always get a laugh out of this as the function IIf is short for immediate if, which couldn't be further from the truth. Unless you interpret it as immediately evaluating both conditions. =)

Kyle Undefined Over a year ago

Unfortunately that didn't work, it hangs up the page. I'll keep that in mind though for the future.

Will A Over a year ago

@Yuck - LOL, likewise, it's a shame it wasn't a proper inline-if from the outset.

Will A Over a year ago

@Kyle - it's highly unlikely that the If(...) is responsible for hanging up the page - you're sure it hangs and doesn't error?

|

Baldy · Accepted Answer · 2011-06-21 22:28:00Z

0

The Iif function is evaluating each statement, use a different construct for your null check such as The IF function

answered Jun 21, 2011 at 22:28

Baldy

3,6684 gold badges40 silver badges61 bronze badges

Collectives™ on Stack Overflow

.Net SqlDataReader Item is Null [VB]

2 Answers 2

11 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related