saving pdf file to sql server

[andy_ro]

Hi group,

I have an web application where the user can upload a pdf file. This file is
stored in a table, in a column of type ntext.
The user can later request the content of this column, and the application
should open a new page and load the pdf content.

My problem:
If I read the uploaded pdf file into a string variable and then I
response.write it right away to another page, it shows fine (I can see a pdf
document opened with acrobat reader).
But if I save the string in the sql database and then later retreive it from
there, it will not show anymore.
It seems like it is being somehow transformed during saving to/retreiving
from the sqlserver ...

Any ideas ?

thanks,
Andrei.

Following is the code I use. The commented block sends back the pdf info to
the browser.
Dim mf As HttpPostedFile = myfile.PostedFi le

Dim strFile As String = myfile.Value

Dim nFileLen As Integer = mf.ContentLengt h

Dim b(nFileLen) As Byte

mf.InputStream. Read(b, 0, nFileLen)

Dim str As String = Encoding.Unicod e.GetChars(b)

str = Replace(str, "'", "''")

'str = Replace(str, "''", "'")

'Dim b1() As Byte

'b1 = Encoding.Unicod e.GetBytes(str)

'Response.Conte ntType = "applicatio n/pdf"

'Response.Binar yWrite(b1)

'Response.Flush ()

'Response.Close ()

'save file to DB

