User Tips: Use GetRows to Speed Up Displaying "Skinny" Tables
By Pete D.
My name is Pete D. I work for a regional auditing company and do all
of our software and data-driven web development using mostly SQL Server,
ASP, and a little Access. We have a highly mobile staff and have
committed to using the intranet/internet for as much of our communications
and as many of our information needs as possible. Therefore, we have a
lot of web applications for a company our size. This article is about a
solution I worked out to display a table with multiple records per row
rather than with just one record per row. If you'd like, please send any
feedback to pdraigh@businessstrategy.com.
Have you ever had a "long, skinny table" such as Employee Name, Position
that you want to display in multiple columns like a newspaper instead of
in one long skinny column? The internet is loaded with examples of
displaying data from a database in a tabular format with one record per
row. But what if you have a two-field table that looks awkward in a
tabular display? It's easy to loop through, and list them in say, three
columns in order from left to right, top to bottom, but I wanted them in
order from top to bottom, left to right.
I had an alphabetical employee list that displayed about 120 employees'
names and their positions. Each employee's name is a link to their
biography and a photo stored as a jpeg on the server. The page just
looked bad as two columns with all the white space on either side. (For
speed, I didn't want to add any graphics to make it prettier.) I also
wanted the ability to change the number of columns to whatever looked good
without having it hardcoded to a specific number of columns.
I solved the problem using GetRows to move the recordset into an array.
Then, using a couple loops and a little logic, I bounce around the array,
filling in the table with elements from the array until I've displayed all
of the recordset. I can also easily change the number of columns by
changing the value of one constant. (For more information on GetRows
be sure to read: Optimizing Database Table Displays
through an ASP Page.)
<%Option Explicit
response.buffer=true%>
<html>
<head>
<title>Associate Biographies</title>
</head>
<h1 ALIGN="CENTER">Associate Biographies</h1>
<%
'--Declare variables
Dim Connect, SQLSTR, rstEmployees, arrEmployees, _
DisplayName, RowCounter, ColCounter, NumRows, NumRecords
'--Change this constant to tell it how many columns in which to display
the table
Const NumColumns = 2
'--Open connection to SQL Server database containing employee records
Set Connect=Server.CreateObject("ADODB.Connection")
Connect.Open "Provider=SQLOLEDB; Data Source=MyServer; Initial
Catalog=DatabaseName; User ID=sa; Password="
SQLSTR = "Select ID, firstname, lastname, position FROM table1 " & _
"ORDER by lastname, firstname"
'--Open recordset and then use GetRows to move it into the array
set rstEmployees = connect.execute(SQLSTR)
arrEmployees = rstEmployees.getrows
'--Close and terminate connection and recordset objects
rstEmployees.close
set rstEmployees = nothing
Connect.close
set Connect=nothing
%>
<p ALIGN="CENTER">Click the associate's name to view his/her biography and
picture.</p>
<table ALIGN="CENTER" BORDER="1" CELLPADDING="1" CELLSPACING="1">
<!-- Writes a set of column headings for each set of columns you've
specified
<THEAD>
<tr>
<%For colcounter = 1 to NumColumns%>
<th>Associate</th>
<th>Position</th>
<%next%>
</tr>
</THEAD>
<%
'-- Find out how many records are in the array (Add 1 since array
coordinate starts at 0)
NumRecords = ubound(arrEmployees,2) + 1
'--Calculates how many rows there should be based on the specified number
of columns
'--The last column will always the the short column if there is one
if NumRecords mod NumColumns = 0 then
NumRows = NumRecords\NumColumns
Else
NumRows = NumRecords\NumColumns + 1
End if
'--The outer loop walks down the rows
for RowCounter = 1 to NumRows
'--The inner loop steps across the columns
For ColCounter = 0 to NumColumns-1
if RowCounter + ColCounter * NumRows <= NumRecords then
'--Build the display name from the first and last name fields
DisplayName = arrEmployees(1, RowCounter + ColCounter * NumRows-1) & _
" " & arrEmployees(2, RowCounter + ColCounter * NumRows-1)
'--Write out the display name as a link to the detail
'--page and their position
response.write "<td><a href=""biopage.asp?selectedperson=" & _
arrEmployees(0, RowCounter + ColCounter * NumRows-1) & _
""">" & Displayname & _
"</a></td><td>" & _
arrEmployees(3, RowCounter + ColCounter * NumRows-1) & _
"</td>"
Else
'--This condition takes care of the case where your last column
'--has fewer rows than the first one
Response.write "<td> </td><td> </td>"
end if
Next
response.write "</tr>"
Next
%>
</table>
<p ALIGN="CENTER"><a href="/">Return to Home Page</a></p>
</html>
|
The result of the above code is a four column table that has two records
per row. The records are in order top to bottom in the first column and
then they continue top to bottom in the second column. You can change it
to any number of columns by simply changing the NumColumns constant. I
hope someone out there can use this example to stop scrolling through page
after page of a long, skinny table.
Happy Programming!
