Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to retrieve open xml documents from SQL

Posted on 2009-06-29
13
Medium Priority
?
807 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Viewers will learn how the fundamental information of how to create a table.

609 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