ASPkey - A Resource Site for Web Developers

	Services
	Portfolio
	Products
	Sample Code
	Quotes
	Blog
	Feedback
	Basket
	Checkout
	Tools
	.net Assemblies

Your basket has: Total items: 0
Subtotal: $0.00

Article
Report Maker

A request was made for an example script which illustrates the use of enabling a user with the ability to page through recordsets. Here is an axample which moves data from a database into a client-side array and provides a control panel which allows the user to page through the recordset, one record at a time. By putting the data into an array, excessive trips back to the server are minimized.

There is also an added feature which allows the developer to create a libray of predefined reports. The user can select the desired report from a droplist. Each report selection triggers a new query which submits to the server by having the page call itself.

<%@Language=VBScript

response.buffer=true

Response.AddHeader "cache-control", "private"
Response.AddHeader "pragma", "no-cache"
Response.ExpiresAbsolute = #January 1, 1990 00:00:01#
Response.Expires=Now()-1

'------------------------------------------------------------------
'Page calls back to itself so get name
'------------------------------------------------------------------

strScriptName = Request.ServerVariables("SCRIPT_NAME")

'------------------------------------------------------------------
'Connect to database and define tablename and key field
'------------------------------------------------------------------

conndb = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ= " & Server.MapPath("northwind.mdb")
tablename="orders"
key= "orderid"

'------------------------------------------------------------------
'Set first query equal to default selection for reports
'------------------------------------------------------------------

if Request("report") = "" then
recordID = "<= 10250"
else
recordID= Request("report")
end if

'------------------------------------------------------------------
'Get the recordset and move into client-side array
'------------------------------------------------------------------

Call MakeArray(conndb,tablename, recordID,key )

On Error Resume Next

Sub MakeArray(conndb,tablename,recordID,key)

dim ARR(), ARRrs()

    set rs = Server.CreateObject("ADODB.Recordset")
    SQL = "SELECT " & tablename & ".[RequiredDate]," & tablename & ".[OrderDate]," & tablename & ".[orderid]," & tablename & ".[customerid]," & tablename & ".[employeeid] FROM " & tablename & " WHERE " & tablename & ".[" & key & "] " & recordID

    rs.Open SQL, conndb, 3
    numrecords=rs.recordcount
    session("fieldcount")=rs.Fields.Count - 1

    If numrecords <= 0 then
        response.redirect "error.asp"
    end if

'------------------------------------------------------------------
'Use server to write out script commands
'------------------------------------------------------------------

response.write("<script language=vbscript>" & vbcrlf)
response.write("fieldnamecount = " & session("fieldcount") & vbcrlf)
response.write ("ARR = " & chr(34))

'------------------------------------------------------------------
'get field names from table
'------------------------------------------------------------------

For I = 0 to rs.Fields.Count - 1
    redim preserve ARR(I)
    ARR(I) = rs.Fields(I).Name
    If I=0 then
    myarray=myarray & rs.Fields(I).Name
    else
    myarray=myarray & " " & rs.Fields(I).Name
    end if
next

response.write(myarray)
response.write( chr(34) & vbcrlf)
response.write("</script>" & vbcrlf & vbcrlf)

'------------------------------------------------------------------
'Now lets grab all the records using rs.getrows
'Close and cleanup recordset as data is now in an array
'------------------------------------------------------------------

alldata=rs.getrows
rs.close
set rs=nothing

response.write("<script language=vbscript>" & vbcrlf)

numcols=ubound(alldata,1)
numrows=ubound(alldata,2)

response.write ("dim ARRrs(" & numrows & ")" & vbcrlf)
response.write ("numrows=" & numrows & vbcrlf)
response.write ("numcols=" & numcols & vbcrlf)

FOR rowcounter= 0 TO numrows
myrsarray=""
redim preserve ARRrs(rowcounter)
response.write("ARRrs(" & rowcounter & ") = " & chr(34))
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
    If colcounter=0 then
    myrsarray=myrsarray & thisfield
    else
    myrsarray=myrsarray & " " & thisfield
    end if

NEXT
response.write(myrsarray)
response.write(chr(34) & vbcrlf)
NEXT

response.write("</script>" & vbcrlf)

End Sub

