How many simple reports have you created? You know the kind: a query or stored procedure with several fields, filtered by a value or date, just listed on a Web page with titles—nothing more. Such reports have become ubiquitous on most corporate intranets. If you’ve ever wanted to whip those pages out in five minutes flat, this is your recipe.
The data layer
Even if you don’t use formal n-tier architecture, there will at least be a data component and a presentation component involved in this technique. For the data layer, we’ll use the example one-table SQL database holding contacts (Table A), with one stored procedure that makes an e-mail list.
Our single stored procedure will return all fields in a recordset—unfiltered, for simplicity:
Even if there were a larger number of fields, or a JOIN, or a complex WHERE clause, the result would be the same. A call to this stored procedure will result in a recordset with the fields we need for our report and nothing more.
The presentation layer
In our ASP file, we will need to open the datasource, get the recordset, and then build and display the list. Let’s take each step in turn.
Open the datasource
We’ll use a Command object to gain access to the stored procedure. Notice that since there are no input parameters, we call the procedure with empty parentheses:
Set cmdGetcontacts = Server.CreateObject(“ADODB.Command”)
.ActiveConnection = “DSN=CONTACTS”
.CommandText = “GetAllContacts()”
.CommandType = 4
Get the recordset
Now that we have a Command object, we can hand it to the recordset object:
Set rsContacts = Server.CreateObject(“ADODB.Recordset”)
.CursorLocation = 3
.Open cmdGetcontacts, , 2, 3
What we have essentially done here is built a copy of the table in a recordset. Although you can just call a table directly from ASP by setting your commandtype to 2, use of the stored procedure will allow us to directly specify the fields we need in the report. This will be very important.
Build the list
This is the neat part. We all know that the recordset object has a Fields collection, a significantly underused attribute containing all fields in the active row. We can use this collection to solve our reporting problem. Since we used a stored procedure specifically designed to produce this report, we actually want every field in every row on the page. ASP will allow us to nest loops to produce just this effect.
First, we’ll trap for an empty recordset:
if rsContacts.EOF then
strContactList = “<B>There is no data for the criteria you selected</B>”
Then, we’ll assign the header of the table to a variable:
strContactList = strContactList & “<TABLE class=reportSmall>”
strContactList = strContactList & “<TR>”
strContactList = strContactList & “<TH>First Name</TH>”
strContactList = strContactList & “<TH>Last Name</TH>”
strContactList = strContactList & “<TH>Email Address</TH>”
strContactList = strContactList & “</TR>"
Finally, we’ll loop through the rows and fields of the table to write the report:
do while not rsContacts.EOF
strTable = strTable & "<TR>"
for each oField in rsContacts.Fields
strRow = strRow & "<TD>" & oField.Value & "</TD>"
strTable = strTable & strRow
strTable = strTable & "</TR>" & vbCrLf
strContactList = strContactList & strTable & "</TABLE>"
Now the entire table is in the strContactList variable.
Display the list
This is the simplest code in the article. It appears after you have written the head and foot of your HTML page:
So what does this technique do for us?
- It provides us with an ASP file that will build a simple report with any properly formed stored procedure.
- It saves us the time of matching column names in our ASP code.
- It provides a template for more complex reporting.
It should be noted that this tool doesn’t allow for totaling at the bottom, and you still need to customize the file with the name of the SP and the titles of the columns. You could build the procedure better and use the Name property of the Field object to write the titles in the loop. Just the same, this is a slick way to generate a quick report.