How to retrieve open xml documents from SQL

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

RedPhoenix3Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
I am sorry, but has this question got anything to do with MS SQL Server?
0
RedPhoenix3Author Commented:
actually, any sql server...the problem is with office 2007 docs not opening via asp.net regardless which sql server it's in...
0
RedPhoenix3Author Commented:
i'm using visual studio 2008 with sql server 9.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Anthony PerkinsCommented:
I am afraid I have no idea.
0
RedPhoenix3Author Commented:
is there anyway to spread this issue throughout EE?
0
Anthony PerkinsCommented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
RedPhoenix3Author Commented:
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
RedPhoenix3Author Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RedPhoenix3Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.