none
Retrieving .docx stored in SQL Server results in “there was an error opening the file”

    Question

  • I am working on a legacy Windows desktop application written in VB.net 4.0 with a SQL Server 2005 database. The application generates reports based on Word templates which are stored in the database. There is an admin section that allows new Word report templates (.docx) to be loaded into the database in case there are some changes to the text in the report. The admin form allows new files to be added, or existing ones deleted or updated. When adding a new file it asks for a code (text) which is used as the primary key for the file in the database lookup table it is stored in. It also allows a file to be selected and opened from the database so it can be viewed.

    The following behavior can be consistently reproduced: A new .docx file can be added with a new code (primary key). That file can be successfully opened. If any existing file is updated with a new .docx, any attempt to open will fail with the error message "there was an error opening the file" in Word. If that file's record is deleted, and the same file is added with a new code (PK) then it can open successfully. If the same file (or any .docx file) is added using the same code (PK) as the deleted file, it will appear to write to DB ok, but any attempt to open the file will fail with the same error message as above.

    The .docx files that cannot be opened are one byte longer than when they can be successfully opened. The .docx files that cannot be opened by Word can still be open as an archive (with 7zip for example) and the file contents appear to be the same.

    Overwriting an existing file, or reusing a deleted file's code (PK) with any other file type (including .doc) will work fine. Only .docx files are causing problems; which is a shame as .docx files are required because the report generation is achieved by manipulating the Word document contents with OpenXML code.

    Here's the code that inserts the file to the database:

    Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles     btnInsert.Click
        Dim sName As String = InputBox("What is the name of this Resource")
        If (sName.Length > 0) Then
            Dim ofd As New OpenFileDialog
            With ofd
                .CheckFileExists = True
                .ShowReadOnly = False
                .Filter = "All Files|*.*"
                If (.ShowDialog = DialogResult.OK) Then
                    Dim fs As FileStream = New FileStream(.FileName, FileMode.Open, FileAccess.Read)
                    Dim docByte As Byte() = New Byte(fs.Length - 1) {}
                    fs.Read(docByte, 0, Convert.ToInt32(fs.Length))
                    fs.Close()
                    Dim FileType As String = Path.GetExtension(.FileName).ToLower
                    Dim conn As New SqlConnection(strConnString)
                    Dim cmd As New SqlCommand(sqlInsert, conn)
                    cmd.Parameters.AddWithValue("@ID", sName)
                    cmd.Parameters.AddWithValue("@Bytes", docByte)
                    cmd.Parameters.AddWithValue("@Types", FileType)
                    conn.Open()
                    cmd.ExecuteNonQuery()
                    conn.Close()
                    MsgBox("Saved to DB")
                    Dim li As ListViewItem = lvwResource.Items.Add(sName)
                    li.SubItems.Add(FileType)
                    docByte = Nothing
                    fs.Dispose()
                End If
            End With
        End If
    End Sub

    And the code that retrieves:

    Private Sub btnOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpen.Click
        Dim sName As String = lvwResource.SelectedItems(0).Text
        Dim sType As String = lvwResource.SelectedItems(0).SubItems(1).Text
        Dim fileName As String = String.Format("{0}{1}{2}", Path.GetTempPath, sName, sType)
        Dim conn As New SqlConnection(strConnString)
        Dim cmd As New SqlCommand(sqlSelect, conn)
        cmd.Parameters.AddWithValue("@ID", sName)
        Dim da As New SqlDataAdapter(cmd)
        Dim dt As New DataTable
        da.Fill(dt)
        Dim docByte() As Byte = dt.Rows(0)(1)
        Dim fs As New FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write)
        fs.Write(docByte, 0, Convert.ToInt32(docByte.Length))
        fs.Dispose()
        Try
            System.Diagnostics.Process.Start(fileName)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Question, "Unknown Resource Type")
        End Try
    End Sub

    Some further details: Workstations are Windows XP Sp3 with Microsoft Office 2003 SP3, server is Server 2003 Standard edition SP2, db is SQL Server 2005 SP2. It is a large organisation and I have no influence over their outdated software.


    Monday, November 18, 2013 12:26 AM

