When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
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!

Return to user tips...


ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article