M
merlinturner
Guest
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.
Continue reading...
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.
Continue reading...