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.
Published: Thursday, November 18, 1999

Custom Formatted Database Tables

By Matt Smith


Yes, this is another article on how to display the results from a database in 'newspaper-style'

- continued -

columns. Josh Hurwitz started with his article, Formatting Database Results, which showed how to display records horizontally (from left-to-right and top-to-bottom). Claude Rubinson followed up with his article, Formatting Database Results into Newspaper-Style Columns, which showed how to display records vertically (from top-to-bottom and left-to-right). Both articles have the flexibility to allow a variable number of columns set by the user (or parameters), however, both authors approach the task of correctly populating the HTML table differently. Josh uses mod to display the records horizontally and Claude uses multi-dimensional arrays to display the records vertically. This article builds on the flexibility of their code, provides another approach for displaying the records that works for either horizontal or vertical and addresses the limitations that Claude points out in his code.

The main reason I started playing around with this is because I hate arrays, especially multi-dimensional arrays, and when I was reading Claude's article I thought I could see a way of achieving the same results with only the recordset object and some For...Next loops. It took me a little while, but I was able to do it. Of course, by that time, my cube-mate, Brian Moeskau, had noticed that I wasn't doing any real work and wanted to know what I was working on. I showed him and as always, he wanted to take my efforts up a notch: "How about adding the ability to specify whether to display the records horizontally or vertically?" he asked. Well, since I already spent this much time on it... I finished that task and then it was "Can you add the ability to specify whether it is the number of columns or number of rows that is being passed in?" So, with a little input from both us we came up with the code below. Of course neither of us has any real use for the code but we enjoyed the challenge.

So what we ended up with was an ASP page that would custom format results from a database into an HTML table based on three parameters: Size, Orientation, and LayoutType. Size is the number of rows or columns (based on LayoutType) in the HTML table. Orientation is whether the results should be displayed (H)orizontally across the table or (V)ertically down the table. LayoutType is whether the Size refers to the number of (R)ows or (C)olumns in the HTML table. Take a look at the examples below or try it yourself and then check out the code.

Examples

Size = 5
LayoutType = C
Orientation = V

Size = 5
LayoutType = C
Orientation = H

Size = 5
LayoutType = R
Orientation = V

Size = 5
LayoutType = R
Orientation = H

Alphabet
A G M S Y
B H N T Z
C I O U  
D J P V  
E K Q W  
F L R X  
Alphabet
A B C D E
F G H I J
K L M N O
P Q R S T
U V W X Y
Z        
Alphabet
A F K P U Z
B G L Q V  
C H M R W  
D I N S X  
E J O T Y  
Alphabet
A B C D E F
G H I J K L
M N O P Q R
S T U V W X
Y Z        

 

The Code
This first segment of code sets up my constants and variables. Nothing too terribly difficult so far. I did not include ADOVBS.INC (see ADOVBS.inc... use it!) because I only needed two values: adOpenStatic and adCmdText. Everything is named decently except intSize and intOtherSize because strLayoutType actually determines whether intSize refers to rows or columns. Semantics aside, we just need two variables to hold the dimensions of our table: one that is passed in through parameters and the other that is set through logic. Don't forget to include OPTION EXPLICIT (Using Option Explicit) so that variable naming doesn't trip you up.

<% OPTION EXPLICIT %>
<% Const adOpenStatic = 3 Const adCmdText = &H0001 Const TITLE = "Alphabet" dim objRS 'Recordset object dim strSQL 'SQL string dim intSize 'user defined dimension of table '(could be number of rows or cols) dim intOtherSize 'other dimension of table '(could be number of rows or cols) dim strLayoutType 'whether intSize refers to (C)olumns or (R)ows dim strOrientation 'direction of output: (V)ertical or (H)orizontal dim intNumRecs 'number of records in the database dim intNumCols 'number of columns in table dim intNumRows 'number of rows in table dim intRowIndex 'placeholder for current row dim intColIndex 'placeholder for current column dim intPosition 'placeholder for current record %>

 

We will use a recordset object (Talking to your Database: The Recordset Object) for working with our records from the database. I don't use a connection object but instead define the active connection for the recordset. I can't currently run OLEDB (OLEDB For Me) on my host so that is why I have two different connection strings. I recommend OLEDB but a standard ODBC connection works just fine for this example. I also use a DSN-less connection (System DSN or DSN-less Connection?) because it allows me to move my code from my PC to our intranet server to my host without creating DSNs everywhere. I also like DSN-less connections because it gets me around that annoying $4/month/DSN my host wants to charge for creating and maintaining a DSN. The SQL itself is pretty simple but if you are unfamilar with it then check out SQL, the Language of Databases.  Then we want to open our recordset which executes the SQL. We open our connection with a static cursor (Recordset Cursors: Choose the Right Cursor for the Right Job) because we need to move around our recordset.

