|
Situation
Recently I was asked to develop a web based report that would collect
data from a database, format it in an HTML page and then print a certain
number of rows on each page with a column header. The problem
with coming up with a solution such as requested, is to find out how to do a
page break after a certain number of rows.
I investigated on the net on some
possible solutions for this. None was exactly what I was looking for. So I
combined pieces of solutions together and made my own custom built header
printing page.
Note: This solution only works
with Internet Explorer. I tested it with IE version 5.5 and IE 6.0
beta.
Setup for Solution
The following solution uses the
included MS Northwind database on a SQL server 2000. The script will run on
IIS5, but not depended on IIS5.
Solution Step 1
We will solve the problem using
Style Sheet and Div tags. First we add this style sheet tag at the <Head>
section of the asp page -
[Code Block]
<style type="text/css">
<!--
td { font-family: Arial; font-size: 9pt; }
br.page { page-break-before: always; }
-->
</style>
We add the br.page {
page-break-before: always; } to tell Internet explorer to add a page break at
the end of each table section.
[Code Block]
<%
Dim Test_Conn, Test_RS, Test_sql, Counter
Set Test_Conn = Server.CreateObject("ADODB.Connection")
Set Test_RS = Server.Createobject("ADODB.Recordset")
Test_Conn.Open "Provider=SQLOLEDB; Data Source=yourserver; Initial Catalog=Northwind;
User ID=sa; Password=sapassword"
Test_sql = "Select CustomerID, CompanyName, ContactName, ContactTitle,
Address, City, Region, Country from Customers"
Test_rs.open Test_sql, Test_Conn
%>
We do our database connection and
open the record set. Note : The database connection uses MS Northwind database
included with SQL server 2000. Also, you will need to change the Data Source
name, UserID, and Password if you cut and paste to use the above code.
[Code Block]
<%
Counter = 0
Test_Rs.Movefirst
while not Test_Rs.Eof
if Counter = 0 then
With Response
.Write "<table border=""0"" cellpadding=""0"" cellspacing=""4""
style=""border-collapse: collapse"" bordercolor=""#111111"" id=""AutoNumber1""
width=""100%"">"
.Write "<tr>"
.Write "<td bgcolor=""#CCCCFF"" style=""border: 1px dashed #000000"">Customer
ID</td>"
.Write "<td bgcolor=""#CCCCFF"" style=""border: 1px dashed #000000"">Company
Name</td>"
.Write "<td bgcolor=""#CCCCFF"" style=""border: 1px dashed #000000"">Contact
Name</td>"
.Write "<td bgcolor=""#CCCCFF"" style=""border: 1px dashed #000000"">Contact
Title</td>"
.Write " <td bgcolor=""#CCCCFF"" style=""border: 1px dashed
#000000"">Address</td>"
.Write " <td bgcolor=""#CCCCFF"" style=""border: 1px dashed
#000000"">City</td>"
.Write " <td bgcolor=""#CCCCFF"" style=""border: 1px dashed
#000000"">Region</td>"
.Write " <td bgcolor=""#CCCCFF"" style=""border: 1px dashed
#000000"">Country</td>"
.Write "</tr>"
End With
End if
With Response
.Write "<tr><td>"
.Write Test_Rs("CustomerID") & "</td>"
.Write "<td>"
.Write Test_Rs("CompanyName") & "</td>"
.Write "<td>"
.Write Test_Rs("ContactName") & "</td>"
.Write "<td>"
.Write Test_Rs("ContactTitle") & "</td>"
.Write "<td>"
.Write Test_Rs("Address") & "</td>"
.Write "<td>"
.Write Test_Rs("City") & "</td>"
.Write "<td>"
.Write Test_Rs("Region") & "</td>"
.Write "<td>"
.Write Test_Rs("Country") & "</td></tr>"
.Write " <tr>"
.Write "<td colspan=""8"">"
.Write "<hr noshade color=""#000000"" size=""1""></td>"
.Write "</tr>"
End With
Counter = Counter + 1
if Counter = 10 then
Response.Write "</table>"
response.write "<br class=""page"" />"
Counter = 0
end if
Test_rs.movenext
Wend
Test_rs.close
Test_Conn.close
Set Test_rs = nothing
Set Test_Conn = nothing
%>
We use the counter to count how
many rows have been printed. This will help us to identify when the header row
needs to be printed again. In the example it prints 10 rows per page, but this
can be easily changed to as many rows that will fit your particular need.
Change the line that writes If Counter = 10 to 20, 30 Whatever"
I thought this solution was quick
and nice! If you like it send me a quick note. (Yaheya
Quazi)
Happy programming!
|
