Create PDF Files with asp and a stored procedure
Techniques
This section makes it possible to gather a whole of techniques or scripts which have the originality to manage to solve a difficulty with the least effort or which are particularly effective...
Techniques / PDF : Create a PDF from a stored procedure
Original version from Masar Ivica - Visit our sponsors
Part I : Create the procedure.
- A dynamic PDF report on the fly :
In order to create a simple invoice we have to fill the table psopdf with the data that must appear in the invoice. In order to generate the file on the fly from ASP, we going to use SQL queries via ADO, then we can change the content type of the page and write to it the generated content of the PDF file. :
' The script Generate_pdf.asp
'Create ADODB connection object
'Set cn = Server.CreateObject("ADODB.Connection")
'Fill the table:
cn.Execute("INSERT psopdf(code) SELECT SPACE(60) + 'COMPANY LTD'"))
cn.Execute("INSERT psopdf(code) SELECT SPACE(60) + 'COMPANY ADDRESS')
cn.Execute("INSERT psopdf(code) SELECT SPACE(60) + 'STREET NAME & No')
cn.Execute("INSERT psopdf(code) SELECT ' ')
cn.Execute("INSERT psopdf(code) SELECT SPACE(34) + 'BILL OF SALE')
cn.Execute("INSERT psopdf(code) SELECT ' ')
cn.Execute("INSERT psopdf(code) SELECT 'Product' + SPACE(10) + 'Quantity'+ SPACE(10) + 'Price' + SPACE(10) + 'Total')
cn.Execute("INSERT psopdf(code) SELECT REPLACE(SPACE(56), ' ', '_'))
cn.Execute("INSERT psopdf(code) SELECT 'Product1' + SPACE(9) + '10.00 '+ SPACE(10) + '52.30' + SPACE(10) + '5230.0')
cn.Execute("INSERT psopdf(code) SELECT 'Product2' + SPACE(9) + '2.00 '+ SPACE(10) + '10.00' + SPACE(10) + ' 20.0')
cn.Execute("INSERT psopdf(code) SELECT REPLACE(SPACE(56), ' ', '_'))
cn.Execute("INSERT psopdf(code) SELECT SPACE(50) + '5250.0'))
'Now we call the stored procedure
'with no filename in order to
'generate it from ASP:
Set rs = Con.Execute("sql2pdf 'TEXTPDF'")
'If we have any result, we can specify
'that the content type of the document wich will
'be downloaded is a PDF file
If not rs.EOF Then
Response.buffer = true
with response
.Clear
.ContentType = "application/pdf"
.Charset = "UTF-8"
.AddHeader "Content-Disposition", _
"attachment;filename=test.pdf"
end with
'We write out the PDF content!
while not rs.EOF
response.write rs.Fields("code") & vbcrlf
rs.MoveNext
wend
'That'all! We can stop the script here!
Response.Flush
Response.end
end if
cn.Close
'Clean memory
Set cn = Nothing
'Create ADODB connection object
'Set cn = Server.CreateObject("ADODB.Connection")
'Fill the table:
cn.Execute("INSERT psopdf(code) SELECT SPACE(60) + 'COMPANY LTD'"))
cn.Execute("INSERT psopdf(code) SELECT SPACE(60) + 'COMPANY ADDRESS')
cn.Execute("INSERT psopdf(code) SELECT SPACE(60) + 'STREET NAME & No')
cn.Execute("INSERT psopdf(code) SELECT ' ')
cn.Execute("INSERT psopdf(code) SELECT SPACE(34) + 'BILL OF SALE')
cn.Execute("INSERT psopdf(code) SELECT ' ')
cn.Execute("INSERT psopdf(code) SELECT 'Product' + SPACE(10) + 'Quantity'+ SPACE(10) + 'Price' + SPACE(10) + 'Total')
cn.Execute("INSERT psopdf(code) SELECT REPLACE(SPACE(56), ' ', '_'))
cn.Execute("INSERT psopdf(code) SELECT 'Product1' + SPACE(9) + '10.00 '+ SPACE(10) + '52.30' + SPACE(10) + '5230.0')
cn.Execute("INSERT psopdf(code) SELECT 'Product2' + SPACE(9) + '2.00 '+ SPACE(10) + '10.00' + SPACE(10) + ' 20.0')
cn.Execute("INSERT psopdf(code) SELECT REPLACE(SPACE(56), ' ', '_'))
cn.Execute("INSERT psopdf(code) SELECT SPACE(50) + '5250.0'))
'Now we call the stored procedure
'with no filename in order to
'generate it from ASP:
Set rs = Con.Execute("sql2pdf 'TEXTPDF'")
'If we have any result, we can specify
'that the content type of the document wich will
'be downloaded is a PDF file
If not rs.EOF Then
Response.buffer = true
with response
.Clear
.ContentType = "application/pdf"
.Charset = "UTF-8"
.AddHeader "Content-Disposition", _
"attachment;filename=test.pdf"
end with
'We write out the PDF content!
while not rs.EOF
response.write rs.Fields("code") & vbcrlf
rs.MoveNext
wend
'That'all! We can stop the script here!
Response.Flush
Response.end
end if
cn.Close
'Clean memory
Set cn = Nothing
Here is the final result we should have :
Now we just write a simple page in order to call the first one wich will propose us a pdf file to download !
'The script displayLink.asp :
<html>
<head>
<title>Click on the link to generate a new PDF!</title>
</head>
<body>
<a href="Generate_pdf.asp">Download the file!</a>
</body>
</html>
Part I : Create the procedure.
<html>
<head>
<title>Click on the link to generate a new PDF!</title>
</head>
<body>
<a href="Generate_pdf.asp">Download the file!</a>
</body>
</html>