[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Uploaded then downloaded same .xlsx file corrupts in SQL

Posted on 2011-05-06
13
Medium Priority
?
1,331 Views
Last Modified: 2012-05-11
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()



0
Comment
Question by:mike_mpfax
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 9

Expert Comment

by:kaminda
ID: 35711798
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
0
 
LVL 17

Accepted Solution

by:
Carlos Villegas earned 2000 total points
ID: 35712992
Hi, I made this example for you based in your code, please try it:
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<%@ Page Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
    'YOUR CONNECTION STRING!
    'INFO: http://connectionstrings.com/sql-server-2005
    Dim myConnectionString As String = "Data Source=YOURDBSERVER;Initial Catalog=YOURDBNAME;User Id=YOURUSER;Password=YOURPASSWORD;"
    
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Using cn As New SqlConnection(myConnectionString)
            Dim cm As New SqlCommand("INSERT [dbo].[AttachedDocuments] (Document_FileName, Document_File) VALUES (@pDocument_FileName, @pDocument_File)", cn)
            cm.Parameters.Add("pDocument_FileName", SqlDbType.NVarChar, 50).Value = FileUpload1.FileName
            cm.Parameters.Add("pDocument_File", SqlDbType.Image).Value = FileUpload1.FileBytes
            cn.Open()
            cm.ExecuteNonQuery()
        End Using
        
        LoadAvailableFiles()
    End Sub

    Sub LoadAvailableFiles()
        Using cn As New SqlConnection(myConnectionString)
            Dim cm As New SqlCommand("SELECT RecNum, Document_FileName FROM [dbo].[AttachedDocuments] ORDER BY RecNum", cn)
            cn.Open()
            ListBox1.DataValueField = "RecNum"
            ListBox1.DataTextField = "Document_FileName"
            ListBox1.DataSource = cm.ExecuteReader()
            ListBox1.DataBind()
        End Using
    End Sub
    
    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        If ListBox1.SelectedItem Is Nothing Then
            Exit Sub
        End If
        
        Dim fileContents() As Byte
        Using cn As New SqlConnection(myConnectionString)
            Dim cm As New SqlCommand("SELECT Document_File FROM [dbo].[AttachedDocuments] WHERE RecNum= @pRecNum", cn)
            cm.Parameters.Add("pRecNum", SqlDbType.Int).Value = ListBox1.SelectedItem.Value
            cn.Open()
            fileContents = cm.ExecuteScalar()
        End Using
        
        Dim fileName As String = ListBox1.SelectedItem.Text
        Dim contentType As String
        Select Case System.IO.Path.GetExtension(fileName)
            Case ".doc", ".docx"
                contentType = "application/msword"
            Case ".pdf"
                contentType = "application/pdf"
            Case ".xls", ".xlsx"
                contentType = "application/vnd.ms-excel"
            Case Else
                contentType = "application/octet-stream"
        End Select
        
        Response.Clear()
        Response.Cache.SetCacheability(HttpCacheability.NoCache)
        Response.Cache.SetNoStore()
        Response.ContentType = contentType
        Response.AddHeader("Content-Disposition", "attachment;filename=" & fileName)
        Response.BinaryWrite(fileContents)
        Response.End()
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
        If Not IsPostBack Then
            LoadAvailableFiles()
        End If
    End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:FileUpload ID="FileUpload1" runat="server" />
        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Upload" />
        <br />
        <br />
        Download File:<br />
        <asp:ListBox ID="ListBox1" runat="server" Width="220px"></asp:ListBox>
        <br />
        <asp:Button ID="Button2" runat="server" OnClick="Button2_Click" Text="Download" />
    </div>
    </form>
</body>
</html>

Open in new window


Or download the aspx file:
UploadFileExample.aspx

Remember change the connection string var: myConnectionString

I hope this help.
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35713184
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35716113
>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
0
 

Author Comment

by:mike_mpfax
ID: 35716127
Thanks for all the help so far - we are testing all these solutions and will advise back asap.

Mike
0
 

Author Closing Comment

by:mike_mpfax
ID: 35721414
Needs Update to line 65 :

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

Expert Comment

by:Carlos Villegas
ID: 35721500
0
 

Author Comment

by:mike_mpfax
ID: 35721619
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.
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35721767
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.
0
 

Author Comment

by:mike_mpfax
ID: 35721775
OK !  Anyway - thanks so much for yr help - it worked and got us out of a real problem !
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35722312
It's also worth noting CodeCruiser corrected the mime types for docx and xlsx files in post http://#a35716113 ;-)
0
 

Author Comment

by:mike_mpfax
ID: 35722492
Yes indeed - We didn't mean to forget anyone.  We really appreciate all help offered from everyone - thanks again.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35722654
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
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Screencast - Getting to Know the Pipeline
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question