'------------------------------------------------------------------
'Begin scripts to handle selection of record for viewing
'default is record 0 and button 1 (first)
'
'Case 2 and 3 relative positioning handled by "select case"
'
'Case 1 and 4 are handled directly from button tags since
'their boundary conditions are known
'------------------------------------------------------------------

%>

<Script Language="VBScript"></Script>

</HEAD>
<%

'------------------------------------------------------------------
'Create the control panel and buttons to scroll through data
'and create droplist for selectable reports
'------------------------------------------------------------------

%>
<HTML>
<TITLE>Report Script to View Records</TITLE>
<HEAD>
<BODY bgcolor="gray">

<DIV style="position: relative; left:450px; Top:10px;">

<Table border="3" bgcolor="silver"><TD>
    <TR><TD Align="center">Control Panel</TD></TR>
    <TR><TD>
        <Table border="10" ><TD bgcolor="black">
            <Button ID='button1' onClick='getrecord 0,1'>First</BUTTON>
            <Button ID='button2' onClick='getrecord document.myform.arraynumber.value-1,2'><<Prev</BUTTON>
            <Button ID='button3' onClick='getrecord document.myform.arraynumber.value+1,3'>Next>></BUTTON>
            <Button ID='button4' onClick='getrecord numrows,4'>Last</BUTTON>
        </TD></Table>
    </TD></TR>
    <TR><TD Align="center">
    <Form name="ReportForm" Action= "<%=strScriptName%>?Report=1" >
            Select report for viewing <BR>
                <center>
                <SELECT NAME="Report" SIZE="1">
<%

'------------------------------------------------------------------
'Set up the report selection list as an array value
'Dimension the array = number of defined reports -1
'because arrays are zero based
'------------------------------------------------------------------

dim ReportARR(1)

ReportARR(0)= "<=10250,first 3 records"
ReportARR(1)= "<=10275,first 28 records"

'------------------------------------------------------------------
'The selected value is set to your preference of defined reports
'the remainder of the list is generated from the array
'First condition checks for initial call to script
'------------------------------------------------------------------

if request("Report")="" then%>
                <OPTION value="<=10250" selected>first 3 records</OPTION>
<% for i = 1 to Ubound(ReportARR)
        ReportARRvalues=split(ReportARR(i),",")%>
                <OPTION value="<%=ReportARRvalues(0)%>"><%=ReportARRvalues(1)%></OPTION>
    <%next
end if

'------------------------------------------------------------------
'Subsequent calls for reports are used to select display value
'------------------------------------------------------------------

if request("Report")<>"" then
    for i = 0 to Ubound(ReportARR)
    ReportARRvalues=split(ReportARR(i),",")
        if request("Report")= ReportARRvalues(0) then%>
                <OPTION value="<%=ReportARRvalues(0)%>" selected><%=ReportARRvalues(1)%></OPTION>
        <%else %>
                <OPTION value="<%=ReportARRvalues(0)%>"><%=ReportARRvalues(1)%></OPTION>
        <%end if
    next
end if
%>

    </SELECT>
    <INPUT type="Submit" value="Get Report" name=SubmitReport>
<%

'------------------------------------------------------------------
'Setup form to view report output
'------------------------------------------------------------------

%>

</Form>
    </TD></TR>
</TD></Table>

</DIV>

<table border=1 style="POSITION: absolute; LEFT: 10px; TOP:10px">
<Form name ="myform">
<tr><td bgcolor='blue' width='140'><B>
<Input Type='text' size='20' value = 'Array #'>
</B></td>
<td bgcolor=#FFFFCC width='200'><B>
<input type="hiddden"size='30' name="arraynumber" value="0">
</B></td>
</tr>

<%
for I = 1 to session("fieldcount")
    response.write ("<tr><td bgcolor='blue' width='140'><B>")
    response.write ("<Input Type='text' size='20' name= 'fieldname' value = ''>" )
    response.write ("</B></td>")
    response.write ("<td bgcolor=#FFFFCC width='200'><B>")
    response.write ("<Input Type='text' size='30' name= 'fieldvalue' value = ''> ")
    response.write ("</B></td>")
    response.write ("</tr>")
next
%>

</Form>
</table></BODY></HTML>
</CENTER>

Hope you enjoy this script!

Return To Top

Article