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.PostedFile

Dim strFile As String = myfile.Value

Dim nFileLen As Integer = mf.ContentLength

Dim b(nFileLen) As Byte

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

Dim str As String = Encoding.Unicode.GetChars(b)

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

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

'Dim b1() As Byte

'b1 = Encoding.Unicode.GetBytes(str)

'Response.ContentType = "application/pdf"

'Response.BinaryWrite(b1)

'Response.Flush()

'Response.Close()

'save file to DB

Dim cn As New SqlClient.SqlConnection("server=(local);initial
catalog=atest;integrated security=SSPI;")

cn.Open()

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

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

cmd.ExecuteNonQuery()

...........
Dim cmd As New SqlCommand("select name,info from cust where nume='" &
cboFis.SelectedValue & "'", cn)

Dim dr As SqlDataReader

dr = cmd.ExecuteReader()

Dim str As String

Dim b() As Byte

If dr.Read() Then

str = dr("info")

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

b = Encoding.Unicode.GetBytes(str)

Response.ClearContent()

Response.ClearHeaders()

Response.ContentType = "application/pdf"

Response.BinaryWrite(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: +972-(0)3-5786668
Fax: +972-(0)3-5703475
Mobile: +972-(0)58-888377

Know the overall picture


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
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" <andy_ro@videotron.ca> wrote in message
news:O9OaIpsODHA.2476@TK2MSFTNGP10.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.PostedFile
>
> Dim strFile As String = myfile.Value
>
> Dim nFileLen As Integer = mf.ContentLength
>
> Dim b(nFileLen) As Byte
>
> mf.InputStream.Read(b, 0, nFileLen)
>
> Dim str As String = Encoding.Unicode.GetChars(b)
>
> str = Replace(str, "'", "''")
>
> 'str = Replace(str, "''", "'")
>
> 'Dim b1() As Byte
>
> 'b1 = Encoding.Unicode.GetBytes(str)
>
> 'Response.ContentType = "application/pdf"
>
> 'Response.BinaryWrite(b1)
>
> 'Response.Flush()
>
> 'Response.Close()
>
> 'save file to DB
>
> Dim cn As New SqlClient.SqlConnection("server=(local);initial
> catalog=atest;integrated security=SSPI;")
>
> cn.Open()
>
> Dim cmd As New SqlClient.SqlCommand("insert into cust (name, info)

values("

> & _
>
> "'" & strFile & "','" & str & "')", cn)
>
> cmd.ExecuteNonQuery()
>
> ..........
> Dim cmd As New SqlCommand("select name,info from cust where nume='" &
> cboFis.SelectedValue & "'", cn)
>
> Dim dr As SqlDataReader
>
> dr = cmd.ExecuteReader()
>
> Dim str As String
>
> Dim b() As Byte
>
> If dr.Read() Then
>
> str = dr("info")
>
> str = Replace(str, "''", "'")
>
> b = Encoding.Unicode.GetBytes(str)
>
> Response.ClearContent()
>
> Response.ClearHeaders()
>
> Response.ContentType = "application/pdf"
>
> Response.BinaryWrite(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" <andy_ro@videotron.ca> wrote in message
news:O9OaIpsODHA.2476@TK2MSFTNGP10.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.PostedFile
>
> Dim strFile As String = myfile.Value
>
> Dim nFileLen As Integer = mf.ContentLength
>
> Dim b(nFileLen) As Byte
>
> mf.InputStream.Read(b, 0, nFileLen)
>
> Dim str As String = Encoding.Unicode.GetChars(b)
>
> str = Replace(str, "'", "''")
>
> 'str = Replace(str, "''", "'")
>
> 'Dim b1() As Byte
>
> 'b1 = Encoding.Unicode.GetBytes(str)
>
> 'Response.ContentType = "application/pdf"
>
> 'Response.BinaryWrite(b1)
>
> 'Response.Flush()
>
> 'Response.Close()
>
> 'save file to DB
>
> Dim cn As New SqlClient.SqlConnection("server=(local);initial
> catalog=atest;integrated security=SSPI;")
>
> cn.Open()
>
> Dim cmd As New SqlClient.SqlCommand("insert into cust (name, info)

values("

> & _
>
> "'" & strFile & "','" & str & "')", cn)
>
> cmd.ExecuteNonQuery()
>
> ..........
> Dim cmd As New SqlCommand("select name,info from cust where nume='" &
> cboFis.SelectedValue & "'", cn)
>
> Dim dr As SqlDataReader
>
> dr = cmd.ExecuteReader()
>
> Dim str As String
>
> Dim b() As Byte
>
> If dr.Read() Then
>
> str = dr("info")
>
> str = Replace(str, "''", "'")
>
> b = Encoding.Unicode.GetBytes(str)
>
> Response.ClearContent()
>
> Response.ClearHeaders()
>
> Response.ContentType = "application/pdf"
>
> Response.BinaryWrite(b)
>
> Response.Flush()
>
> Response.Close()
>
> End If
>
>
>