Format data from Access Database

[tlmm]

I have an access database with a front-end created in asp. A part of my application is a directory of all members in my database - name, contact info, phone, fax, email, etc.

I am trying to figure out how to format this directory on the screen so that it looks nice and so I can get as many records as possible to appear on each page for printing purposes. It would be good to have it in rows and colums.

This is what I have so far:

<table cellSpacing="0" cellPadding="0" width="710" border="0">
<tbody>
<tr>
<td colSpan="3" height="5"></td>
</tr>

<%
Dim i
if not rsMembers.eof or rsMembers.bof then
i=0
'As long as it's not at the end of the recordset, keep adding 1 to loop through the records.
do while not rsMembers.eof
i=i+1
%>

<tr>
<td>
<table>
<tr>
<td width="480">
<%Response.Write(rsMembers("FName"))%> <%Response.Write(rsMembers("LName"))%><br>
<%Response.Write(rsMembers("Address1"))%><br>
<%Response.Write(rsMembers("Address2"))%><br>
<%Response.Write(rsMembers("CityTown"))%><br>
<%Response.Write(rsMembers("Province"))%><br>
<%Response.Write(rsMembers("PostalCode"))%><br>
</td>
<td width="480">
<%Response.Write(rsMembers("FName"))%> <%Response.Write(rsMembers("LName"))%><br>
<%Response.Write(rsMembers("Address1"))%><br>
<%Response.Write(rsMembers("Address2"))%><br>
<%Response.Write(rsMembers("CityTown"))%><br>
<%Response.Write(rsMembers("Province"))%><br>
<%Response.Write(rsMembers("PostalCode"))%><br>
</td>
<td width="480">
<%Response.Write(rsMembers("FName"))%> <%Response.Write(rsMembers("LName"))%><br>
<%Response.Write(rsMembers("Address1"))%><br>
<%Response.Write(rsMembers("Address2"))%><br>
<%Response.Write(rsMembers("CityTown"))%><br>
<%Response.Write(rsMembers("Province"))%><br>
<%Response.Write(rsMembers("PostalCode"))%><br>
</td>
</tr>
<tr>
<td> </td>
</tr>
<tr>
<td width="480">
<%Response.Write(rsMembers("FName"))%> <%Response.Write(rsMembers("LName"))%><br>
<%Response.Write(rsMembers("Address1"))%><br>
<%Response.Write(rsMembers("Address2"))%><br>
<%Response.Write(rsMembers("CityTown"))%><br>
<%Response.Write(rsMembers("Province"))%><br>
<%Response.Write(rsMembers("PostalCode"))%><br>
</td>
<td width="480">
<%Response.Write(rsMembers("FName"))%> <%Response.Write(rsMembers("LName"))%><br>
<%Response.Write(rsMembers("Address1"))%><br>
<%Response.Write(rsMembers("Address2"))%><br>
<%Response.Write(rsMembers("CityTown"))%><br>
<%Response.Write(rsMembers("Province"))%><br>
<%Response.Write(rsMembers("PostalCode"))%><br>
</td>
<td width="480">
<%Response.Write(rsMembers("FName"))%> <%Response.Write(rsMembers("LName"))%><br>
<%Response.Write(rsMembers("Address1"))%><br>
<%Response.Write(rsMembers("Address2"))%><br>
<%Response.Write(rsMembers("CityTown"))%><br>
<%Response.Write(rsMembers("Province"))%><br>
<%Response.Write(rsMembers("PostalCode"))%><br>
</td>
</tr>
<tr>
<td> </td>
</tr>
</table>
</td>
</tr>

<%
rsMembers.movenext
loop
end if
%>
</tbody>
</table>

Will probably be more rows, but just put this in to show what i am doing. The format part is not a problem, but I am not sure of the loop. If I don't format with rows and colums, it displays all my records down in a single column. Now that I try to format, I get the same record appearing in all 6 colums above, then the same one appearing in the next 6 columns and so on. This is the problem. I need to know how to fix the loop so that it shows the next record in the next column. I want to also put in page breaks once I figure out how many records can fit on a page, so that it can be printed.

Can someone give me some pointers about where to put my loop?

Thanks in advance.

 

[MadJock]

Hi,

You only need to paint one row per per record - not 6.

Example below:

<table cellSpacing="0" cellPadding="0" width="710" border="0">
   <tbody>
    <tr>
       <td colSpan="3" height="5"></td>
    </tr>

    <%                         
    Dim i
    if not rsMembers.eof or rsMembers.bof then
    i=0
    'As long as it's not at the end of the recordset, keep adding 1 to loop through the records.
    do while not rsMembers.eof
    i=i+1 
    %>

    <tr>
            <td><%Response.Write(rsMembers("FName"))%> <%Response.Write(rsMembers("LName"))%></td>
            <td><%Response.Write(rsMembers("Address1"))%></td>
            <td><%Response.Write(rsMembers("Address2"))%></td>
            <td><%Response.Write(rsMembers("CityTown"))%></td>
            <td><%Response.Write(rsMembers("Province"))%></td>
            <td><%Response.Write(rsMembers("PostalCode"))%></td>
    </tr>
                                 
    <% 
    rsMembers.movenext    
    loop 
    end if
    %>
   </tbody>
</table>

hope this helps,

Graeme

"Just beacuse you're paranoid, don't mean they're not after you

 

[tlmm]

I would like to have 3 columns across the page and 3 0r 4 down, depending on what fits. I had it done like you mentioned but it just lists them down the page one after the other. I would prefer to have it formatted with serveral rows and columns on the page and have a page break so it prints nice.

Any other suggestions or an easier way to go about this?

Thanks!

 

[ChrisHirst]

search the forums for database paging or faq333-3229 or ASP101 paging script



Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[tlmm]

Thanks for your replies.

After reading the following article, I used the code posted there. It works great with a little modification.

Here it is in case anyone is interested:

http://www.aspfaq.com/show.asp?id=2247