Display Records in Multiple Columns with ASPMy 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.
|
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More ASP Code Articles
More By aspfree  developerWorks - FREE Tools!
|
David Barnes, Lead Evangelist for IBM Emerging Internet Technologies will discuss aspects of Web 2.0 that bring value to corporations, academia, and government. He'll also discuss IBM's vision around Web 2.0, including the importance of remixability and consumability. The discussion will culminate with examples of various IBM Software Group solutions you can use to get ahead of the Web 2.0 adoption curve.
FREE! Go There Now!
| | | |
CakePHP is a stable production-ready, rapid-development aid for building Web sites in PHP. This "Cook up Web sites fast with CakePHP" series shows you how to build an online product catalog using CakePHP.
FREE! Go There Now!
| | | |
WebSphere Process Server delivers a unique integration framework that simplifies existing IT resources. Often, as IT assets grow to support business demand, so too does their complexity and manageability. In this webcast, we’ll discuss how WebSphere Process Server helps deliver an SOA infrastructure that provides a common model to orchestrate, mediate, connect, map, and execute the underlying IT functions. Discover how WebSphere Process Server simplifies integration of business processes by leveraging existing IT assets as reusable services without the complexities of traditional integration methodologies.
FREE! Go There Now!
| | | |
Visit IBM developerWorks to download a free trial of the Rational Host Access Transformation Services (HATS) Toolkit. The HATS toolkit provides a set of plug-ins for the IBM Rational Software Delivery Platform to help you easily extend your legacy applications. HATS makes your 3270 and 5250 applications available as HTML through the most popular Web browsers, while converting your host screens to a Web look and feel and it also enables you to develop new Web, portal, and rich-client applications.
FREE! Go There Now!
| | | |
Manage, govern, and share services across your organization by using WebSphere Service Registry and Repository. Follow the hands-on exercises to learn how to navigate the Web interface to publish, find, reuse, and update services.
FREE! Go There Now!
| | | |
Secure your Web applications with IBM Rational AppScan Standard Edition V7.7, previously known as Watchfire AppScan. This Web application security testing tool automates vulnerability assessments and scans and tests for common Web application vulnerabilities. Visit IBM developerWorks to download a free trial of IBM Rational AppScan Standard Edition V7.7.
FREE! Go There Now!
| | | |
Ken Krugler, co-founder of code search company Krugle, and Laura Merling, vice president of Marketing and Business Development for Krugle, join to talk about the ins and outs of code search and what it means as a new feature for developerWorks users.
FREE! Go There Now!
| | | |
Get a free trial download of the latest version of IBM Rational Performance Tester V7.0.1, a load and performance testing solution for teams concerned about the scalability of their Web-based applications. Combining multiple ease-of-use features with granular detail, Rational Performance Tester simplifies the test-creation, load-generation and data-collection processes that help teams ensure the ability of their applications to accommodate required user loads.
FREE! Go There Now!
| | | |
The Eclipse community is constantly working to extend Eclipse's functionality. In this webcast, learn about some of the most important and feature-rich projects under development. From multi-language support to plug-in development, tune in to see what Eclipse is capable of now.
FREE! Go There Now!
| | | |
The unprecedented scope of a service-oriented architecture (SOA) initiative brings to the forefront a number of management and governance issues that were sidestepped in the past. The key to a successful SOA implementation is managing and governing activities throughout the entire SOA delivery lifecycle by ensuring that services conform to the needs of all of the business’s stakeholders. Learn how service lifecycle management allows the business to ensure that the process by which services are defined, created, tested, deployed, optimized and retired is manageable, repeatable and auditable.
FREE! Go There Now!
| | | |
All FREE IBM® developerWorks Tools!
| |
/ 8 
