I have the code below that I use to fill a DropDownList control. I would
like to do something different when it returns more than 1 record. Can I do
this here or back at the code where I call this class function? Thanks.

David
Public Shared Function GetStaffPrograms(ByVal intStaffID As Int32) As
SqlDataReader
Dim conData As SqlConnection = New
SqlConnection(ConfigurationManager.ConnectionStrings("CoreConnectionString").ConnectionString)
conData.Open()

Dim strSQL As String

strSQL = "SELECT ProgramID, ProgramName" & _
" FROM dbo.vw_StaffPrograms" & _
" WHERE StaffID = " & intStaffID.ToString

Dim cmdSel As SqlCommand = New SqlCommand(strSQL, conData)
Dim dtr As SqlDataReader = cmdSel.ExecuteReader()
Return dtr
End Function

because datareaders do a "firehose" read, the record count is not known
until all rows are read.

-- bruce (sqlwork.com)

David C wrote:
> I have the code below that I use to fill a DropDownList control. I would
> like to do something different when it returns more than 1 record. Can I do
> this here or back at the code where I call this class function? Thanks.
>
> David
> Public Shared Function GetStaffPrograms(ByVal intStaffID As Int32) As
> SqlDataReader
> Dim conData As SqlConnection = New
> SqlConnection(ConfigurationManager.ConnectionStrings("CoreConnectionString").ConnectionString)
> conData.Open()
>
> Dim strSQL As String
>
> strSQL = "SELECT ProgramID, ProgramName" & _
> " FROM dbo.vw_StaffPrograms" & _
> " WHERE StaffID = " & intStaffID.ToString
>
> Dim cmdSel As SqlCommand = New SqlCommand(strSQL, conData)
> Dim dtr As SqlDataReader = cmdSel.ExecuteReader()
> Return dtr
> End Function
>
>

"Mark Rae [MVP]" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> "David C" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>
>> I would like to do something different when it returns more than 1
>> record. Can I do this here or back at the code where I call this class
>> function?
>
> Neither. A DataReader is not the same as a DataTable. With a DataReader,
> all you can do is read from beginning to end, and you can only do that
> once. This makes it much more efficient in certain circumstances than a
> DataTable, but the trade-off is the lack of support for functionality like
> that which you want here. In fact, in early versions of .NET DataReaders
> didn't even have a .HasRows property - essentially, you had to trap the
> error caused by the .Read() method...
>
> You could, I suppose, read all of the records out of the DataReader into
> another type of storage, but this will almost certainly be very
> inefficient.
>
> I'd simply use a DataTable. Then you can examine its Rows.Count property,
> move backwards as well as forwards through its records, read it as many
> times as you like etc...
>
> DataReader vs DataTable / DataSet is an interesting debate...
>
>
> --
> Mark Rae
> ASP.NET MVP
> http://www.markrae.net

What if I used something like below at the "calling" page, where ddl =
DropDownList control?

If ddl.Items.Count > 1 Then
ddl.Items.Insert(0, New ListItem("", "0"))
ddl.SelectedValue = "0"
End If

Thanks.
David

"David C" <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> I have the code below that I use to fill a DropDownList control. I
> would like to do something different when it returns more than 1
> record. Can I do this here or back at the code where I call this
> class function? Thanks.

You can get record count first and then run the DataReader. Or, you can
curse through all of the records and get a record count. Or, you can use a
DataTable instead. But you cannot get the count at the beginning of a
DataReader, as it is a stream, not a recordset.

Peace and Grace,

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com


*******************************************
| Think outside the box! |
*******************************************

"David C" <(E-Mail Removed)> wrote in
news:Ohve$(E-Mail Removed):

