ASPFAQs.com
Your source for ASP-related frequently asked questions and answers!


4GuysFromRolla.com : ASP FAQS : Databases, General

Question: How can I output the records from my recordset in columns in a table? That is, instead of just one record per row in a table, how can I have multiple records per row?

Answer: In other words, what you want to see is code something like this:

<TR>
    <TD>info from record 1</TD>
    <TD>and then record 2</TD>
    <TD>and record 3</TD>
</TR>
<TR>
    <TD>record 4 starts a new row</TD>
    <TD>and record 5</TD>
    <TD> </TD>
</TR>


And the answer is pretty easy: Only put in the <TR> tag at the beginning of every group of N records. Only put in the </TR> tag at the end of such a group.

"And how," you ask, "does one do that?"

Several ways, but this is conceptually a simple way to do it:

<%
Const COLUMN_COUNT = 3 ' or 2 or 4 or ...
...
... we assume you know how to get the recordset RS from a query ...
...
column = 0 ' initialize counter
Do While Not rs.EOF
    ' as describe in the text, put in
    ' <TR> at beginning of group:
    If column = 0 Then Response.Write "<TR>"
    ... output your <TD> through </TD> stuff...
    ... such as:
    ... Response.Write "<TD>" & _
            RS("somethingOrOther") & "</TD>"
    ...
    column = column + 1
    If column = COLUMN_COUNT Then
        Response.Write "</TR>" & vbNewLine
        column = 0 ' start over!
    End If
    RS.MoveNext
Loop
' clean up last row, if needed!
If column <> 0 Then
    For c = column To COLUMN_COUNT
        Response.Write "<TD>???</TD>"
    Next
    Response.Write "</TR>" & vbNewLine
End If
...
%>


In the line that reads Response.Write "<TD>???</TD>" replace the ??? there with nothing at all if you don't want the table cell boundaries to show. Replace the ??? with &NBSP; if you want the boundaries to show up with no content. And you can omit the loop entirely (and just output the </TR> tag) if you aren't showing borders on the table.

That was the simple answer!

It works fine if you are happy seeing output on the screen in the order
record1 -- record2 -- record3
record4 -- record5 -- record6


But what if you *must* have the data displayed in the form
record1 -- record5 -- record9
record2 -- record6 -- record10
record3 -- record7 -- record11
record4 -- record8 -- record12

Then what do you do?

There are several ways to accomplish this:
(1) Get the count of records. Divide by the number of columns. Loop through the recordset as many times as needed to get all rows, but instead of using MoveNext use Move to move to the next logically numbered record for the given row.
(2) Get the count of records. Divide by the number of columns. Then output code of the form:

<TR>
    <TD>
        <TABLE>
        <TR><TD>..rec1..</TD></TR>
        <TR><TD>..rec2..</TD></TR>
        <TR><TD>..rec3..</TD></TR>
        <TR><TD>..rec4..</TD></TR>
        </TABLE>
    </TD>
    <TD>
        <TABLE>
        <TR><TD>..rec5..</TD></TR>
        <TR><TD>..rec6..</TD></TR>
        <TR><TD>..rec7..</TD></TR>
        <TR><TD>..rec8..</TD></TR>
        </TABLE>
    </TD>
...
</TR>


But both those solutions have problems. The first, because moving randomly through recordsets is quite inefficient. The second, because getting the "inner" tables to line up nice and pretty may not be as easy as you wish (depending on the cell contents).

So...

An alternative to the first solution: Use ADODB.RecordSet.GetRows( ).

It has all the advantages of being able to use a single table and none of the drawbacks of doing random cursor positiong.

Thus:

<%
Const COLUMN_COUNT = 3
...
... get the recordset from a query ...
allRecords = RS.GetRows ' convert it to an array!
RS.Close ' no further need of it

' how many records?
recMax = UBound( allRecords, 2 )
' then how many rows from those records?
rowCount = (recMax + 1) / COLUMN_COUNT
' round to next number of rows if not even divide:
If rowCount <> Int(rowCount) Then
    rowCount = Int(rowCount) + 1
End If

For row = 0 To rowCount-1
    Response.Write "<TR>" & vbNewLine
    ' for this row, we start on this same record:
    startRec = row
    ' we do COLUMN_COUNT columns per row:
    For col = 0 To COLUMN_COUNT-1
        ' and we use this record number:
        rec = startRec + col * rowCount
        ' caution! we might go past end!
        If rec > recMax Then
            ' past end...output blank cell
            Response.Write "<TD>???</TD>"
        Else
            ' output info about this record:
            ' (one fld of record shown...but use
            ' as many fields as needed, of course!)
            Response.Write "<TD>" & _
            allRecords(0,rec) & "</TD>"
        End If
        Next ' next column
    ' done with one row...
    Response.Write "</TR>" & vbNewLine
Next ' next row
...
%>

Again, replace the ??? with the appropriate blank cell contents for your table.

Hope that makes sense to you! Email to junco.junction@verizon.net if you have problems with it.

Addendum


Matt Smith pointed out that he wrote an article for 4GuysFromRolla some time ago that does somewhat the same thing as described here. Actually, it does a lot more than what is described here, since it allows you to choose orientation and pick whether you will specify the number of rows or columns.

The code is well written and well organized. I, personally, would change it so that it used the 2D array from GetRows instead of continually changing the ADODB.RecordSet.AbsolutePosition, simply because the performance increase would, I believe, be noticable. But doing so wouldn't really change the logic of Matt's code, so even if you want to opt for the better performance the code is well worth reading. Or, if you don't need to worry about performance, go ahead and use the code, as is.



FAQ posted by Bill Wilkinson at 1/7/2002 7:16:35 PM to the Databases, General category. This FAQ has been viewed 71,539 times.


Copyright 2018 QuinStreet Inc. All Rights Reserved.
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers