Developer

Formatting data results in Active Server Pages

If you're writing Web-based applications for your company's intranet, you could be asked to produce output in multiple columns, a la Excel. Not quite sure how to do that? This reprint from our Web Development Zone TechMail can get you started.


Editor’s note: This article originally appeared in TechRepublic’s Web Development Zone TechMail. Subscribe, and you’ll receive information on Web development-related projects and trends.

As more companies use an intranet to eliminate the problems associated with installing programs on all of their users’ desktop computers, we (as developers) are asked to write Web-based applications that function more like traditional programs.

This is especially true when it comes to writing business reports. Storing reports on an intranet makes sharing the results of key operations simple and efficient. The problem is that a lot of end users are accustomed to reading reports from applications like Microsoft Excel, where data is formatted alphabetically by column. Data returned from a presorted record can be easily formatted into a single column table, but what do you do if you need to produce the output in multiple columns?

The answer is to build the table rows a cell at a time. By creating a loop that checks each record for the End of File (EOF) marker before writing the output record to a table cell, we can continue adding new rows to our table as needed until EOF has been reached. This gives us a table ordered left to right, with our list order continuing onto the next line.

In order to format our data by columns, we need to first place our data into an array. For this example, we are using a single field of data. However, you can use a multidimensional array to store additional data fields.
dim ContactRecords(0)
I = 0
While (NOT rsContact.EOF)
       redim preserve ContactRecords(I)
       ContactRecords(i) = rsContact(0)
      rsContact.MoveNext
       I = I + 1
wend


Once the data is loaded into an array, we know the number of records we have to display. For the purposes of this example, our display table is going to have four columns. We can establish the number of rows that our table will contain by using the FIX and MOD functions of VBScript (see the VBScript help file for details).
RowsToDisplay =  (FIX(I/4))
if (I mod 4) <> 0 then
  RowsToDisplay = (RowsToDisplay + 1)
end if


Now that we know the number of table rows that we are going to display, we need to set the array index for the columns in the first row.
Col1Display = 0
col2display = RowsToDisplay
col3Display = ( 2 * RowsToDisplay)
col4Display = ( 3 * RowsToDisplay)


By using a "for" loop to process the "RowsToDisplay" and increasing the column index value by one after outputting the array's value, we achieve our desired formatting by column.
<table width="100%" border="0">
<tr>
<th align="left" colspan="4" width="100%" valign="top">Contacts</th>
</tr>
<%for rownum = 1 to RowsToDisplay %>
<tr>

<%if col1display <= Ubound(ContactRecords) then  %>
<td align="left" width="25%"><%=ContactRecords(col1display)%></td>
<%else%>
<td align="left" width="25%">&nbsp;</td>
<%end if
col1display = col1display + 1

if col2display <= Ubound(ContactRecords) then  %>
<td align="left" width="25%"><%=ContactRecords(col2display)%></td>
<%else%>
<td align="left" width="25%">&nbsp;</td>
<%end if
col2display = col2display + 1

if col3display <= Ubound(ContactRecords) then  %>
<td align="left" width="25%"><%=ContactRecords(col3display)%></td>
<%else%>
<td align="left" width="25%">&nbsp;</td>
<%end if
col3display = col3display + 1

if col4display <= Ubound(ContactRecords) then  %>
<td align="left" width="25%"><%=ContactRecords(col4display)%></td>
<%else%>
<td align="left" width="25%">&nbsp;</td>
<%end if%>
</tr>
<%col4display = col4display + 1

next%>
</table>


This technique should come in handy the next time you get a request for special formatting from your user groups.

For more information on VBScript and ADO, check out these sites:

Editor's Picks

Free Newsletters, In your Inbox