Datareader question

[oddball]

Is it possible to return all values from a table using a datareader, then select the values displayed in a bound control using another pulldownmenu, without requerying the database. At the moment every time i change the category dropdown menu selection it has to reload the page. Can't i just select the values from the original datareader results provided on Page_Load event. Heres what i have at the moment:

===========================================================
Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)

' Obtain ProductDefinition information from ProductDefinition table
' and bind to the datareader control
Dim products As New ASPNetPortal.ProductsDB()

' DataBind ProductDefinition to DataReader Control
ProductList.DataSource = products.GetAllProductDefinitions()
ProductList.DataValueField = "ProductDefinitionID"
ProductList.DataTextField = "Description"
ProductList.DataBind()

End Sub

Sub Index_Changed(ByVal sender As Object, ByVal E As EventArgs)

' Obtain ProductDefinition information from ProductDefinition table
' and bind to the datareader control
Dim products As New ASPNetPortal.ProductsDB()
Dim strCategoryID = ProductCategory.SelectedItem.Value

' DataBind ProductDefinition to DataReader Control
ProductList.DataSource = products.GetProductDefinitions(strCategoryID)
ProductList.DataValueField = "ProductDefinitionID"
ProductList.DataTextField = "Description"
ProductList.DataBind()

End Sub
:
:
:
:
<aspropDownList id=&quot;ProductCategory&quot; OnSelectedIndexChanged=&quot;Index_Changed&quot; AutoPostBack=&quot;true&quot; runat=&quot;server&quot; CssClass=&quot;Normal&quot;>
<asp:ListItem Value=&quot;1&quot;>Category1</asp:ListItem>
<asp:ListItem Value=&quot;2&quot;>Category2</asp:ListItem>
<asp:ListItem Value=&quot;3&quot;>Category3</asp:ListItem>
<asp:ListItem Value=&quot;4&quot;>Category4</asp:ListItem>
<asp:ListItem Value=&quot;5&quot;>Category5</asp:ListItem>
</aspropDownList>
:
:
:
<asp:ListBox id=&quot;ProductList&quot; selectionmode=&quot;Multiple&quot; width=&quot;250&quot; Rows=&quot;10&quot; runat=&quot;server&quot; class=&quot;Normal&quot; />

===========================================================

Many thanks in advance,

si

 

[link9]

Definitely not with a datareader. It's a single shot firehose cursor over data. Once you're at the end, that's it. Sure, you could stick it in a session variable (which isn't good idea for anything aside of a simple variable -- and even then, only sparingly), but it would be completely useless when you retrieved it.

The only way that I could think of would be to serialize your results to an xml file using the dataset's .writeXML method, which you could then reload the dataset from and requery that w/o hitting the database again.

But for simple dropdown list type data (two columns, probably not that many rows), the tax on the database really isn't that high for just getting the data again.

Plus if you do drop it out to xml, you have the added admin task of making sure those files get deleted in a timely manner so you don't waste too much disk space storing them.

On the other hand, data access via xml-->datareader is very fast, so if you wrote an xml file for each of your dropdowns and used those files for every request, then you'd never have to hit the database for that data. Just have to remember to update them if your data changed for some reason. That might not be a bad option if you're just flat out against going to the db every time.

Check out the docs and search google for examples of how to use the dataset with xml. There are a ton of great examples out there.


paul

 

[oddball]

The performance hit of reconnecting to the database is only part of a problem. My dropdownlist is near the bootom of my now very long registration page, so that on reloading it goes right back to the top, requiring the user to scroll down again.

Can i prevent this?

cheers,

si

 

[link9]

The basic working is:
on body unLoad, you fire a client side function, which populates a hidden form var, thePosition with the value: document.body.scrollTop

Then, onLoad, you just fire an event that sets that same property -- and voila. Works in Nutscrape, too.

<%
dim thePosition as string
thePosition = request.form(&quot;thePosition&quot
if thePosition = &quot;&quot; then
thePosition = &quot;0&quot;
end if
%>
<script language=javascript>
function setScrollUnload(){
document.forms[0].thePosition.value = document.body.scrollTop;
}
function setScrollLoad(){
document.body.scrollTop = <%=thePosition%>;
}
</script>
<body onLoad=&quot;setScrollLoad();&quot; onUnload=&quot;setScrollUnload();&quot;>

~~~~
<input type=hidden name=thePosition>
~~~~

It ain't very pretty, but it does work.


paul

 

[oddball]

Cheers paul