The Web Report Database Reporting Tool
by Laruen Hightower



Listing One
<%
    Set DBConnection = Server.CreateObject("ADODB.Connection")
    DBConnection.Open ("northwind")
    SQL="SELECT name FROM MSysObjects WHERE type=1 AND name 
                                            NOT LIKE 'MSys%' ORDER BY name"
    Set DBTables = DBConnection.Execute(SQL)
%>

Listing Two
            <select name="tables" size=10 onClick=dispCols();>
            <% 
                Do While Not DBTables.EOF 
            %>
                <option> <%=DBTables("Name")%>
            <%
                DBTables.MoveNext
                Loop
            %>
            </select>


Listing Three
<select name="tables" size=10 onClick=dispCols();>
    <option> Categories
    <option> Customers
    <option> Employees
    <option> Order Details
    <option> Orders
    <option> Products
    <option> Shippers
    <option> Suppliers
</select>


Listing Four
function dispCols() 
    {
        var selIx=document.dbLists.tables.selectedIndex;
        var optText=document.dbLists.tables[selIx].text;
        for (var y=0;y<document.dbLists.tablecols.length;y++) 
        {
            document.dbLists.tablecols[y].text='';
        }
        <% 
            DBTables.MoveFirst
            Do While Not DBTables.EOF 
            SQL="SELECT * FROM [" & DBTables("Name") & "]"
            Set DBFields = DBConnection.Execute(SQL)
        %>
        if (optText=='<%=DBTables("Name")%>')
        {
           <%For i = 0 to DBFields.Fields.Count - 1%>
               document.dbLists.tablecols[<%= CStr(i) %>].text='
                                                     <%=DBFields(i).Name%>';
            <%Next%>
        }
        <%
            DBTables.MoveNext
            Loop
            DBFields.Close
        %>
    }

Listing Five
function dispCols() 
    {
        var selIx=document.dbLists.tables.selectedIndex;
        var optText=document.dbLists.tables[selIx].text;
    
        for (var y=0;y<document.dbLists.tablecols.length;y++) 
        {
            document.dbLists.tablecols[y].text='';
        }
        if (optText=='Categories')
        {
                document.dbLists.tablecols[0].text='CategoryID';
                document.dbLists.tablecols[1].text='CategoryName';
                document.dbLists.tablecols[2].text='Description';
                document.dbLists.tablecols[3].text='Picture';
        }


Listing Six
<html>
<head><title>The Web Report Results</title>

<STYLE type=text/css>
 .Header
    { 
        position : absolute;
        left: 25;
        top: 15;
    }
 .BodyText
    { 
        position: absolute;
        left=25;
        top=100;
    }
</STYLE>
</head>

<body bgcolor=#FFFFFF>
<div class="Header">
    <img src=header.gif>
</div>

<div class="BodyText">
<table border=1>
    <tr>
        <%For i=0 to DBResults.Fields.Count - 1%>
            <td align=center bgcolor=#003366>
                <p style="color:#FFFFFF;background:#003366;font-family:arial;
                        font-size=12pt;font-weight:bold"><%= 
                        DBResults(i).Name %></style>
            </td>
        <%Next%>
    </tr>
<% 
    j=0
    Do Until DBResults.EOF or j=100
%>
    <tr>
        <%For i=0 to DBResults.Fields.Count - 1%>
            <td valign=top>
                <p style="color:#000000;background:#FFFFFF;font-family:arial;
                            font-size=10pt"><%= CStr(DBResults(i)) %></style>
            </td>
        <%Next%>
    </tr>   
<% 
    DBResults.MoveNext
    j=j+1   
    Loop
    DBResults.Close
    DBConnection.Close
%>
</table>

<p style="color:RED;background:#FFFFFF;font-family:arial;font-size=10pt;
                                       font-weight:bold"><%= SQL %></style>
<%If j>99 Then%>
    <p style="color:RED;background:#FFFFFF;font-family:arial;font-size=10pt;
                  font-weight:bold">For demonstration purposes, 
                  this version of The Web Report returns only 100 
                  records of large recordsets.</style>
<%End if%>
<p>
</div>

</body>
</html> 


4


