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

The 4 Guys Present: ASPFAQs.com

Jump to a FAQ
Enter FAQ #:
..or see our 10 Most Viewed FAQs.

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?


[Print this FAQ]

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.

Do you have a FAQ you'd like to suggest? Suggestions? Comments? If so, send it in! Also, if you'd like to be a FAQ Admin (creating/editing FAQs), let me know! If you are looking for other FAQs, be sure to check out the 4Guys FAQ and Commonly Asked Messageboard Questions!

Most Viewed FAQs:

1.) How can I format numbers and date/times using ASP.NET? For example, I want to format a number as a currency. (761643 views)
2.) I am using Access and getting a 80004005 error (or a [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)' error) when trying to open a connection! How can I fix this problem? (207777 views)
3.) How can I convert a Recordset into an array? Also, how can I convert an array into a Recordset? (202549 views)
4.) How can I quickly sort a VBScript array? (196039 views)
5.) How can I find out if a record already exists in a database? If it doesn't, I want to add it. (156019 views)
6.) How do I display data on a web page using arrays instead of Do...While...MoveNext...???... (152331 views)
7.) When I get a list of all files in a directory via the FileSystemObject, they aren't ordered in any reasonable way. How can I sort the files by name? Or by size? Or by date created? Or... (140381 views)
8.) For session variables to work, must the Web visitor have cookies enabled? (110162 views)
9.) Can I send emails without using CDONTS? (107083 views)
10.) How can I take the result of a SELECT...MULTIPLE or a group of same-named checkboxes and turn it into a query? That is, if the user selects 3 answers, how can I construct a query that looks for all 3? (106308 views)
Last computed at 9/17/2007 3:22:00 AM


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