>
> "Mark Rae [MVP]" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> "David C" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>
>>> I would like to do something different when it returns more than 1
>>> record. Can I do this here or back at the code where I call this
>>> class function?
>>
>> Neither. A DataReader is not the same as a DataTable. With a
>> DataReader, all you can do is read from beginning to end, and you can
>> only do that once. This makes it much more efficient in certain
>> circumstances than a DataTable, but the trade-off is the lack of
>> support for functionality like that which you want here. In fact, in
>> early versions of .NET DataReaders didn't even have a .HasRows
>> property - essentially, you had to trap the error caused by the
>> .Read() method...
>>
>> You could, I suppose, read all of the records out of the DataReader
>> into another type of storage, but this will almost certainly be very
>> inefficient.
>>
>> I'd simply use a DataTable. Then you can examine its Rows.Count
>> property, move backwards as well as forwards through its records,
>> read it as many times as you like etc...
>>
>> DataReader vs DataTable / DataSet is an interesting debate...
>>
>>
>> --
>> Mark Rae
>> ASP.NET MVP
>> http://www.markrae.net
>
> What if I used something like below at the "calling" page, where ddl =
> DropDownList control?
>
> If ddl.Items.Count > 1 Then
> ddl.Items.Insert(0, New ListItem("", "0"))
> ddl.SelectedValue = "0"
> End If
>

Yes, that would be another option, leaving:

1. Use a DataTable
2. Select the Count before instantiating the reader
3. Run through a reader twice
4. Insert value in DropDownList if count is greater than 1

But you are solving the "real" problem, which is not what you stated the
problem was in your question. It is still a useful exercise, as asking
led you to the real problem and removed the confusion of mixing the
problem with a proposed solution.

peace and grace,



--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************

"Mark Rae [MVP]" <(E-Mail Removed)> wrote in news:uW0ZUTeQKHA.4028
@TK2MSFTNGP05.phx.gbl:

>
>> Or, you can curse through all of the records and get a record count.
>
> Yes, but then the DataReader itself is no longer of any use...

Actually, I was trying to point out that you had to build it twice to get a
count, which is a waste, but I guess I got a big FAIL on illustrating that.
;-)

Peace and Grace,

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************

"Gregory A. Beamer" <(E-Mail Removed)> wrote in message
news:Xns9C966953C8595gbworld@207.46.248.16...
> "David C" <(E-Mail Removed)> wrote in
> news:(E-Mail Removed):
>
>> I have the code below that I use to fill a DropDownList control. I
>> would like to do something different when it returns more than 1
>> record. Can I do this here or back at the code where I call this
>> class function? Thanks.
>
> You can get record count first and then run the DataReader. Or, you can
> curse through all of the records and get a record count. Or, you can use a
> DataTable instead. But you cannot get the count at the beginning of a
> DataReader, as it is a stream, not a recordset.
>
> Peace and Grace,
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
>
> Twitter: @gbworld
> Blog: http://gregorybeamer.spaces.live.com
>
>
> *******************************************
> | Think outside the box! |
> *******************************************

Yes, I am sorry for the confusion. I will need both situations in the web
project. Sometimes I need to do something at the (App_Code) class level and
sometimes at the page code-behind due to different circumstances. I would
guess that using a DataTable would allow me to do what I need at either
location. Am I correct? Thank you all.

David

"David C" <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> Yes, I am sorry for the confusion. I will need both situations in the
> web project. Sometimes I need to do something at the (App_Code) class
> level and sometimes at the page code-behind due to different
> circumstances. I would guess that using a DataTable would allow me to
> do what I need at either location. Am I correct? Thank you all.

If you want a count attached at all times, you need to move to a
construct that keeps the count. A DataTable works, if you are using
DataSets. You can also choose to use LINQ to SQL, which can defer the
actual execution. Entity Framework is another choice.

The end story is that the DataReader is simply a stream, or to use a
database term, a firehose cursor. It is opened up and you pull items
from the stream much like a queue. Once you have pulled everything, you
can count what you pulled. Until then, the system is unaware of what is
there.

Underneath the hood, a DataSet's DataTable is filled using a DataReader,
which kind of gives you a hint at the relationship.

Peace and Grace,


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************