Solved

How to retrieve open xml documents from SQL

Posted on 2009-06-29
13
773 Views
Last Modified: 2012-05-07
I'm able to store and retrieve any document/image type in our sql db...with the exception of any Office 2007 document type (ie.  docx, xlsx, etc).  I've read up on 2007 file types are basically zip file with other things which may cause my problems.  When I click on a link to open the document, I'm prompted "The Office Open XML file test[1].docx cannot be opened because there are problems with the contents.  Details:  The file is corrupt and cannot be opened."  I also do not know why it's seeing the file name as test[1], because I uploaded it as just test.docx.  Any help will be greatly appreciated.
'inserting attachments
    Protected Sub btnInsert_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnInsert.Click
        'insert record data and return id for the attachment
        InsertUpdate("Weekly", "")
 
        'Make sure a file has been successfully uploaded
        If FileUpload1.PostedFile Is Nothing OrElse String.IsNullOrEmpty(FileUpload1.PostedFile.FileName) OrElse FileUpload1.PostedFile.InputStream Is Nothing Then
            '... Show error message ...
            'MsgBox("No item has been selected to upload.")
            ClientScript.RegisterStartupScript(Me.GetType(), "CkUploadBox", "<script language=""JavaScript"">alert('No item has been selected to upload.');</script>")
            Exit Sub
        End If
 
        'Make sure we are dealing with appropriate file types and sizes
        Dim extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName).ToLower()
        Dim fname As String = System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName)
        Dim MIMEType As String = Nothing
 
        If FileUpload1.PostedFile.ContentLength > 786432 Then
            'MsgBox("The file you are trying to upload is greater than 1mb.  Please reduce the size, and try again.")
            ClientScript.RegisterStartupScript(Me.GetType(), "SizeLimit", "<script language=""JavaScript"">alert('The file you are trying to upload is greater than 786k.  Please reduce the size, and try again.');</script>")
            Exit Sub
        End If
 
        Select Case extension
            Case ".asp", ".bat", ".bmp", ".doc", ".docm", ".docx", ".dot", ".dotx", ".gif", ".htm", ".html", ".ics", ".jpg", ".jpeg", ".pdf", ".png", ".potm", ".potx", ".ppam", ".ppsm", ".ppsx", ".ppt", ".pptm", ".pptx", ".rtf", ".shtml", ".text", ".tif", ".tiff", ".txt", ".url", ".vcs", ".xlam", ".xlsb", ".xlsm", ".xls", ".xlsx", ".xltm", ".xltx", ".zip"
                MIMEType = FileUpload1.PostedFile.ContentType
            Case Else
                'Invalid file type uploaded
                'MsgBox("The file type is invalid.  Upload has failed.")
                ClientScript.RegisterStartupScript(Me.GetType(), "FileType", "<script language=""JavaScript"">alert('The file type is invalid.  Upload has failed.');</script>")
                Exit Sub
        End Select
 
        'Connect to the database and insert a new record into attachments
        Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString)
 
            Const SQL As String = "INSERT INTO [tblAttachments] ([PKeyID], [FileName], [DateUploaded], [MIMEType], [Attachment]) VALUES (@PKeyID, @FileName, @DateUploaded, @MIMEType, @Attachment)"
            Dim myCommand As New SqlCommand(SQL, myConnection)
            myCommand.Parameters.AddWithValue("@PKeyID", qStr)
            myCommand.Parameters.AddWithValue("@FileName", fname)
            myCommand.Parameters.AddWithValue("@DateUploaded", Date.Now.ToString("MM/dd/yyyy"))
            myCommand.Parameters.AddWithValue("@MIMEType", MIMEType)
 
            'Load FileUpload's InputStream into Byte array
            Dim imageBytes(FileUpload1.PostedFile.InputStream.Length) As Byte
            FileUpload1.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length)
            myCommand.Parameters.AddWithValue("@Attachment", imageBytes)
 
            myConnection.Open()
            myCommand.ExecuteNonQuery()
            myConnection.Close()
 
            'MsgBox("Upload was successful.")
        End Using
 
        Response.Redirect("entry.aspx?k=" & qStr & "&zn=" & PZone)
 
    End Sub
