Link to home
Start Free TrialLog in
Avatar of mike_mpfax
mike_mpfax

asked on

Uploaded then downloaded same .xlsx file corrupts in SQL

We have an SaaS application that allows users to upload file attachments.  We have noticed that when we attach an Excel spreadsheet file that ends in .xlsx - that it uploads fine - but then when its downloaded Excel has a problem with it and says 'Excel found unreadable content in <filename>. Do you want to recover the contents of this workbook?'

If you say yes then the file does recover and open .. but thats not the point.

We are worried that the file has been changed in someway - these are client files - thay are not ours and the integrity of the file must stay the same as it was when it was uploaded by them.

Can anyone tell us what is going on or what we are doing wrong here ?

Relevant code is below.

We are using SQLServer 2005 and VB.Net

Database Script
CREATE TABLE [dbo].[AttachedDocuments](
[RecNum] [int] IDENTITY(1,1) NOT NULL,
[Document_FileName] [nvarchar](50) NULL,
[Document_File] [image] NULL)

View File Code
        Dim strContType As String = GetContentType(strDBFilePath) 'Function where i get file type for e.g. ("doc", "application/msword") , ("docx", "application/msword") , ("pdf", "application/pdf"), ("xls", "application/vnd.ms-excel")
        Response.AddHeader("Content-Disposition", "attachment;filename=" & strDBFilePath) ' strDBFilePath value we get from database
        Response.Charset = ""
        Response.ContentType = strContType

        Response.BinaryWrite(CType(objFile, Byte())) ' objFile value we get from database - Document_File field
        Response.End()


Save file code
                                                'file_Document is FileUpload control field
                                                               
                    Dim intLen As Int32 = file_Document.PostedFile.ContentLength
                    Dim imgBinaryData(intLen) As Byte
                    file_Document.PostedFile.InputStream.Read(imgBinaryData, 0, intLen)

                    strSQL = "Select * From AttachedDocuments"
                    Dim da As New SqlClient.SqlDataAdapter(strSQL, oconn)
                    Dim dt As New DataTable, dr As DataRow
                    da.Fill(dt)
                    dr = dt.NewRow
                    dr("Document_FileName") = strFileName
                    dr("Document_File") = imgBinaryData
                    dt.Rows.Add(dr)
                    Dim sqlCmdBld As New SqlClient.SqlCommandBuilder(da)
                    da.Update(dt)
                    oconn.Close()
                    da.Dispose()



Avatar of kaminda
kaminda
Flag of Sri Lanka image

I think it is better to use varbinary to store the excel file, I dont know exactly this problem is because of you are using IMAGE datatype. But try using varbinary for Excel files.

Another best way is using an XML type column and store excel file in that column. this is a good article

http://www.codeproject.com/KB/office/excel2xml.aspx
ASKER CERTIFIED SOLUTION
Avatar of Carlos Villegas
Carlos Villegas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Please update line 65:
Response.AddHeader("Content-Disposition", "attachment;filename=" & Server.UrlEncode(fileName))

Open in new window

Server.UrlEncode(fileName) will encode the FileName, that is required.
Avatar of Nasir Razzaq
>Dim strContType As String = GetContentType(strDBFilePath) 'Function where i get file type for e.g. ("doc", "application/msword") , ("docx", "application/msword") , ("pdf", "application/pdf"), ("xls", "application/vnd.ms-excel")

Are you dealing with xlsx files as xls as well?

The mime type for xlsx is: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

I see you have "application/msword" for both doc and docx.
For docx: application/vnd.openxmlformats-officedocument.wordprocessingml.document
Avatar of mike_mpfax
mike_mpfax

ASKER

Thanks for all the help so far - we are testing all these solutions and will advise back asap.

Mike
Needs Update to line 65 :

Response.AddHeader("Content-Disposition", "attachment;filename=" & Server.UrlEncode(fileName))
You mean the post referring to

Response.AddHeader("Content-Disposition", "attachment;filename=" & Server.UrlEncode(fileName))

I marked the main post as the solution and added a comment regarding the updated line : I cannot find a way of indicating that 2 suggestions are the solutiuon - I think you can only mark one solution as the Accepted one. Thats why I added the extra comment - but if I'm wrong then I will mark that as well.
Ok I understand, I think that you can mark one as solution and the other one as an assisted solution... well that is fine, the important is that you mention that.
OK !  Anyway - thanks so much for yr help - it worked and got us out of a real problem !
It's also worth noting CodeCruiser corrected the mime types for docx and xlsx files in post http://#a35716113 ;-)
Yes indeed - We didn't mean to forget anyone.  We really appreciate all help offered from everyone - thanks again.
Understood :) I wasn't really part of the thread. Just pointing out the correction for future readers ie So they know they need to change the mime type too.  

Cheers