Answers

  • Hi,

    I've tested your latest code, indeed, there will be an error message "there was an error opening the file" in Word:

    After troubleshooting, I found that there were some mistakes in your update operation, here is my fixed code:

    Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
            Dim ofd As New OpenFileDialog
            Dim sName As String = lvwResource.SelectedItems(0).Text
            With ofd
                .CheckFileExists = True
                .ShowReadOnly = False
                .Filter = "All Files|*.*"
                If .ShowDialog = DialogResult.OK Then
                    Dim fs As FileStream = New FileStream(.FileName, FileMode.Open, FileAccess.Read)
                    Dim img As Byte() = New Byte(fs.Length - 1) {}
                    fs.Read(img, 0, Convert.ToInt32(fs.Length))
                    fs.Close()
    
                    Dim FileType As Object = Path.GetExtension(.FileName).ToLower
                    Dim conn As New SqlConnection(strConnString)
                    Dim cmd As New SqlCommand(sqlUpdate, conn)
                    cmd.Parameters.AddWithValue("@ID", sName)
                    cmd.Parameters.AddWithValue("@Bytes", img)
                    cmd.Parameters.AddWithValue("@Types", FileType)
                    cmd.Parameters.AddWithValue("@Length", img.Length)
                    conn.Open()
                    cmd.ExecuteNonQuery()
                    conn.Close()
                    lvwResource.SelectedItems(0).SubItems(1).Text = FileType 'update Type column
                    MsgBox("Saved to DB")
                    img = Nothing
                    fs.Dispose()
                End If
            End With
    
        End Sub

    And an important step is to clear your temp file when you click open button:

    Private Sub btnOpen_Click(sender As Object, e As EventArgs) Handles btnOpen.Click
            Dim sName As String = lvwResource.SelectedItems(0).Text
            Dim sType As String = lvwResource.SelectedItems(0).SubItems(1).Text
            Dim fileName As String = String.Format("{0}{1}{2}", Path.GetTempPath, sName, sType)
    
            If System.IO.File.Exists(fileName) = True Then
                System.IO.File.Delete(fileName)
            End If
    ....
    End Sub

    Now, it works fine:)


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by merlinturner Thursday, November 21, 2013 1:23 AM
    Wednesday, November 20, 2013 7:47 AM
    Moderator

