error downloading .pdf from SQL database

[Matt Mercer]

Hi all,

Well, thanks to some smart/helpful people here my upload and download
section of my website works great! I am allowing users to upload
directly into the SQL database but files cannot exceed 1 Meg (actually
1.2M but I tell them 1 meg) and a max of five uploads per incident (it
is a security incident reporting system)

I do not want to redesign this now that it is working, but I cannot
download pdfs! Acrobat tells me the file is corrupt (reader version
6). This happens if I open or save to disk and open later. Every other
file type I have tried works great.

As users upload attachments, a datagrid appears with the option to
view or delete attachments. Below is the code to view (which works for
all but pdf) and the next sub is the upload routine (might be
important, eh). Forgive my coding...I am new and self-taught as you
will undoubtedly notice Thanks in advance.



Sub dgrdAttachment_click (sender As Object, e as
DataGridCommandEventArgs)
Dim conMatt_I3 As SqlConnection
conMatt_I3 = New SqlConnection(
"Server=localhost;uid=sa;pwd=sa;database=Matt_I3" )

conMatt_I3.Open()

If e.commandname="View" Then

Dim buttonColumn as TableCell = e.Item.Cells(0)
viewstate ("attachment_id") = buttonColumn.text

Dim cmdLoadAttachment As SqlCommand
Dim dtrLoadAttachment As SqlDataReader

cmdLoadAttachment = New SqlCommand ("SELECT * FROM attachment WHERE
attachment_id='" & viewstate ("attachment_id") & "'", conMatt_I3)
dtrLoadAttachment = cmdLoadAttachment.ExecuteReader()
dtrLoadAttachment.Read()

Response.ContentType =
dtrLoadAttachment("Attachment_ContentType").ToString
Response.Outputstream.Write(Ctype(dtrLoadAttachment("Attachment_FileData"),
Byte() ), 0, Cint(dtrLoadAttachment("Attachment_FileSize")))
Response.AddHeader("Content-Disposition", "attachment;filename=" +
dtrLoadAttachment("Attachment_FileName").ToString())

End If


-----------------------------------------------------------------

Sub attachmentSubmit_Click ( s As Object, e As EventArgs )
Dim conMatt_I3 As SqlConnection
Dim cmdCheckFiveAttachments As SqlCommand
Dim cmdInsertAttachment As SqlCommand
Dim intCheckFiveAttachments

conMatt_I3 = New SqlConnection(
"Server=localhost;uid=sa;pwd=sa;database=Matt_I3" )
conMatt_I3.Open()

cmdCheckFiveAttachments = New SqlCommand ("SELECT Count(*) FROM
attachment WHERE incident_number='" & viewstate
("intMaxIncidentNumber") & "'", conMatt_I3)

intCheckFiveAttachments = cmdCheckFiveAttachments.ExecuteScalar()
If intCheckFiveAttachments = 5 Then
lblError.text = "Only five attachments are allowed per Incident.
Please delete one and try again."
Else

cmdInsertAttachment = New SqlCommand ("Insert Attachment
(attachment_filename, attachment_description, incident_number,
attachment_filesize, attachment_filedata, attachment_contenttype)
Values ( @attachment_filename, @attachment_description,
@incident_number, @attachment_filesize, @attachment_filedata,
@attachment_contenttype )", conMatt_I3)


Dim iLength As Integer = CType(File1.PostedFile.InputStream.Length,
Integer)
If iLength = 0 Then Exit Sub 'not a valid file
Dim sContentType As String = File1.PostedFile.ContentType
Dim sFileName As String, i As Integer
Dim bytContent As Byte()
ReDim bytContent(iLength) 'byte array, set to file size

'strip the path off the filename
i = InStrRev(File1.PostedFile.FileName.Trim, "\")
If i = 0 Then
sFileName = File1.PostedFile.FileName.Trim
Else
sFileName = Right(File1.PostedFile.FileName.Trim,
Len(File1.PostedFile.FileName.Trim) - i)
End If

File1.PostedFile.InputStream.Read(bytContent, 0, iLength)
If attachment_description.text = "" Then
lblError.text = "You must give the file a description"
Else
If iLength > 1300000 Then
lblError.text = "Your file must be under 1 Meg"
Else
Try
cmdInsertAttachment.Parameters.Add( "@Attachment_FileName",
sFileName )
cmdInsertAttachment.Parameters.Add( "@Attachment_FileSize",
iLength )
cmdInsertAttachment.Parameters.Add( "@Attachment_FileData",
bytContent )
cmdInsertAttachment.Parameters.Add( "@Attachment_ContentType",
sContentType )
cmdInsertAttachment.Parameters.Add( "@Attachment_Description",
attachment_description.text )
cmdInsertAttachment.Parameters.Add( "@incident_number",
viewstate("intMaxIncidentNumber") )
cmdInsertAttachment.ExecuteNonQuery()
Catch ex As Exception
'Handle your database error here
conMatt_I3.Close()
End Try
End If
End If

End If

 

[Ken Cox [Microsoft MVP]]

Hi Matt,

It looks like you're not sending out the data the right way for format. When
I've pushed out pdfs, I've used this code:

Dim byteTemp As Byte()
Dim strFileNameToDisplay As String
strFileNameToDisplay = "Documentname.pdf"
' Put the document bytes into a byte array
byteTemp = 'Get the bytes from the database here
Response.Buffer = True
Response.Clear()
Response.ClearContent()
Response.ClearHeaders()
Response.ContentType = "application/pdf"
Response.AddHeader("Content-Disposition", _
"attachment;filename=" & strFileNameToDisplay)
Response.BinaryWrite(byteTemp)
Response.End()

You'll want to check with this article on using BLOB data in SQL server:

http://support.microsoft.com/default.aspx?scid=kb;en-us;326502

Let us know what works?

Ken
Microsoft MVP [ASP.NET]

 

[Matt Mercer]

Response.AddHeader("Content-Length",
dtrLoadAttachment("Attachment_FileSize").ToString())

Adding this line to my code before outputting the file has fixed it
for me. Weird.