Dim cn As New SqlClient.SqlCo nnection("serve r=(local);initi al
catalog=atest;i ntegrated security=SSPI;" )

cn.Open()

Dim cmd As New SqlClient.SqlCo mmand("insert into cust (name, info) values("
& _

"'" & strFile & "','" & str & "')", cn)

cmd.ExecuteNonQ uery()

...........
Dim cmd As New SqlCommand("sel ect name,info from cust where nume='" &
cboFis.Selected Value & "'", cn)

Dim dr As SqlDataReader

dr = cmd.ExecuteRead er()

Dim str As String

Dim b() As Byte

If dr.Read() Then

str = dr("info")

str = Replace(str, "''", "'")

b = Encoding.Unicod e.GetBytes(str)

Response.ClearC ontent()

Response.ClearH eaders()

Response.Conten tType = "applicatio n/pdf"

Response.Binary Write(b)

Response.Flush( )

Response.Close( )

End If

[Natty Gur]

Hi,

I think it is better to save the PDF file as binary data in the SQL
server instead of translating it from / to string.

Natty Gur, CTO
Dao2Com Ltd.
28th Baruch Hirsch st. Bnei-Brak
Israel , 51114

Phone Numbers:
Office: [phone]
Fax: [phone]
Mobile: [phone]

Know the overall picture
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[PJ]

use the Image datatype in sqlserver. Put the .InputStream from the
PostedFile into a byte array. The byte array will be your parameter into
the stored proc that takes an image datatype. Careful if you happen to be
deriving parameters from the proc...as .net will not type the Image SqlType
correctly....I believe it makes it ntext or something....

~PJ

"andy_ro" <an*****@videot ron.ca> wrote in message
news:O9******** ******@TK2MSFTN GP10.phx.gbl...

Hi group,

I have an web application where the user can upload a pdf file. This file is stored in a table, in a column of type ntext.
The user can later request the content of this column, and the application
should open a new page and load the pdf content.

My problem:
If I read the uploaded pdf file into a string variable and then I
response.write it right away to another page, it shows fine (I can see a pdf document opened with acrobat reader).
But if I save the string in the sql database and then later retreive it from there, it will not show anymore.
It seems like it is being somehow transformed during saving to/retreiving
from the sqlserver ...

Any ideas ?

thanks,
Andrei.

Following is the code I use. The commented block sends back the pdf info to the browser.
Dim mf As HttpPostedFile = myfile.PostedFi le

Dim strFile As String = myfile.Value

Dim nFileLen As Integer = mf.ContentLengt h

Dim b(nFileLen) As Byte

mf.InputStream. Read(b, 0, nFileLen)

Dim str As String = Encoding.Unicod e.GetChars(b)

str = Replace(str, "'", "''")

'str = Replace(str, "''", "'")

'Dim b1() As Byte

'b1 = Encoding.Unicod e.GetBytes(str)

'Response.Conte ntType = "applicatio n/pdf"

'Response.Binar yWrite(b1)

'Response.Flush ()

'Response.Close ()

'save file to DB

Dim cn As New SqlClient.SqlCo nnection("serve r=(local);initi al
catalog=atest;i ntegrated security=SSPI;" )

cn.Open()

Dim cmd As New SqlClient.SqlCo mmand("insert into cust (name, info) values(" & _

"'" & strFile & "','" & str & "')", cn)

cmd.ExecuteNonQ uery()

..........
Dim cmd As New SqlCommand("sel ect name,info from cust where nume='" &
cboFis.Selected Value & "'", cn)

Dim dr As SqlDataReader

dr = cmd.ExecuteRead er()

Dim str As String

Dim b() As Byte

If dr.Read() Then

str = dr("info")

str = Replace(str, "''", "'")

b = Encoding.Unicod e.GetBytes(str)

Response.ClearC ontent()

Response.ClearH eaders()

Response.Conten tType = "applicatio n/pdf"

Response.Binary Write(b)

Response.Flush( )

Response.Close( )

End If

[andrei]

Thank you, Natty and PJ,

I used an Image field in SQLServer with a stored procedure that writes to it
and it works perfect.

Andrei.

"andy_ro" <an*****@videot ron.ca> wrote in message
news:O9******** ******@TK2MSFTN GP10.phx.gbl...

Hi group,

I have an web application where the user can upload a pdf file. This file is stored in a table, in a column of type ntext.
The user can later request the content of this column, and the application
should open a new page and load the pdf content.

My problem:
If I read the uploaded pdf file into a string variable and then I
response.write it right away to another page, it shows fine (I can see a pdf document opened with acrobat reader).
But if I save the string in the sql database and then later retreive it from there, it will not show anymore.
It seems like it is being somehow transformed during saving to/retreiving
from the sqlserver ...

Any ideas ?

thanks,
Andrei.

Following is the code I use. The commented block sends back the pdf info to the browser.
Dim mf As HttpPostedFile = myfile.PostedFi le

Dim strFile As String = myfile.Value

Dim nFileLen As Integer = mf.ContentLengt h

Dim b(nFileLen) As Byte

mf.InputStream. Read(b, 0, nFileLen)

Dim str As String = Encoding.Unicod e.GetChars(b)

str = Replace(str, "'", "''")

'str = Replace(str, "''", "'")

'Dim b1() As Byte

'b1 = Encoding.Unicod e.GetBytes(str)

'Response.Conte ntType = "applicatio n/pdf"

'Response.Binar yWrite(b1)

'Response.Flush ()

'Response.Close ()

'save file to DB

Dim cn As New SqlClient.SqlCo nnection("serve r=(local);initi al
catalog=atest;i ntegrated security=SSPI;" )

cn.Open()

Dim cmd As New SqlClient.SqlCo mmand("insert into cust (name, info) values(" & _

"'" & strFile & "','" & str & "')", cn)

cmd.ExecuteNonQ uery()

..........
Dim cmd As New SqlCommand("sel ect name,info from cust where nume='" &
cboFis.Selected Value & "'", cn)

Dim dr As SqlDataReader

dr = cmd.ExecuteRead er()

Dim str As String

Dim b() As Byte

If dr.Read() Then

str = dr("info")

str = Replace(str, "''", "'")

b = Encoding.Unicod e.GetBytes(str)

Response.ClearC ontent()

Response.ClearH eaders()

Response.Conten tType = "applicatio n/pdf"

Response.Binary Write(b)

Response.Flush( )

Response.Close( )

End If