Search Search

#1 worldwide
FREE Coding Lessons
since 1996
   THE BEST WAY to learn ASP & Asp.net!
Advertise Here!
click for details 		Credits Host:
DiscountASP.net 		Server Admin:
The "Team" 		Contact Info.
Charles M. Carroll
my Blog
[prev. Lesson]  Subroutine: Working with Dates #2
     [next Lesson]  Subroutine: Query2List

Query2Table by Charles Carroll

Subroutines can save you having to repeat blocks of code. This code illustrates how we can build tables with minimal code in the main script and by including a file with a convenient subroutine.

   filename=/learn/test/subdbtable.asp

<Test Script Below>


<HEAD><TITLE>subdbtable.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<%
myDSN="DSN=student;uid=student;pwd=magic"
mySQL="select * from publishers"
call query2table(mySQL,myDSN)
%>
<!--#include virtual="/learn/test/lib_dbtable.asp"-->
</BODY></HTML>

   filename=/learn/test/subdbtable2.asp

<Test Script Below>


<HEAD><TITLE>subdbtable2.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<%
myDSN="DSN=student;uid=student;pwd=magic"
mySQL="select * from titles"
call query2table(mySQL,myDSN)
%>
<!--#include virtual="/learn/test/lib_dbtable.asp"-->
</BODY></HTML>

   filename=/learn/test/subdbtable3.asp

<Test Script Below>


<HEAD><TITLE>subdbtable3.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<%
myDSN="DSN=student;uid=student;pwd=magic"
mySQL="select * from authors"
call query2table(mySQL,myDSN)
%>
<!--#include virtual="/learn/test/lib_dbtable.asp"-->
</BODY></HTML>

   filename=/learn/test/subdbtable4.asp

<Test Script Below>


<HEAD><TITLE>subdbtable4.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<%
myDSN="DSN=student;uid=student;pwd=magic"
mySQL= "SELECT Publishers.Name, Titles.Title "
mySQL= mySQL & "FROM Publishers "
mySQL= mySQL & "INNER JOIN Titles ON Publishers.PubID = Titles.PubID "
mySQL= mySQL & "order by Name"

call query2table(mySQL,myDSN)
%>
<!--#include virtual="/learn/test/lib_dbtable.asp"-->
</BODY></HTML>

   filename=/learn/test/subdbtable5.asp

<Test Script Below>


<HEAD><TITLE>subdbtable5.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<%
myDSN="DSN=student;uid=student;pwd=magic"
mySQL="select * from title_author"
call query2table(mySQL, myDSN)
%>
<!--#include virtual="/learn/test/lib_dbtable.asp"-->
</BODY></HTML>

   filename=/learn/test/subdbtable6.asp

<Test Script Below>


<HEAD><TITLE>subdbtable6.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<%
myDSN="DSN=student;uid=student;pwd=magic"
mySQL="select name,type from sysobjects"
call query2table(mySQL,myDSN)
%>
<!--#include virtual="/learn/test/lib_dbtable.asp"-->
</BODY></HTML>

The Include file lib_dbtable.asp looks like this:

   filename=/learn/test/lib_dbtable.asp

<Test Script Below>


<% 
sub query2table(inputquery, inputDSN)
    dim conntemp, rstemp
    set conntemp=server.createobject("adodb.connection")
    conntemp.open inputDSN
    set rstemp=conntemp.execute(inputquery)
    howmanyfields=rstemp.fields.count -1%>
    <table border=1><tr>
    <% 'Put Headings On The Table of Field Names
    for i=0 to howmanyfields %>
            <td><b><%=rstemp(i).name%></B></TD>
    <% next %>
    </tr>
    <% ' Now lets grab all the records
    do while not rstemp.eof %>
        <tr>
        <% for i = 0 to howmanyfields
            thisvalue=rstemp(i)
            If isnull(thisvalue) then
                thisvalue="&nbsp;"
            end if%>
            <td valign=top><%=thisvalue%></td>
        <% next %>
        </tr>
        <%rstemp.movenext
    loop%>
    </table>
    <%
    rstemp.close
    set rstemp=nothing
    conntemp.close
    set conntemp=nothing
end sub%>

There are many worthy charities!!. But perhaps help starving children in Africa or South America AND help Charles too. a $5 tip buys him lunch at McDonalds, a $20 tip buys his kid Hitoshi a new computer game, a $39 tip buys his daughter Michiko a few nice outfits. See our donor list.