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(strDBFilePa th) '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("Conten t-Disposit ion", "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.C ontentLeng th
Dim imgBinaryData(intLen) As Byte
file_Document.PostedFile.I nputStream .Read(imgB inaryData, 0, intLen)
strSQL = "Select * From AttachedDocuments"
Dim da As New SqlClient.SqlDataAdapter(s trSQL, 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.SqlCommandBuilde r(da)
da.Update(dt)
oconn.Close()
da.Dispose()
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(strDBFilePa
Response.AddHeader("Conten
Response.Charset = ""
Response.ContentType = strContType
Response.BinaryWrite(CType
Response.End()
Save file code
'file_Document is FileUpload control field
Dim intLen As Int32 = file_Document.PostedFile.C
Dim imgBinaryData(intLen) As Byte
file_Document.PostedFile.I
strSQL = "Select * From AttachedDocuments"
Dim da As New SqlClient.SqlDataAdapter(s
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.SqlCommandBuilde
da.Update(dt)
oconn.Close()
da.Dispose()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Please update line 65:
will encode the FileName, that is required.
Response.AddHeader("Content-Disposition", "attachment;filename=" & Server.UrlEncode(fileName))
Server.UrlEncode(fileName)
>Dim strContType As String = GetContentType(strDBFilePa th) '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.openxmlfor mats-offic edocument. spreadshee tml.sheet
I see you have "application/msword" for both doc and docx.
For docx: application/vnd.openxmlfor mats-offic edocument. wordproces singml.doc ument
Are you dealing with xlsx files as xls as well?
The mime type for xlsx is: application/vnd.openxmlfor
I see you have "application/msword" for both doc and docx.
For docx: application/vnd.openxmlfor
ASKER
Thanks for all the help so far - we are testing all these solutions and will advise back asap.
Mike
Mike
ASKER
Needs Update to line 65 :
Response.AddHeader("Conten t-Disposit ion", "attachment;filename=" & Server.UrlEncode(fileName) )
Response.AddHeader("Conten
Hi, you had to mark this as a solution too I think:
https://www.experts-exchange.com/questions/27023194/Uploaded-then-downloaded-same-xlsx-file-corrupts-in-SQL.html?cid=1065&anchorAnswerId=35713184#a35713184
https://www.experts-exchange.com/questions/27023194/Uploaded-then-downloaded-same-xlsx-file-corrupts-in-SQL.html?cid=1065&anchorAnswerId=35713184#a35713184
ASKER
You mean the post referring to
Response.AddHeader("Conten t-Disposit ion", "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.
Response.AddHeader("Conten
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.
ASKER
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 ;-)
ASKER
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
Cheers
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