|
Display Table and Disconnect
Recordset by Charles Carroll
This page demonstrates the capabilities how to
display a table from a SQL statement. It illustrates not only how to display the table,
but also how to detect that no records were returned from a query, and how to detect null
and blank values in the data. This example disconnects the recordset so that the
data will not be drawn from up to the millisecond database data BUT retrieval
will be much faster.
filename=/learn/test/dbtabledisconnected.asp
<%@enablesessionstate=false%>
<%response.buffer=true%>
<!--#include virtual="/adovbs.inc"-->
<html><head>
<TITLE>dbtabledisconnected.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
' to disconnect a recordset it must be created explicitly
set rstemp=server.createobject("adodb.recordset")
rstemp.cursorlocation=aduseclient
rstemp.open mySQL,conntemp
' this line below disconnects the recordset
set rstemp.activeconnection=nothing
If rstemp.eof then
response.write "No records matched<br>"
response.write mySQL & "<br>So cannot make table..."
rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing
response.end
end if
response.write "<table border='1'><tr>"
'Put Headings On The Table of Field Names
for each whatever in rstemp.fields
response.write "<td><b>" & whatever.name & "</B></TD>"
next
response.write "</tr>"
DO UNTIL rstemp.eof
response.write "<tr>"
for each whatever in rstemp.fields
thisfield=whatever.value
if isnull(thisfield) then
thisfield=shownull
end if
if trim(thisfield)="" then
thisfield=showblank
end if
response.write "<td valign=top>" & thisfield & "</td>"
next
response.write "</tr>"
rstemp.movenext
LOOP
response.write "</table>"
rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing
%>
</body></html>
|