DataReader Limitation?

[Martin Feuersteiner]

Dear Group

I've created a DataReader to populate a dropdown box.
It works fine until I would like to use the DataReader result to poulate a
second drop-down box on the same form

e.g. like this:
Dim str1 As String = "EXEC sp_MyDataReader"
Dim cmd1 As New SqlClient.SqlCommand(str1, SQLConnection)
Dim MyDataReader As SqlClient.SqlDataReader = cmd1.ExecuteReader()

SearchDrop1.DataSource = MyDataReader
SearchDrop1.DataTextField = "LabelTransactionDate"
SearchDrop1.DataValueField = "TransactionDate"
SearchDrop1.DataBind()

SearchDrop2.DataSource = MyDataReader
SearchDrop2.DataTextField = "LabelTransactionDate"
SearchDrop2.DataValueField = "TransactionDate"
SearchDrop2.DataBind()

MyDataReader.Close()

When executing this code, both drop-down boxes remain empty. Why? Any work
arounds?

Thanks for your time & efforts!

Martin

 

[Frans Bouma [C# MVP]]

I've created a DataReader to populate a dropdown box.
It works fine until I would like to use the DataReader result to poulate
a second drop-down box on the same form

e.g. like this:
Dim str1 As String = "EXEC sp_MyDataReader"
Dim cmd1 As New SqlClient.SqlCommand(str1, SQLConnection)
Dim MyDataReader As SqlClient.SqlDataReader = cmd1.ExecuteReader()

SearchDrop1.DataSource = MyDataReader
SearchDrop1.DataTextField = "LabelTransactionDate"
SearchDrop1.DataValueField = "TransactionDate"
SearchDrop1.DataBind()

SearchDrop2.DataSource = MyDataReader
SearchDrop2.DataTextField = "LabelTransactionDate"
SearchDrop2.DataValueField = "TransactionDate"
SearchDrop2.DataBind()

MyDataReader.Close()

When executing this code, both drop-down boxes remain empty. Why? Any
work arounds?

A datareader is a forward only cursor. This means that once the
reader has processed the data, you can't rewind and start over. If you
want to populate multiple controls with the same data, fill a datatable
and bind that to both controls, or better: create 2 dataviews and bind
those to the different controls

Frans.

 

[Cor Ligthert]

Hi Martin,

A datareader is no datasource, it gives a piece of data, so you can use it
to fill a dropdownlist item by item adding it while reading. And that can as
well for one or for more controls, if you first save the item to values
while reading.

The other way to create a datatable and use bindings as Frans stated.

I think that the question what is better is a matter of preference in your
situation.
(Although I almost am forever for a dataset I think here the reader can do a
good job)

I hope this helps?

Cor

 

[William Ryan eMVP]

Martin:

On the winforms version, you can't bind to a DataReader at all like you can
in ASP.NET which tells you something... I'd consider walking the reader
once, and adding the values to a ArrayList or something, and binding to it.
Depending on your needs, your choice of object may change. However, you can
bind 9000000000 things to this arraylist/other object once you've loaded it
so it may be more practical. DataReaders only work with a DB with an Open
and available connection. You can call execute reader again and do the same
binding, but it's wasteful. I'd use a DataTable in this instance, or add
the values to some other data object with IList and bind to it.

HTH,

Bill

 

[Cor Ligthert]

Hi Bill,

I do not mind if you correct me directly, however this message I did also
understand.

I did not know that there was a build in datasource on an asp control.
(Looks a little bit weird to me)

And next time I will do the answer in the correct way.
(I tried it before I wrote this message of course)

Thanks you,

Cor

 

[Cor Ligthert]

Hi Martin,

The same question came in the aspnet newsgroup, I thought it was again from
you.

I made a code sample how to do it not using the datatable.

I hope this helps?

Cor
\\\
Dim rdr As SqlDataReader
rdr = cmd.ExecuteReader()
While rdr.Read()
Dim item As New ListItem
item.Text = rdr.GetInt32(0).ToString 'for an integer value from the
database
item.Value = rdr.GetString(1) 'for a string value from the database
DropDownList1.Items.Add(item)
DropDownList2.Items.Add(item)
End While
///

 

[Cor Ligthert]

Hi William,

I see now that my thinking was wrong however my answer was not.

I supossed without reading that I had done that.

Cor

 

[William Ryan eMVP]

Cor:

That's a canned post I have that I just use and modify the text slightly. I
use OE and a lot of time don't see the updated posts b/c I don't refresh all
the time... wasn't trying to step on your toes ;-). It's a bad habit of
mine and I need to do refreshes more often.

Bill

 

[Cor Ligthert]

Hi Bill,

... wasn't trying to step on your toes ;-).

You did not at all, I was very happy with your message.

I datareader which is a datasource, I mis something, however maybe the same
as a stream which is an image, that is too something I cannot understand.

Cor