detect empty datareader 1

[jaredfan]

I got this working on for my dropdownlist populated on page load - very easy.

However, I'm having difficulty when a change is made after page load. I have two dropdown lists: hardware category and hardware sub category. When the category is changed, I pull a stored procedure that populates the second dropdown list with appropriate selections. Some categories do not have sub categories.

What I am trying to do is print "N/A" as the selected item in the dropdownlist when no items are returned, or "Not selected" as the selected field when there are items.

If Not (dr.Read()) Then
ddlTypeSub.Items.Insert(0, "N/A&quot
ddlTypeSub.SelectedIndex = 0
ddlTypeSub.SelectedItem.Value = 0
ddlTypeSub.SelectedItem.Text = "N/A"
dr.Close()
Else
ddlTypeSub.Items.Insert(0, "not selected&quot
ddlTypeSub.SelectedIndex = 0
ddlTypeSub.SelectedItem.Value = 0
ddlTypeSub.SelectedItem.Text = "not selected"
dr.Close()
End If

However, the sub category box returns the selected item "N/A" regardless of whether there are sub category items returned for the category selected. I tried placing "If Not page.IsPostBack" around the page load calls that populate these dropdown lists, but that didn't make a difference. It's probably a dumb error somewhere - can anyone offer any advice to lessen my mental anguish?

Thanks,

John

 

[link9]

This, IMHO, is a serious problem with the datareader that MS simply overlooked... the ability to check for an empty one.

Problem is that the .read() violates the Command-Query Separation Principle by both returning a value and performing a command...

In this case, telling you if there's a record, and moving to that record, all in one fell swoop... just bad programming practice (albeit convenient most of the time).

The ONLY work-around that I've found is to declare an incrementor = 0, and increment it on each iteration of the .read() operation.

Then, you check it when you're out of the loop, and if it's still = 0, then you can assume you never went into the loop, that the datareader is empty, and you can do what you need to do:

dim i as integer
while dr.read()
'do your stuff here
i+=1
wend
if i = 0 then
'take appropriate action -- the datareader is empty
' and whatever's in the loop never got executed
end if

hth!
paul

 

[Alcar]

I'm sorry if I interrupt, but couldn't you just check:

SqlDataReader1.RecordsAffected before getting into the while(SqlDataReader1.Read()) loop?
Daren J. Lahey
Just another computer guy...
FAQ183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
Support your forums TODAY!

http://www.tek-tips.com/supportus.cfm

 

[link9]

Excellent. I didn't realize it had that property.

I even asked about this issue at a conference and a speaker just shrugged it off like it was nothing, and kept right on going.

Thanks, Alcar!

 

[Alcar]

You are more than welcome Paul! (thanks for the Christmas Star hehehe) Daren J. Lahey
Just another computer guy...
FAQ183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
Support your forums TODAY!

http://www.tek-tips.com/supportus.cfm