|
Table Database Display via
GetRows by Charles Carroll
This page demonstrates the capabilities how to
display a table from a SQL statement a very fast and scaleable way using a
recordset method called GetRows. GetRows that move many records and fields into
a memory array. Once in the array it is accessed very fast. If you read the code
closely you will notice it can free up the recordset and connection object
earlier than the traditional loop thus freeing up those resources for other
scripts.
The array fields are accessed by
number, a script at:
http://www.learnasp.com/freebook/asp/dbtablegetrowsnamed.aspx
shows how to combine the speed of getrows and simulate named fields using
dictionary objects.
In terms of why this is faster and
reduces server resource consumption,
read:
http://www.learnasp.com/advice/whygetrows.asp
to see an in-depth explanation.
http://www.learnasp.com/freebook/asp/dbtablegetstring.aspx
rips getrows to shreds speed-wise as the backend transfers one big string
instead of a complex array structure but formatting is SOOOOO limited (unless
you know Regexps like the back of your hand....)
"The Worlds Fastest Listbox"
http://www.learnasp.com/freebook/asp/speedappdata.aspx
shows the only faster way to display databases.
Since this code relies heavily on
arrays you may want to read up on them at:
filename=/learn/test/dbtablegetrows.asp
<%@enablesessionstate=false%>
<%response.buffer=true%>
<html><head>
<TITLE>dbtablegetrows.asp</TITLE>
</head>
<body bgcolor="#FFFFFF">
<%
' displays a database in table form via GetRows
myDSN="DSN=Student;uid=student;pwd=magic"
mySQL="select * from publishers where state='NY'"
showblank=" "
shownull="-null-"
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
set rstemp=conntemp.execute(mySQL)
If rstemp.eof then
response.write "No records matched<br>"
response.write mySQL & "<br>So cannot make table..."
Call CloseAll
response.end
end if
response.write "<table border=""1""><tr>" & vbcrlf
'Put Headings On The Table of Field Names
for each whatever in rstemp.fields
response.write "<td><b>" & whatever.name & "</b></td>" & vbcrlf
next
response.write "</tr>" & vbcrlf
' Now lets grab all the records
alldata=rstemp.getrows
Call CloseAll
if IsArray(alldata) then
numcols=ubound(alldata,1)
numrows=ubound(alldata,2)
FOR rowcounter= 0 TO numrows
response.write "<tr>" & vbcrlf
FOR colcounter=0 to numcols
thisfield=alldata(colcounter,rowcounter)
if isnull(thisfield) then
thisfield=shownull
end if
if trim(thisfield)="" then
thisfield=showblank
end if
response.write "<td valign=top>"
response.write thisfield
response.write "</td>" & vbcrlf
NEXT
response.write "</tr>" & vbcrlf
NEXT
response.write "</table>"
End If
%>
</body></html>
<%
SUB CloseAll
rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing
END SUB
%>
|