Appendix B: Advanced Dynamic Tables
Another use of tables.
Active Server Pages allow you to do some pretty interesting things.
Say you want to be able to view the contents of any table within your database dynamically.
The following example requires 3 pages: table chooser, table viewer, record viewer:
Table Chooser
Source (frmChooser.asp):
<body>
<form method="POST" action="frmViewTbl.asp">
<table border="1">
<tr>
<td>Choose a table from the following</td>
</tr>
<tr>
<td>
<select name="tbl" size="1">
<option
value="DAT_Customers">DAT_Customers</option>
<option
value="DAT_Orders">DAT_Orders</option>
<option
value="DAT_Suppliers">DAT_Suppliers</option>
<option
value="DAT_Shippers">DAT_Shippers</option>
</select>
</td>
</tr>
<tr>
<td>
<input type="submit" value="Submit"
name="B1">
<input type="reset" value="Reset"
name="B2">
</td>
</tr>
</table>
</form>
</body>
Sample output:
Table Viewer
Source (frmTblView.asp):
<!--#include virtual="/includes/adovbs.inc"-->
<!--#include virtual="/includes/subConn.inc"-->
<%
sql = "SELECT * FROM DAT_Orders ORDER BY CustomerID"
openCN
rs.Open sql, cn
FieldCount=rs.fields.count-1
if FieldCount > 5 then
FieldCount=4
end if
%>
<html>
<head>
<title></title>
</head>
<body>
<form action="frmChooser.asp" method="post">
<table border="1">
<tr>
<%for i=0 to FieldCount%>
<td><%=rs.fields(i).name%></td>
<%next%>
</tr>
<%
if rs.RecordCount > 10 then
max=9
else
max=(rs.RecordCount)-1
end if
for j=0 to max
%>
<tr>
<%for i=0 to FieldCount%>
<td><nobr><%if i = 0 then%>
<a href="frmFull.asp?tbl=<%=tbl%>&rec=<%=rs.fields(i).value%>">
<%end if%><%=rs.fields(i).value%><%if i = 0 then%></a>
<%end if%></nobr></td>
<%next%>
</tr>
<%
rs.MoveNext
next
rs.Close
CloseCN
%>
<tr>
<td colspan="<%=FieldCount + 1%>">
<input type="submit" value="Select Table">
</td>
</tr>
</table>
</form>
</body>
</html>
Sample output:
Record Viewer
Source (frmRecView.asp):
<!--#include virtual="/includes/subConn.inc"-->
<%
tbl=request.querystring("tbl")
rec=request.querystring("rec")
openCN
'get first field name
sql = "SELECT * FROM " & tbl
rs.Open sql, cn
fld=rs.fields(0).name
rs.Close
sql="SELECT * FROM " & tbl & " WHERE "
if isNumeric(rec) then
sql=sql & fld & "=" & rec
else
sql=sql & fld & "='" & rec & "'"
end if
rs.Open sql, cn, adOpenKeyset
FieldCount=rs.fields.count-1
%>
<body>
<form action="frmTblView.asp" method="post">
<input type="hidden" name="tbl" value="<%=tbl%>">
<table border="1">
<%for i=0 to FieldCount%>
<tr>
<td align="right"><%=rs.fields(i).name
& ":"%></td>
<td><%=rs.fields(i).value%></td>
</tr>
<%
next
CloseCN
%>
<tr>
<td colspan="2" align="center">
<input type="button"
onclick="window.location.href="frmChooser.asp""
value="Select Table">
<input type="submit" value="View Table"></td>
</tr>
</table>
</form>
</body>
Sample output:
|