|
|
| Databases 101 | | | ASP.NET Chapter | | | Suggest Article | | | Discussions | | | Bookstore | | | Write for Us | | | OpenAuction | | | Our Authors | |
|
|
|
 Image & Video Software
Know your Web users
Find a Consultant
Database Web Tools
Web System Integrator
Internet Intelligence
IT Solutions
Add Chat To My Site
Add Online Sales Reps
Volume Licensing
|
 |
|
| Caching Select-Box Data | | by Pete Nelson | | | Skill level: Beginner | | | First posted: Thursday, May 11, 2000 | |  | | | | Overview
A common technique used by developers is to store select-box data in a table in a database, then generate the select-box from the database each time it is needed. Items such as a list of states, countries, shipping methods, etc are perfect examples. This sort of data is needed frequently but does not change very often. For this sort of "static" data, you can cache the data once and can bypass querying the server each time it is needed.
Generating Select-Boxes from Lookup Tables
I call any tables that are used in this manner Lookup Tables. For my own naming convention, I prefix the table with "LK_" so I know it's a lookup table. My LK_ShippingMethods table would look something like this:
Normal generation the select-box would generate would loop through a recordset and look like this:
<%
Dim recShipping
Set recShipping = Server.CreateObject("ADODB.Recordset")
recShipping.Source = "Select * from LK_ShippingMethods order by SortOrder"
recShipping.ActiveConnection = conDB
recShipping.Open
%>
<select name="ShippingMethod">
<%
do while not recShipping.EOF
%><option value="<% =recShipping("ID") %>"><% =Trim(recShipping("Description")) %></option>
<%
recShipping.MoveNext
loop
recShipping.Close
Set recShipping = Nothing
%>
</select>
|
and would generate HTML that looked like this:
<select name="ShippingMethod">
<option value="1">USPS Ground</option>
<option value="2">UPS 2-Day</option>
<option value="3">Fedex Overnight</option>
</select>
This makes generating and maintaining select-boxes a snap. But the drawback is that we have to query the database each we want to create a select-box from a table.
Caching the Data
The method I use to cache data for select-boxes is to store the data in an array, then store the array in an Application variable. You can then use a For...Next loop using the LBound and UBound of the array to loop through the data, rather than creating and looping through a recordset.
I populate my arrays in the Application_OnStart sub in Global.asa. First we get the same recordset as above, then dim an array to match the number of records returned by the query (determined by the RecordCount property). As we loop through the recordset, we use the AbsolutePosition property to determine which position in the array to assign the value. AbsolutePosition is a 1-based value, so we subtract 1 from it to match the 0-based array. We use a cursor-type of 1 (keyset) to get an accurate RecordCount property.
<%
Sub Application_OnStart()
'*** Add Code here to open the conDB connection object
Dim recShipping, arrShipping
Set recShipping = Server.CreateObject("ADODB.Recordset")
recShipping.Source = "Select * from LK_ShippingMethods order by SortOrder"
recShipping.ActiveConnection = conDB
recShipping.Open , , 1
Redim arrShipping(recShipping.RecordCount, 2)
Do While Not recShipping.EOF
'*** Put the ID in the first element
arrShipping(recShipping.AbsolutePosition - 1, 0) = recShipping("ID")
'*** Put the Description in the second element
arrShipping(recShipping.AbsolutePosition - 1, 1) = Trim(recShipping("Description"))
recShipping.MoveNext
Loop
recShipping.Close
Set recShipping = Nothing
Application("arrShipping") = arrShipping
End Sub
%>
|
Now when we need to generate a select-box for this data, we already have everything in an Application variable. Just do a For...Next loop to generate the select-box.
<%
Dim arrShipping, x
arrShipping = Application("arrShipping")
%>
<select name="ShippingMethod">
<%
For x = LBound(arrShipping, 1) to UBound(arrShipping, 1) - 1
%><option value="<% =arrShipping(x, 0) %>"><% =arrShipping(x, 1) %></option>
<%
Next
%>
</select>
|
Say for example that you had a web page that had 4 select-boxes and was accessed 100 times each day. By caching the data for the select-boxes, that's 400 fewer queries and trips to the database.
|
| | | | |
This document is not listed in any Encyclopedia chapters.
|
|
|
|
This is what people have been saying about this article:
Create a new discussion |
|
|
|
View Article Statistics
Authors...
Edit this article
View Preview Version
|
|
| Printable Copy of Article |
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - |
|
|
