Generating pdf file and show image and data in pdf from sql server database in classic asp

Question

1.00/5 (1 vote)

See more:

hi everyone, please help me, i have generate a pdf using stored procedure and classic asp, what i'm want to show a image in pdf with stored procedure

My Code for generating pdf is running fine and this is here:
ASP Page Code:--

<%@ Language=VBScript %>

<%

Set cn = Server.CreateObject("ADODB.Connection")
const ConString=" Provider=SQLOLEDB.1;server="";Database=dbAspClassic;uid="";pwd="";"

cn.Open(ConString)

set rs=CreateObject("adodb.recordset")
rs.ActiveConnection=cn

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'")

'Set rs = cn.Execute("Select * from psopdf ")
Set rs = cn.Execute("sql2pdf 'Pardeep'")

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
Set cn = Nothing

%>

Stored procedure is::

-- If you want to delete a preceding version of our procedure
-- uncomment these lines :

-- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sql2pdf]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
-- drop procedure [dbo].[sql2pdf]
-- GO

create PROCEDURE sql2pdf
@filename VARCHAR(100)
AS
SET NOCOUNT ON
CREATE TABLE #pdf (idnumber INT IDENTITY(1,1)
,code NVARCHAR(200))
CREATE TABLE #xref (idnumber INT IDENTITY(1,1)
,code VARCHAR(30))
CREATE TABLE #text (idnumber INT IDENTITY(1,1)
,code VARCHAR(200))

DECLARE @end VARCHAR(7),
@beg VARCHAR(7),
@a1 VARCHAR(3),
@a2 VARCHAR(3),
@ad VARCHAR(5),
@cr VARCHAR(8),
@pr VARCHAR(9),
@ti VARCHAR(6),
@xstr VARCHAR(10),
@page VARCHAR(8000),
@pdf VARCHAR(100),
@trenutniRed NVARCHAR(200),
@rows INT,
@ofset INT,
@len INT,
@nopg INT,
@fs INT,
@ole INT,
@x INT,
@file INT,
@object INT

