ASP.Net PDF document in Database error: There was an error opening this document. The file is damaged and could not be repaired.

MarkLewis
 
on Feb 28, 2014 12:19 PM
3999 Views

I am trying to download a pdf stored in a data table in SQL Server 2008.  The principal fields in the data table are

ObjectId - a guid

the Binary data file - varbinary(max)

the file name - nvarchar(500)

File Hash - a guid( not sure what this is for)

I have set up an ASP page with the Page_Load of

Imports System.Net
Imports System.Data
Imports System.Data.SqlClient

 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim sSQL As String = "SELECT ObjectID, FileName, [File] " & _
                    "FROM v_B2WFileAttachmentSummary WHERE (Job_JobNumber = N'386294')"

        Dim tbl As New DataTable

        Using cnn As New SqlConnection(clsDBActions.RemoteJobcnn)
            cnn.Open()
            Using cmd As New SqlCommand(sSQL, cnn)
                Dim rdr As SqlDataReader = cmd.ExecuteReader
                If rdr.HasRows Then
                    tbl.Load(rdr)
                End If
                If Not IsNothing(tbl) Then
                    Dim bytes As Byte() = DirectCast(tbl.Rows(0)("File"), [Byte]())
                    Response.Clear()
                    Response.Buffer = True
                    Response.Charset = ""
                    Response.Cache.SetCacheability(HttpCacheability.NoCache)
                    Response.ContentType = "application/pdf"
                    Response.AddHeader("content-disposition", "attachment; filename=" & tbl.Rows(0)("FileName").ToString())
                    Response.BinaryWrite(bytes)
                    Response.Flush()
                    Response.[End]()
                End If
            End Using
        End Using
    End Sub

The sql statement is set to go get a pdf that is stored on the server.  When i open the page I get

an Adobe messagebox stating "There was an error opening this document. The file is damaged and could not be repaired."

 

Everything that i have researched show this should work.  the field File Hash is the only field that I don't know why it is part of the table. 

 

Any help would greatly be appreciated.


 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
MarkLewis
 
on Feb 28, 2014 03:13 PM

Thanks Andrea for your quick response.  I did some further research and came across this article.

http://www.dotnetthoughts.net/how-to-store-and-retrieve-files-from-sql-server-database/

I made the following changes to my test case and it works

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim sSQL As String = "SELECT ObjectID, FileName, [File] " & _
                    "FROM v_B2WFileAttachmentSummary WHERE (Job_JobNumber = N'309285')"

        Dim tbl As New DataTable

        Dim cnn As New SqlConnection(clsDBActions.RemoteJobcnn)
        cnn.Open()
        Dim cmd As New SqlCommand(sSQL, cnn)
        Dim reader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        If reader.HasRows Then
            reader.Read()
        End If
        If Not IsNothing(tbl) Then
            Dim content As Byte() = TryCast(reader("File"), Byte())
            Dim filename As String = reader("FileName").ToString()
            Response.Clear()
            Response.ClearContent()
            Response.AddHeader("content-disposition", "attachment; filename=" & filename)
            Response.AddHeader("content-Length", content.Length.ToString())
            Response.OutputStream.Write(content, 0, content.Length)
            Response.[End]()
        End If
        cnn.Close()
    End Sub

So for the moment I will run with this solution.  I will mark this case closed.  Again, thank you for your help