SEARCH ASP FREE

Display Records in Multiple Columns with ASP

My name is Pete Draigh. 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.

Here is the code!

<%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.