DataReader Limitation?

  • Thread starter Thread starter Martin Feuersteiner
  • Start date Start date
M

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
 
Martin Feuersteiner said:
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?
Click to expand...

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.
 
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
 
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
 
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
 
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
///
 
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
 
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
 
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
 
Back
Top