'create recordset object Set objRS = Server.CreateObject("ADODB.Recordset") 'connect via ODBC objRS.ActiveConnection = "DBQ=" & server.MapPath("alphabet.mdb") & _ ";Driver={Microsoft Access Driver (*.mdb)};" 'or connect to database via OLEDB 'objRS.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.3.51; Data Source=" & _ Server.MapPath("alphabet.mdb") 'create SQL query strSQL = "SELECT Letter " strSQL = strSQL & "FROM Letters " strSQL = strSQL & "ORDER BY Letter;" 'execute SQL statement objRS.Open strSQL, ,adOpenStatic , , adCmdText

 

In this part, we will set all of our variables to get ready for the real work in the next segment. First, if there are no records then we skip the majority of the code except at the very end where we clean up and close our connection object. If there are records then let's get the parameters passed to the file. It really doesn't matter when or where you retrieve these values as long as you do it before they are needed. Then, we get the number of records from out recordset object (How not to count records returned in a Recordset Object). If Size was not passed in then we will go ahead and set size to intNumRecs. That way, if no parameters are passed in, the results will be returned on separate rows. Since intSize specifies the size of one dimension of our table, we then divide intNumRecs by intSize to get intOtherSize as the other dimension of our table. If the remainder is not zero then we need to increase intOtherSize by one. Then we just assign intSize and intOtherSize to intNumRows and intNumCols appropriately based on the strLayoutType.

If objRS.EOF Then Response.Write "No records in " & TITLE & "database." Else 'retrieve parameters intSize = Request("Size") strOrientation = UCase(Request("Orientation")) strLayoutType = UCase(Request("LayoutType")) Response.Write "<P><TABLE BORDER=1>" & vbCRLF 'get the number of records in the database intNumRecs = objRS.RecordCount 'if size was not passed in then set size equal to the number of records If intSize = "" Then intSize = intNumRecs End If 'get other dimension of the table If intNumRecs Mod intSize = 0 Then intOtherSize = intNumRecs / intSize Else intOtherSize = Int((intNumRecs / intSize)) + 1 End If 'set variables for table size If strLayoutType = "C" Then intNumRows = intOtherSize intNumCols = intSize Else'If strLayoutType = "R" Then intNumRows = intSize intNumCols = intOtherSize End If

 

Here, we span intNumCols with our table header and assign the title. Now this is the fun part. We use For...Next loops to iterate through our table rows by column and populate from our recordset using the AbsolutePosition property. The outer loop keeps track of rows and the inner loop keeps track of columns. These loops help us to output our cells from left-to-right and top-to-bottom, which is necessary for outputting to HTML. Before we get to the inner loop though, we check strOrientation to see whether we will display our records vertically or horizontally. If vertically, then intPosition is set to the intRowIndex before we go into the inner loop and intPosition is incremented by intNumRows after PrintRecord (explained below). If horizontally, then intPosition is set inside the loop before the cell is printed. In this case, intPosition is set to intColIndex + ((intRowIndex - 1) * intNumCols). It's kind of difficult to explain the exact logic or algorithm used to do this but if you step through printing 1 through 10, either horizontally or vertically, in different size grids you will start to see how the logic works. Don't forget to clean up your recordset at the end.

'span the number of columns in the table with the title Response.Write "<TR><TH COLSPAN=" & intNumCols & ">" & TITLE & "</TH></TR>" & vbCRLF 'loop thru table by row For intRowIndex = 1 To intNumRows Response.Write "<TR>" & vbCRLF If strOrientation = "V" Then intPosition = intRowIndex 'loop thru table by column For intColIndex = 1 To intNumCols PrintRecord intPosition = intPosition + intNumRows Next Else 'otherwise assume "H" 'loop thru table by column For intColIndex = 1 To intNumCols intPosition = intColIndex + ((intRowIndex - 1) * intNumCols) PrintRecord Next End If Response.Write "</TR>" & vbCRLF Next Response.Write "</TABLE>" & vbCRLF End If 'clean up and close the database objRS.Close Set objRS = Nothing

 

PrintRecord writes out the contents of each cell in the table. The first check is to avoid having an intPosition that is greater than intNumRecs. This situation happens in the extra cells when printing vertically so we just output a blank cell. Otherwise, let's go ahead and get our record using AbsolutePosition. If it is empty then write out a blank cell. If not empty, write out the value in the cell.

sub PrintRecord 'if position is past EOF print a blank cell If intPosition > intNumRecs Then Response.Write "<TD>&nbsp;</TD>" & vbCRLF Else objRS.AbsolutePosition = intPosition 'if necessary, check for blank field values and print blank cell If objRS.Fields("Letter") = "" Then Response.Write "<TD>&nbsp;</TD>" & vbCRLF Else Response.Write "<TD>" & objRS.Fields("Letter") & "</TD>" End If End If End Sub

Enhancing the System
Alert reader Snow H. wrote in asking if it was possible to have the output broken up into multiple "pages." That is, if we had 500 records, we would like to be able to show five pages of 100 records each, with Next and Previous buttons. I decided to implement this approach; you can see an example and download the code from http://www.smitty.net/matt/asp/table/paging.asp.

Conclusion

There you have it.  Hope it makes sense and that you can find some use for it. 

Happy Programming!


  • By Matt Smith


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