SELECT @pdf = 'C:\' + @filename + '.pdf'
SET @page = ''
SET @nopg = 0
SET @object = 6
SET @end = 'endobj'
SET @beg = ' 0 obj'
SET @a1 = '<<'
SET @a2 = '>>'
SET @ad = ' 0 R'
SET @cr = CHAR(67) + CHAR(114) + CHAR (101) + CHAR(97) + CHAR(116) + CHAR (111) + CHAR(114)
SET @pr = CHAR(80) + CHAR(114) + CHAR (111) + CHAR(100) + CHAR(117) + CHAR (99 ) + CHAR(101) + CHAR(114)
SET @ti = CHAR(84) + CHAR(105) + CHAR (116) + CHAR(108) + CHAR(101)
SET @xstr = ' 00000 n'
SET @ofset = 396
INSERT INTO #xref(code) VALUES ('xref')
INSERT INTO #xref(code) VALUES ('0 10')
INSERT INTO #xref(code) VALUES ('0000000000 65535 f')
INSERT INTO #xref(code) VALUES ('0000000017' + @xstr)
INSERT INTO #xref(code) VALUES ('0000000790' + @xstr)
INSERT INTO #xref(code) VALUES ('0000000869' + @xstr)
INSERT INTO #xref(code) VALUES ('0000000144' + @xstr)
INSERT INTO #xref(code) VALUES ('0000000247' + @xstr)
INSERT INTO #xref(code) VALUES ('0000000321' + @xstr)
INSERT INTO #xref(code) VALUES ('0000000396' + @xstr)
INSERT INTO #pdf (code) VALUES ('%' + CHAR(80) + CHAR(68) + CHAR (70) + '-1.2')
INSERT INTO #pdf (code) VALUES ('%ÓÓÓÓ')
INSERT INTO #pdf (code) VALUES ('1' + @beg)
INSERT INTO #pdf (code) VALUES (@a1)
INSERT INTO #pdf (code) VALUES ('/' + @cr + ' (Dypso BackOffice' + CHAR(80) + CHAR(83) + CHAR (79) + CHAR(80) + CHAR(68) + CHAR (70) + ')')
INSERT INTO #pdf (code) VALUES ('/' + @pr + ' (stored procedure for ms sql)')
INSERT INTO #pdf (code) VALUES ('/' + @ti + ' (SQL2' + CHAR(80) + CHAR(68) + CHAR (70) + ')')
INSERT INTO #pdf (code) VALUES (@a2)
INSERT INTO #pdf (code) VALUES (@end)
INSERT INTO #pdf (code) VALUES ('4' + @beg)
INSERT INTO #pdf (code) VALUES (@a1)
INSERT INTO #pdf (code) VALUES ('/Type /Font')
INSERT INTO #pdf (code) VALUES ('/Subtype /Type1')
INSERT INTO #pdf (code) VALUES ('/Name /F1')
INSERT INTO #pdf (code) VALUES ('/Encoding 5' + @ad)
INSERT INTO #pdf (code) VALUES ('/BaseFont /Courier')
INSERT INTO #pdf (code) VALUES (@a2)
INSERT INTO #pdf (code) VALUES (@end)
INSERT INTO #pdf (code) VALUES ('5' + @beg)
INSERT INTO #pdf (code) VALUES (@a1)
INSERT INTO #pdf (code) VALUES ('/Type /Encoding')
INSERT INTO #pdf (code) VALUES ('/BaseEncoding /WinAnsiEncoding')
INSERT INTO #pdf (code) VALUES (@a2)
INSERT INTO #pdf (code) VALUES (@end)
INSERT INTO #pdf (code) VALUES ('6' + @beg)
INSERT INTO #pdf (code) VALUES (@a1)
INSERT INTO #pdf (code) VALUES (' /Font ' + @a1 + ' /F1 4' + @ad + ' ' + @a2 + ' /ProcSet [ /' + CHAR(80) + CHAR(68) + CHAR (70) + ' /Text ]')
INSERT INTO #pdf (code) VALUES (@a2)
INSERT INTO #pdf (code) VALUES (@end)
INSERT INTO #text(code) (SELECT code FROM psopdf)
SELECT @x = COUNT(*) FROM #text
SELECT @x = (@x / 60) + 1
WHILE @nopg < @x
BEGIN
DECLARE SysKursor INSENSITIVE SCROLL CURSOR
FOR SELECT SUBSTRING((code + SPACE(81)), 1, 80) FROM #text WHERE idnumber BETWEEN ((@nopg * 60) + 1) AND ((@nopg + 1) * 60 )
FOR READ ONLY
OPEN SysKursor
FETCH NEXT FROM SysKursor INTO @trenutniRed
SELECT @object = @object + 1
SELECT @page = @page + ' ' + CAST(@object AS VARCHAR) + @ad
SELECT @len = LEN(@object) + LEN(@object + 1)
INSERT INTO #pdf (code) VALUES (CAST(@object AS VARCHAR) + @beg)
INSERT INTO #pdf (code) VALUES (@a1)
INSERT INTO #pdf (code) VALUES ('/Type /Page')
INSERT INTO #pdf (code) VALUES ('/Parent 3' + @ad)
INSERT INTO #pdf (code) VALUES ('/Resources 6' + @ad)
SELECT @object = @object + 1
INSERT INTO #pdf (code) VALUES ('/Contents ' + CAST(@object AS VARCHAR) + @ad)
INSERT INTO #pdf (code) VALUES (@a2)
INSERT INTO #pdf (code) VALUES (@end)
SELECT @ofset = @len + 86 + @ofset
INSERT INTO #xref(code) (SELECT SUBSTRING('0000000000' + CAST(@ofset AS VARCHAR),
LEN('0000000000' + CAST(@ofset AS VARCHAR)) - 9,
LEN('0000000000' + CAST(@ofset AS VARCHAR))) + @xstr)
INSERT INTO #pdf (code) VALUES (CAST(@object AS VARCHAR) + @beg)
INSERT INTO #pdf (code) VALUES (@a1)
SELECT @object = @object + 1
INSERT INTO #pdf (code) VALUES ('/Length ' + CAST(@object AS VARCHAR) + @ad)
INSERT INTO #pdf (code) VALUES (@a2)
INSERT INTO #pdf (code) VALUES ('stream')
INSERT INTO #pdf (code) VALUES ('BT')
INSERT INTO #pdf (code) VALUES ('/F1 10 Tf')
INSERT INTO #pdf (code) VALUES ('1 0 0 1 50 802 Tm')
INSERT INTO #pdf (code) VALUES ('12 TL')
WHILE @@Fetch_Status = 0
BEGIN
INSERT INTO #pdf (code) VALUES ('T* (' + @trenutniRed + ') Tj')
FETCH NEXT FROM SysKursor INTO @trenutniRed
END
INSERT INTO #pdf (code) VALUES ('ET')
INSERT INTO #pdf (code) VALUES ('endstream')
INSERT INTO #pdf (code) VALUES (@end)
SELECT @rows = (SELECT COUNT(*) FROM #text WHERE idnumber BETWEEN ((@nopg * 60) + 1) AND ((@nopg + 1) * 60 ))* 90 + 45
SELECT @nopg = @nopg + 1
SELECT @len = LEN(@object) + LEN(@object - 1)
SELECT @ofset = @len + 57 + @ofset + @rows
INSERT INTO #xref(code) (SELECT SUBSTRING('0000000000' + CAST(@ofset AS VARCHAR),
LEN('0000000000' + CAST(@ofset AS VARCHAR)) - 9,
LEN('0000000000' + CAST(@ofset AS VARCHAR))) + @xstr)
INSERT INTO #pdf (code) VALUES (CAST(@object AS VARCHAR) + @beg)
INSERT INTO #pdf (code) VALUES (@rows)
INSERT INTO #pdf (code) VALUES (@end)
SELECT @len = LEN(@object) + LEN(@rows)
SELECT @ofset = @len + 18 + @ofset
INSERT INTO #xref(code) (SELECT SUBSTRING('0000000000' + CAST(@ofset AS VARCHAR),
LEN('0000000000' + CAST(@ofset AS VARCHAR)) - 9,
LEN('0000000000' + CAST(@ofset AS VARCHAR))) + @xstr)
CLOSE SysKursor
DEALLOCATE SysKursor
END
INSERT INTO #pdf (code) VALUES ('2' + @beg)
INSERT INTO #pdf (code) VALUES (@a1)
INSERT INTO #pdf (code) VALUES ('/Type /Catalog')
INSERT INTO #pdf (code) VALUES ('/Pages 3' + @ad)
INSERT INTO #pdf (code) VALUES ('/PageLayout /OneColumn')
INSERT INTO #pdf (code) VALUES (@a2)
INSERT INTO #pdf (code) VALUES (@end)
UPDATE #xref SET code = (SELECT code FROM #xref WHERE idnumber = (SELECT MAX(idnumber) FROM #xref)) WHERE idnumber = 5
DELETE FROM #xref WHERE idnumber = (SELECT MAX(idnumber) FROM #xref)
INSERT INTO #pdf (code) VALUES ('3' + @beg)
INSERT INTO #pdf (code) VALUES (@a1)
INSERT INTO #pdf (code) VALUES ('/Type /Pages')
INSERT INTO #pdf (code) VALUES ('/Count ' + CAST(@nopg AS VARCHAR))
INSERT INTO #pdf (code) VALUES ('/MediaBox [ 0 0 595 842 ]')
INSERT INTO #pdf (code) VALUES ('/Kids [' + @page + ' ]')
INSERT INTO #pdf (code) VALUES (@a2)
INSERT INTO #pdf (code) VALUES (@end)
SELECT @ofset = @ofset + 79
UPDATE #xref SET code =(SELECT SUBSTRING('0000000000' + CAST(@ofset AS VARCHAR),
LEN('0000000000' + CAST(@ofset AS VARCHAR)) - 9,
LEN('0000000000' + CAST(@ofset AS VARCHAR))) + @xstr) WHERE idnumber = 6
INSERT INTO #xref(code) VALUES ('trailer')
INSERT INTO #xref(code) VALUES (@a1)
SELECT @object = @object + 1
UPDATE #xref SET code = '0 ' + CAST(@object AS VARCHAR) WHERE idnumber = 2
INSERT INTO #xref(code) VALUES ('/Size ' + CAST(@object AS VARCHAR))
INSERT INTO #xref(code) VALUES ('/Root 2' + @ad)
INSERT INTO #xref(code) VALUES ('/Info 1' + @ad)
INSERT INTO #xref(code) VALUES (@a2)
INSERT INTO #xref(code) VALUES ('startxref')
SELECT @len = LEN(@nopg) + LEN(@page)
SELECT @ofset = @len + 86 + @ofset
INSERT INTO #xref(code) VALUES (@ofset)
INSERT INTO #xref(code) VALUES ('%%' + CHAR(69) + CHAR (79) + CHAR(70))
INSERT INTO #pdf (code) (SELECT code FROM #xref)
SELECT code FROM #pdf
SELECT @trenutniRed = 'del '+ @pdf
EXECUTE @ole = sp_OACreate 'Scripting.FileSystemObject', @fs OUT
EXEC master..xp_cmdshell @trenutniRed, NO_OUTPUT