All replies

  • Hi,

    I've tested your code, it works fine in my side: Windows 8 x64, SQL Server 2012 & Office 2013

    I noticed that your Office Version is 2003 SP3 and you said you can open a .docx file successfully in certain cases.

    As we know, if we want to open an OpenXML file using Office 2003, the only way is to install the Compatibility Pack: http://www.microsoft.com/en-us/download/details.aspx?id=3

    >>If any existing file is updated with a new .docx, any attempt to open will fail with the error message "there was an error opening the file" in Word.

    For this error message, we can refer to this reference:

    #You receive an error when trying to open a .docx file in Word 2003 or with the Word Viewer
    http://support.microsoft.com/kb/2001377

    Please check that when you updated an existing file, the content can be updated correctly(No data loss).

    Certainly, using Office2007 or later version is the recommended way to open .docx file.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, November 18, 2013 8:47 AM
    Moderator
  • XML documents for Office apps have a checksum validation so if the file size changes without updating the document through the host application then it will be considered corrupt. If the extra byte of data is being added during update to the database, then I would store the document file size in another column to ensure that the correct number of bytes can be read for the document when retrieving it from the database.

    There may be other ways to work around this issue but I can't really tell from your description whether the extra byte is being added when the document is read from the file or written to the database.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, November 18, 2013 2:37 PM
  • Thank you for your response.

    I ran the code on my home machine, which has the same configuration as yours (Windows 8 x64, SQL Server 2012 & Office 2013) and am still getting the same strange behaviour!

    Did you try over-writing records?  It works fine with each new record, but only starts acting strange when existing records are overwritten. 

    Actually, I guess I had better provide the overwrite code!

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
            Dim ofd As New OpenFileDialog
            Dim sName As String = lvwResource.SelectedItems(0).Text
            With ofd
                .CheckFileExists = True
                .ShowReadOnly = False
                .Filter = "All Files|*.*"
                If .ShowDialog = DialogResult.OK Then
                    Dim fs As FileStream = New FileStream(.FileName, FileMode.Open)
                    Dim img As Byte() = New Byte(fs.Length) {}
                    fs.Read(img, 0, fs.Length)
                    fs.Close()
                    Dim FileType As Object = Path.GetExtension(.FileName).ToLower
                    Dim conn As New SqlConnection(strConnString)
                    Dim cmd As New SqlCommand(sqlUpdate, conn)
                    cmd.Parameters.AddWithValue("@ID", sName)
                    cmd.Parameters.AddWithValue("@Bytes", img)
                    cmd.Parameters.AddWithValue("@Types", FileType)
                    cmd.Parameters.AddWithValue("@Length", img.Length)
                    conn.Open()
                    cmd.ExecuteNonQuery()
                    conn.Close()
                    lvwResource.SelectedItems(0).SubItems(1).Text = FileType 'update Type column
                    MsgBox("Saved to DB")
                    img = Nothing
                End If
            End With
        End Sub

    Sorry about that, I tried to keep the initial post as brief as possible.

    Also, here is the SQL string variables:

        Dim sqlSelect As String = "SELECT * FROM luResourceFiles"
        Dim sqlInsert As String = "INSERT luResourceFiles VALUES(@ID, @Bytes, @Types, @Length)"
        Dim sqlDelete As String = "DELETE luResourceFiles WHERE ID = @ID"
        Dim sqlUpdate As String = "UPDATE luResourceFiles SET Bytes=@Bytes, Types=@Types, Length=@Length WHERE ID=@ID"

    I added the Length variable after Paul Clement's comment below (it didn't help).

    The database table has the following attributes:

    ID: varchar(50)

    Bytes: varbinary(max)

    Types: varchar(50)

    Length: (int)

    It is only after uploading a couple of documents and overwriting them that weird things start to happen. After a while it started opening the wrong documents, which didn't make sense at all!

    With regard to your suggestion and link for KB 2001377, that did not help.  I can open .docx files, its' only after overwriting that the error occurs.

    Many thanks

    Wednesday, November 20, 2013 5:19 AM
  • Hi,

    I've tested your latest code, indeed, there will be an error message "there was an error opening the file" in Word:

    After troubleshooting, I found that there were some mistakes in your update operation, here is my fixed code:

    Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
            Dim ofd As New OpenFileDialog
            Dim sName As String = lvwResource.SelectedItems(0).Text
            With ofd
                .CheckFileExists = True
                .ShowReadOnly = False
                .Filter = "All Files|*.*"
                If .ShowDialog = DialogResult.OK Then
                    Dim fs As FileStream = New FileStream(.FileName, FileMode.Open, FileAccess.Read)
                    Dim img As Byte() = New Byte(fs.Length - 1) {}
                    fs.Read(img, 0, Convert.ToInt32(fs.Length))
                    fs.Close()
    
                    Dim FileType As Object = Path.GetExtension(.FileName).ToLower
                    Dim conn As New SqlConnection(strConnString)
                    Dim cmd As New SqlCommand(sqlUpdate, conn)
                    cmd.Parameters.AddWithValue("@ID", sName)
                    cmd.Parameters.AddWithValue("@Bytes", img)
                    cmd.Parameters.AddWithValue("@Types", FileType)
                    cmd.Parameters.AddWithValue("@Length", img.Length)
                    conn.Open()
                    cmd.ExecuteNonQuery()
                    conn.Close()
                    lvwResource.SelectedItems(0).SubItems(1).Text = FileType 'update Type column
                    MsgBox("Saved to DB")
                    img = Nothing
                    fs.Dispose()
                End If
            End With
    
        End Sub

    And an important step is to clear your temp file when you click open button:

    Private Sub btnOpen_Click(sender As Object, e As EventArgs) Handles btnOpen.Click
            Dim sName As String = lvwResource.SelectedItems(0).Text
            Dim sType As String = lvwResource.SelectedItems(0).SubItems(1).Text
            Dim fileName As String = String.Format("{0}{1}{2}", Path.GetTempPath, sName, sType)
    
            If System.IO.File.Exists(fileName) = True Then
                System.IO.File.Delete(fileName)
            End If
    ....
    End Sub

    Now, it works fine:)


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by merlinturner Thursday, November 21, 2013 1:23 AM
    Wednesday, November 20, 2013 7:47 AM
    Moderator
  • Thank you so much for you time and patience!

    It appears to be working perfectly for me now.

    Thursday, November 21, 2013 1:28 AM
  • Hello Merlin, I was checking out your code and tried to run it, but it has the problem of once I open the file, it just opens the first document stored in sql. It always opens the same file... Do you have this problem?
    Monday, February 11, 2019 7:07 AM
  • Hello Merlin, I was checking out your code and tried to run it, but it has the problem of once I open the file, it just opens the first document stored in sql. It always opens the same file... Do you have this problem?

    If interested in a C# solution with full source code (done in VS2015 which is fully compatible with VS2017) that if needed can easily be understood with VB.NET then start a new question and I will provide the code.

    Why start a new question? Because this can assist others looking for the same thing while this current thread more likely than not whatever I provide will not be seen by others, only the resolution for the initial question.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, February 11, 2019 12:10 PM
    Moderator