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

    Question

  • 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!

    Monday, February 11, 2019 9:15 PM

All replies

  • Hello Viravalo,

    "When I want to retrieve the document, it always bring back the same file"

    Could you please clarify what you mean? 


    Regards,

    Trevor White

    Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

    Monday, February 11, 2019 9:25 PM
  • If I'm not mistaken you are attempting to get the image by a string name? You need a integer/int primary key. Setup a primary key is the first step and assign it to the row for displaying data e.g in a ListViewItem.Tag

    See my MSDN code sample which shows inserts and reads of images in SQL-Server.

    https://code.msdn.microsoft.com/INSERT-Image-into-SQL-29dfc8ee?redir=0


    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 10:48 PM
    Moderator
  • Note a side note, this will come back and bite you now or later. Just create them as needed (see my code sample).

    Dim conn As SqlConnection
    Dim cmd1 As SqlCommand
    Dim da As SqlDataAdapter
    Dim ds As DataSet


    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 10:51 PM
    Moderator
  • I meant that whenever I want to open a .docx file from the database, it always open the first archive that was inserted, if i insert 5 documents, all of them will open the same file, the one first inserted.
    • Edited by Viravalo Tuesday, February 12, 2019 1:59 AM
    Tuesday, February 12, 2019 1:47 AM
  • I did add an ID key with an incremental property and change the code but still the same.
    Tuesday, February 12, 2019 3:20 AM
  • Maybe you should use a different value instead of sqlSelect in ‘cmd.Parameters.AddWithValue("@IDd", sqlSelect)’?

    Tuesday, February 12, 2019 7:16 AM
  • See also my response here

    https://social.msdn.microsoft.com/Forums/en-US/f673ad5a-a110-4fa7-807f-3fdaf3acb49e/issue-when-trying-to-retrieve-binary-word-document-from-sql-server-using-vbnet?forum=vbgeneral


    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

    Tuesday, February 12, 2019 1:20 PM
    Moderator