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!
|
Building a software-as-a-service solution requires addressing a few key technical challenges. In this webcast, we'll focus on the role of IBM Tivoli Directory Server and WebSphere Portlet Factory in creating a Software as a Service solution. We will demonstrate how to use Tivoli Directory Server to prevent the user population of one tenant from accessing the virtual portal and portlet components of another tenant. We will also use the dynamic profile capability of WebSphere Portlet Factory to create multiple highly customized applications from one code base.
FREE! Go There Now!
| | | |
This whitepaper presents the benefits of successfully introducing static analysis into your organization using IBM Rational Software Analyzer. Additionally, it identifies some common pitfalls that can hinder the effective use of static analysis tooling as well as presents 10 simple strategies designed to help you quickly realize the value of static analysis using Rational Software Analyzer.
FREE! Go There Now!
| | | |
Join us for this on demand webcast to learn about developing complex systems more quickly and efficiently. We'll cover market drivers for developing, governing and reusing systems software assets and how you can develop system software assets with Rational Asset Manager.
FREE! Go There Now!
| | | |
Download the Rational Application Developer (RAD) v7.5 open beta code and start developing applications for the JEE5 standard which features EJB3.0, JPA, JSF 1.2, JSP 2.1 and Servlet 2.5 standards. When you use this beta you will see how you can increase developer productivity for already existing applications with improved support for refactoring, as well as adding new features to existing applications. In addition, the beta provides tooling for JD Edwards, Oracle, SAP, Siebel and PeopleSoft to improve the developer productivity with these enterprise systems.
FREE! Go There Now!
| | | |
Join this Rational Talks to You teleconference on December 4 at 1:00 pm ET to discuss how Rational Method Composer can help meet your compliance objectives. Get your questions answered!
FREE! Go There Now!
| | | |
Discover how Rational tools and best practices for testing can make your job easier. The new Rational Testing eKits provide you with valuable resources – including demos, webcasts, tutorials, and articles – that help you address your specific testing needs across the software lifecycle. Five new eKits are available covering the topics of Requirements and Test Management, Functional Testing, Performance Testing, Code Quality and Embedded Systems, and SOA and Web Services Testing.
FREE! Go There Now!
| | | |
This paper is about the critical role that a discipline called integrated requirements management can play in helping to ensure that your business goals and IT investments are continuously aligned—whether you are sourcing, integrating, building or maintaining software. It also looks at ways that automated IBM Rational® products can work together to help you use requirements in the very best way.
FREE! Go There Now!
| | | |
Informix Dynamic Server (IDS) Express Edition offers outstanding online transaction processing (OLTP) database performance, while helping to simplify and automate many of the tasks associated with deploying databases for small business applications. IDS 11 further extends the ease of management and applications integration with the Admin API and Scheduler, high availability with Continuous Log Restore for backup server recovery in case of a primary server failure, and column level encryption to protect personal and company private data.
FREE! Go There Now!
| | | |
Visit IBM developerWorks to try the IBM SOA Sandbox for process. The SOA Sandbox for process focuses on providing a trial environment with the necessary tooling and components required to gain a better understanding of business processes and how to best improve existing business processes to derive value quickly.
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!
| | | |
All FREE IBM® developerWorks Tools!
| |
/ 8 
