Problems opening saved files (.docx) in SQL using VB,net

[Viravalo]

Hello everyone, I've been having problems when retrieving a .docx file from sql server, this is saved in binary. When I want to retrieve the document, it always bring back the same file. I'm using a ListView to show all the content saved in my database.

Dim strConnString = "Data Source=Vic\Vic;Initial Catalog=DB_Archivo;Integrated Security=True"
Dim sqlSelect As String = "SELECT IDd, bytes1, types FROM luResourceFiles1"
Dim sqlInsert As String = "INSERT luResourceFiles1 VALUES(@IDd, @Bytes1, @Types)"
Dim sqlDelete As String = "DELETE luResourceFiles1 WHERE ID = @IDd"
Dim sqlUpdate As String = "UPDATE luResourceFiles1 SET Bytes=@Bytes, Types=@Types WHERE ID=@ID"

Dim conn As SqlConnection
Dim cmd1 As SqlCommand
Dim da As SqlDataAdapter
Dim ds As DataSet
Dim itemcoll(100) As String


Private Sub SaveBtn_Click(sender As Object, e As EventArgs) Handles SaveBtn.Click
Try
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 = "Documento de Word|*.docx"
' .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("@IDd", sName)
cmd.Parameters.AddWithValue("@Bytes1", 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
Catch ex As Exception
MessageBox.Show($"Error: {ex.ToString}", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub

Private Sub OpenBtn_Click(sender As Object, e As EventArgs) Handles OpenBtn.Click
Try

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 File.Exists(fileName) = True Then
File.Delete(fileName)
End If
Dim conn As New SqlConnection(strConnString)
Dim cmd As New SqlCommand(sqlSelect, conn)
' Dim cmd As New SqlCommand("SELECT * FROM luResourceFiles1 where ID = " & sName, conn) ""
cmd.Parameters.AddWithValue("@IDd", sqlSelect)
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
MsgBox(fileName)
'Process.Start(fileName)
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Question, "Unknown Resource Type")
End Try
Catch ex As Exception
MessageBox.Show($"Error: {ex.ToString}", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try


End Sub

Private Sub Form5_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Try
Me.lvwResource.View = View.Details
Me.lvwResource.GridLines = True
Dim conn As New SqlConnection(strConnString)
Dim strQ As String = String.Empty
strQ = "SELECT IDd, Types from luResourceFiles1"

cmd1 = New SqlCommand(strQ, conn)
da = New SqlDataAdapter(cmd1)
ds = New DataSet
da.Fill(ds, "Table")

Dim i As Integer = 0
Dim j As Integer = 0

' adding the columns in ListView
For i = 0 To ds.Tables(0).Columns.Count - 1
Me.lvwResource.Columns.Add(ds.Tables(0).Columns(i).ColumnName.ToString())
Next
'Now adding the Items in Listview
For i = 0 To ds.Tables(0).Rows.Count - 1
For j = 0 To ds.Tables(0).Columns.Count - 1
itemcoll(j) = ds.Tables(0).Rows(i)(j).ToString()
Next
Dim lvi As New ListViewItem(itemcoll)
lvwResource.Items.Add(lvi)
Me.lvwResource.AutoResizeColumns(ColumnHeaderAutoResizeStyle.ColumnContent)
Next
Catch ex As Exception
MessageBox.Show($"Error: {ex.ToString}", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub

Table name: luresourcefiles1

id_resource (int)

IDd (nvarchar50)

Bytes1 (varbinaryMAX)

Types (nvarchar50)

Your help will be appreciated!

Continue reading...