Solved

How to retrieve open xml documents from SQL

Posted on 2009-06-29
13
750 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

707 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

13 Experts available now in Live!

Get 1:1 Help Now