2 dropdownlists using same datareader

[tshad]

Is there a way to databind a datareader to 2 different dropdownlists?
Something like:

StoredSearches.DataSource=objCmd.ExecuteReader
StoredSearches.DataValueField="SearchID"
StoredSearches.DataTextField= "SearchName"
StoredSearches.databind()
SearchList.DataSource=objCmd.ExecuteReader
SearchList.DataValueField="SearchID"
SearchList.DataTextField= "SearchName"
SearchList.databind()


I can't do the above as the second Execute reader gives me an error saying I
have to close the reader first - which would mean a re-read.

I want to do this as I only want to read the database once but show 2
different dropdownlist (in different ways depending on how the user selects
the page).

If I can't do this, can I just do:

Searchlist = StoredSearches

Thanks,

Tom

 

[Richard]

The behavior of ExecuteReader() is to execute the command and return an
IDataReader. An IDataReader can only be read through once.

Yes, you can bind SearchList to StoredSearches like so:

SearchList.DataSource = StoredSearch.Items
SearchList.DataTextField = "Text";
SearchList.DataValueField= "Value";
SearchList.DataBind();

 

[Steve C. Orr [MVP, MCSD]]

In addition to Richard's fine suggestion, you might want to return the
results in a DataTable (or DataSet) instead of a DataReader for improved
reusability. If the DataTable is used often you might store it in Session
or Application state to reduce round trips to the database and it will
always be within easy reach of your code.

 

[tshad]

In addition to Richard's fine suggestion, you might want to return the
results in a DataTable (or DataSet) instead of a DataReader for improved
reusability. If the DataTable is used often you might store it in Session
or Application state to reduce round trips to the database and it will
always be within easy reach of your code.

Both suggestions are what I was looking for.

I assume I could also do a dual select in my stored procedure to return 2
result sets and bind to the first dropdown list, do a nextresult(), and then
bind to the 2nd dropdown.

Thanks,

Tom