Solved

How to retrieve open xml documents from SQL

Posted on 2009-06-29
13
757 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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

932 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now