|
Worlds Fastest Listbox
w/Application Data
by Charles Carroll
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/freebook/asp/rsfast.aspx
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.
filename=/learn/test/ListMakedemo.asp
<HTML>
<TITLE>listmakedemo.asp</TITLE>
<body bgcolor="#FFFFFF">
<br>
City: <!--#include virtual="/learn/test/listcity.asp"-->
<br>
State: <!--#include virtual="/learn/test/liststate.asp"-->
<br>
Zip: <!--#include virtual="/learn/test/listzip.asp"-->
</BODY></html>
ListCity.asp displays listbox of
cities.
filename=/learn/test/ListCity.asp
<!--#include virtual="/learn/test/lib_listmake.asp"-->
<%
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
filename=/learn/test/ListState.asp
<!--#include virtual="/learn/test/lib_listmake.asp"-->
<%
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.
filename=/learn/test/ListZip.asp
<!--#include virtual="/learn/test/lib_listmake.asp"-->
<%
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.
filename=/learn/test/Lib_Listmake.asp
<%
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.
filename=/learn/test/ListMakeClear.asp
<%
application("list_city")=""
application("list_states")=""
application("list_zips")=""
%>
|