Worlds Fastest Listbox w/Application Data

Worlds Fastest Listbox w/Application Data

--------------------------------------------------------------------------------

Description : Sometimes data (like a HTML list box) is displayed on many pages of a website. In fact, the database generated list box is displayed thousands of times a day, and the database is queried every time, but it is unnecessary. The database it is drawn from is not changing thousands of times a day. In the following example any page that displays the list boxes needs to only access the application variables, not hit the database. Very speedy. If the data changes or gains new records, a trigger mechanism could be added to sense data changes and only rebuild the list box if records were added or changed. These scripts is a "proof of concept" script that displays the listboxes without re-querying the database. If you like this approach, this is merely a "proof of concept" and "teaching example". A much more thorough implementation of caching and high speed data retrieval is at: http://www.learnasp.com/learn/rsfast.asp which is a very fast database retrieval library I made and add features and speed tweaks too constantly that supports caching mechanisms too. ListMakedemo.asp is the main script. Simple enough.

<HTML>
<TITLE>listmakedemo.asp</TITLE>
<body bgcolor="#FFFFFF">

City: 

State: 

Zip: 
</BODY></html>

ListCity.asp displays listbox of cities.


<%
If application("list_city")="" Then
myDSN="DSN=student;uid=student;pwd=magic"
mySQL="select distinct city from publishers"
application("list_city")=query2htmlist(mySQL,"cities",myDSN)
End If
Response.Write application("list_city")
%>

ListState.asp displays listbox of states


<%
If application("list_states")="" Then
myDSN="DSN=student;uid=student;pwd=magic"
mySQL="select distinct state from publishers"
application("list_states")=query2htmlist(mySQL,"state",myDSN)
End If
Response.Write application("list_states")
%>

ListZip.asp displays listbox ofzips.


<%
If application("list_zips")="" Then
myDSN="DSN=student;uid=student;pwd=magic"
mySQL="select distinct zip from publishers"
application("list_zips")=query2htmlist(mySQL,"zip",myDSN)
End If
Response.Write application("list_zips")
%>

Lib_Listmake.asp Is a library that makes it easier To make listboxes.

<%
Function query2htmList(myquery,myname,myDSN)
Dim conntemp, rstemp
Set conntemp=Server.CreateObject("adodb.connection")
conntemp.open myDSN
Set rstemp=conntemp.execute(myquery)
query2HTMlist="<Select name='" & myname & "'>"
Do until rstemp.eof
thisfield=Trim(RStemp(0))
If IsNull(thisfield) Or thisfield="" Then
' ignore
Else
query2HTMlist=query2HTMlist & "<option>" & thisfield & "</option>"
End If
rstemp.movenext
Loop
query2HTMlist=query2HTMlist & "</select>"
rstemp.close
Set rstemp=Nothing
conntemp.close
Set conntemp=Nothing
End Function
%>

ListMakeClear.asp Is a crude mechanism To force all the listboxes To refresh. It could be invoked On a timed basis (every 15 minutes For example) Or triggered by data-changes.

<%
application("list_city")=""
application("list_states")=""
application("list_zips")=""
%>