Custom Formatted Database TablesBy Matt Smith
Yes, this is another article on how to display the results from a database in 'newspaper-style'
columns. Josh Hurwitz started with his article, Formatting Database
Results, which showed how to display records horizontally (from left-to-right and top-to-bottom).
Claude Rubinson followed up with his article,
Formatting Database Results into Newspaper-Style Columns,
which showed how to display records vertically (from top-to-bottom and left-to-right).
Both articles have the flexibility to allow a variable number of columns set by the user (or parameters),
however, both authors approach the task of correctly populating the HTML table differently.
mod to display the records horizontally and Claude
uses multi-dimensional arrays to display the records vertically. This article builds
on the flexibility of their code, provides another approach for displaying
the records that works for either horizontal or vertical and addresses
the limitations that Claude points out in his code.
The main reason I started playing around with this is because I hate
arrays, especially multi-dimensional arrays, and when I was reading Claude's article I
thought I could see a way of achieving the same results with only the
recordset object and some
For...Next loops. It took me a little while,
but I was able to do it. Of course, by that time, my cube-mate, Brian Moeskau, had
noticed that I wasn't doing any real work and wanted to know what
I was working on. I showed him and as always, he wanted to take my
efforts up a notch: "How about adding the ability to specify whether to
display the records horizontally or vertically?" he asked. Well, since I
already spent this much time on it... I finished that task and then it was
"Can you add the ability to specify whether it is the number of columns or
number of rows that is being passed in?" So, with a little input from both
us we came up with the code below. Of course neither of us has any real
use for the code but we enjoyed the challenge.
So what we ended up with was an ASP page that would custom format results from a database into an HTML table based on three parameters: Size, Orientation, and LayoutType. Size is the number of rows or columns (based on LayoutType) in the HTML table. Orientation is whether the results should be displayed (H)orizontally across the table or (V)ertically down the table. LayoutType is whether the Size refers to the number of (R)ows or (C)olumns in the HTML table. Take a look at the examples below or try it yourself and then check out the code.Examples
Size = 5
Size = 5
Size = 5
Size = 5
This first segment of code sets up my constants and variables. Nothing too terribly difficult so far. I did not include
ADOVBS.INC(see ADOVBS.inc... use it!) because I only needed two values:
adCmdText. Everything is named decently except
strLayoutTypeactually determines whether
intSizerefers to rows or columns. Semantics aside, we just need two variables to hold the dimensions of our table: one that is passed in through parameters and the other that is set through logic. Don't forget to include
Option Explicit) so that variable naming doesn't trip you up.
We will use a recordset object (Talking to your Database: The Recordset Object) for working with our records from the database. I don't use a connection object but instead define the active connection for the recordset. I can't currently run OLEDB (OLEDB For Me) on my host so that is why I have two different connection strings. I recommend OLEDB but a standard ODBC connection works just fine for this example. I also use a DSN-less connection (System DSN or DSN-less Connection?) because it allows me to move my code from my PC to our intranet server to my host without creating DSNs everywhere. I also like DSN-less connections because it gets me around that annoying $4/month/DSN my host wants to charge for creating and maintaining a DSN. The SQL itself is pretty simple but if you are unfamilar with it then check out SQL, the Language of Databases. Then we want to open our recordset which executes the SQL. We open our connection with a static cursor (Recordset Cursors: Choose the Right Cursor for the Right Job) because we need to move around our recordset.
In this part, we will set all of our variables to get
ready for the real work in the next segment. First, if there are no
records then we skip the majority of the code except at the very end where we
clean up and close our connection object. If there are records then let's
get the parameters passed to the file. It really doesn't matter when or
where you retrieve these values as long as you do it before they are needed.
Then, we get the number of records from out recordset object (How not to count
records returned in a Recordset Object). If Size was not passed in
then we will go ahead and set size to
intNumRecs. That way, if no
parameters are passed in, the results will be returned on separate rows.
intSize specifies the size of one
dimension of our table, we then divide
the other dimension of our table. If the remainder is not zero then
we need to increase
intOtherSize by one. Then we just assign
intNumCols appropriately based on the
PrintRecord writes out the contents of each cell in the table. The first check is to avoid having an intPosition that is greater than intNumRecs. This situation happens in the extra cells when printing vertically so we just output a blank cell. Otherwise, let's go ahead and get our record using AbsolutePosition. If it is empty then write out a blank cell. If not empty, write out the value in the cell.
Enhancing the System
Alert reader Snow H. wrote in asking if it was possible to have the output broken up into multiple "pages." That is, if we had 500 records, we would like to be able to show five pages of 100 records each, with Next and Previous buttons. I decided to implement this approach; you can see an example and download the code from http://www.smitty.net/matt/asp/table/paging.asp.
There you have it. Hope it makes sense and that you can find some use for it.