EXECUTE @ole = sp_OAMethod @fs, 'OpenTextFile', @file OUT, @pdf, 8, 1

IF LEN(@filename)>0
BEGIN
-- Chose to create the file directly with
-- SQL Server

DECLARE SysKursor INSENSITIVE SCROLL CURSOR
FOR SELECT code FROM #pdf ORDER BY idnumber
FOR READ ONLY
OPEN SysKursor
FETCH NEXT FROM SysKursor INTO @trenutniRed
WHILE @@Fetch_Status = 0
BEGIN
EXECUTE @ole = sp_OAMethod @file, 'WriteLine', Null, @trenutniRed
FETCH NEXT FROM SysKursor INTO @trenutniRed
END
CLOSE SysKursor
DEALLOCATE SysKursor
END
ELSE
BEGIN
-- Create the file by calling the stored
-- proc from ASP and get the result back

SELECT code FROM #pdf ORDER BY idnumber
END

DELETE FROM psopdf
EXECUTE @ole = sp_OADestroy @file
EXECUTE @ole = sp_OADestroy @fs

Posted 15-Mar-12 2:21am

Pardeep singh from Chandigarh

Updated 16-Mar-12 20:59pm

v3

Add a Solution

Comments

Sandeep Mewara 12-Jun-12 9:06am

And why would you edit your own question which was marked as solved by you ? Why to pop it out again?

2 solutions

Solution 1

The solution I usually propose to my clients in this situation is to use Sql Server Reporting Services (SSRS). You can use the ReportViewer control included with it in order to generate PDF's, Excel spreadsheets, XML files, CSV files, and others. If you need ad hoc reporting, there is a Report Builder available as well.

Barring that, you can use OpenXml to generate Excel spreadsheets and there are a host of PDF toolkits available

Posted 15-Mar-12 6:37am

Ganesan Senthilvel

Comments

[no name] 16-Mar-12 1:08am

sir i need generate pdf using Stored procedure and then execute this into the asp page

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pardeep singh from Chandigarh · Accepted Answer · 2012-06-11T21:17:00

Solution 2

code works for me by just adding the SET NOCOUNT ON at the top

Posted 11-Jun-12 21:17pm

Pardeep singh from Chandigarh

Updated 2-Dec-16 1:49am

v2

Comments

SumanRoy 21-May-15 7:15am

its note working...Invalid object name 'psopdf'.