-------------------------------------------------------------------------------------------
    'opens attachments
    Protected Sub gvAttachments_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvAttachments.SelectedIndexChanged
        Dim rw As GridViewRow = Me.gvAttachments.SelectedRow
        Dim lbl As Label = rw.FindControl("lblKeyId")
        Dim KeyID As String = ""
        KeyID = lbl.Text
 
        'Connect to the database and bring back the contents & MIME type for the specified picture
        Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString)
 
            Const SQL As String = "SELECT * FROM [tblAttachments] WHERE [KeyID] = @KeyID"
            Dim myCommand As New SqlCommand(SQL, myConnection)
            myCommand.Parameters.AddWithValue("@KeyID", KeyID)
 
            myConnection.Open()
            Dim myReader As SqlDataReader = myCommand.ExecuteReader
 
            If myReader.Read Then
                Response.Clear()
                Response.ClearContent()
                Response.ClearHeaders()
                Response.Buffer = True
                Response.ContentType = myReader("MIMEType").ToString()
                Response.AddHeader("content-disposition", "attachment; filename=" & myReader.Item("FileName"))
                Response.BinaryWrite(myReader("Attachment"))
                'Response.OutputStream.Write(CType(myReader("Attachment"), Byte()), 0, CType(myReader("Attachment"), Byte()).Length - 1)
                Response.Flush()
                Response.Clear()
                Response.End()
            End If
 
            myReader.Close()
            myConnection.Close()
        End Using
 
    End Sub

Open in new window

0
Comment
Question by:RedPhoenix3
  • 6
  • 3
13 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24741569
I am sorry, but has this question got anything to do with MS SQL Server?
0
 

Author Comment

by:RedPhoenix3
ID: 24742151
actually, any sql server...the problem is with office 2007 docs not opening via asp.net regardless which sql server it's in...
0
 

Author Comment

by:RedPhoenix3
ID: 24749745
i'm using visual studio 2008 with sql server 9.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24750978
I am afraid I have no idea.
0
 

Author Comment

by:RedPhoenix3
ID: 24751172
is there anyway to spread this issue throughout EE?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24753750
Click on Request Attention and perhaps a Moderator canadd more appropriate Zones such as:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/ASP/
They can also ask other members to contribute to the thread.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24779387
One solution would be to:

1. Uninstall Micosoft Office 2007
2. and reinstall again.
3. It's will work.

Check whether this helps you out or not:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_22905745.html
http://www.experts-exchange.com/Software/System_Utilities/Compression_Tools/Q_23253582.html
0
 

Author Comment

by:RedPhoenix3
ID: 24782061
i cannot uninstall/re-install software...this is on a company system...we're not allowed (locked down).  i found a solution, and will post it tomorrow.  something about 2007 it adds extra bytes, so getting it out of sql, i had to loop through things...and remove the extra byte(s).
0
 

Accepted Solution

by:
RedPhoenix3 earned 0 total points
ID: 24787048
I noticed upon retrieving the documents, there was one extra byte added to the original filesize.  I now loop and remove the last byte.  It opens all of my documents now regardless of mimetypes:

            If myReader.Read Then
                Response.Clear()
                Response.ClearContent()
                Response.ClearHeaders()
                Response.Buffer = True
                Response.ContentType = myReader("MIMEType").ToString()
                Response.AddHeader("content-disposition", "attachment; filename=" & myReader("FileName").ToString())
                Dim b(myReader("Attachment").Length - 2) As Byte
                'Just copying over all but the last Byte
                For i As Integer = 0 To b.Length - 1
                    b(i) = myReader("Attachment")(i)
                Next i
                Response.OutputStream.Write(b, 0, b.Length)
                Response.Flush()
                Response.Clear()
                Response.End()
            End If
0
 

Author Comment

by:RedPhoenix3
ID: 24828315
I would like to add an additional comment.  After some testing, I found out looping through binary object to be opened will time out the web server/application if an object is larger than 100k.  It's best to remove the extra byte while uploading the object instead.

Change this line:
Dim imageBytes(FileUpload1.PostedFile.InputStream.Length) As Byte

to:
Dim imageBytes(FileUpload1.PostedFile.InputStream.Length - 1) As Byte

Than just remove the loop and write the binary data:
If myReader.Read Then
                Response.Clear()
                Response.ClearContent()
                Response.ClearHeaders()
                Response.Buffer = True
                Response.ContentType = myReader("MIMEType").ToString()
                Response.AddHeader("content-disposition", "attachment; filename=" & myReader("FileName").ToString())
                Response.Binary.Write(myReader("Attachment")
                Response.Flush()
                Response.Clear()
                Response.End()
End If

It is a huge time saver on the servers and still opens Office 2007 documents.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

829 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