How to save multiple blob image from SQL to file (once again) in VB net?

See more:

VB

SQL

Hello everybody,

I have a second itteration of my code, and still have a problem - it's only one file saved and stoped.

How i need to change my code for saving all photos from fltered datagrid?

Thanks a lot for any advise and help.

What I have tried:

This code is loaded data from sql to DataGridView:

OpenConnection() ' open our connection before

    Dim da As New SqlDataAdapter
    Dim dt As DataTable ' declaration data table
    Dim bs As New BindingSource()

    da = New SqlDataAdapter("Select emp.SSNO As 'Number', emp.LASTNAME As 'LASTNAME', emp.FIRSTNAME As 'FIRSTNAME', emp.MIDNAME As 'MIDNAME', MMOBJS.LNL_BLOB As 'Photo' FROM EMP INNER JOIN BADGE ON EMP.ID = BADGE.EMPID INNER JOIN UDFEMP ON UDFEMP.ID = EMP.ID INNER JOIN DEPT on UDFEMP.DEPT = DEPT.ID INNER JOIN BADGSTAT ON BADGE.STATUS = BADGSTAT.ID INNER JOIN MMOBJS ON MMOBJS.EMPID = BADGE.EMPID", connections)
    dt = New DataTable

    da.Fill(dt)
    bs = New BindingSource()
    bs.DataSource = dt
    DataGridView1.DataSource = bs
    BindingNavigator1.BindingSource = bs

    Dim band As DataGridViewBand = DataGridView1.Columns(4)
    band.Visible = False
    connections.Close() ' close connections

End Sub

Then, by click on "Save" button, i want to save to local folder all users photo from filtered DataGrid, with original filename for each photo. But my code saved only one photo from grid (seected row) and thats all (((

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim Path As String = IO.Path.Combine(Application.StartupPath, "Images",
                                         " " + DataGridView1.CurrentRow.Cells(0).Value.ToString +
                                         " " + DataGridView1.CurrentRow.Cells(1).Value.ToString +
                                         " " + DataGridView1.CurrentRow.Cells(2).Value.ToString +
                                         " " + DataGridView1.CurrentRow.Cells(3).Value.ToString +
                                         "" + ".jpg")
    Dim Dir As String = System.IO.Path.GetDirectoryName(Path)


    Try
        If Not System.IO.Directory.Exists(Dir) Then
            System.IO.Directory.CreateDirectory(Dir)
        End If

    Catch ex As Exception
        MessageBox.Show("Error: Saving Image Failed ->>" & ex.Message.ToString())
    End Try

    SqlBlob2File(Path)
    
End Sub


Private Sub SqlBlob2File(ByVal DestFilePath As String)

    OpenConnection() ' open our connection before
    Dim PictureCol As Integer = 4 ' the column # of the BLOB field

    'Dim cn As New SqlConnection("server=localhost;integrated security=yes;database=NorthWind")
    Dim cmd As New SqlCommand("Select emp.SSNO As 'Number', emp.LASTNAME As 'LASTNAME', emp.FIRSTNAME As 'FIRSTNAME', emp.MIDNAME As 'MIDNAME', MMOBJS.LNL_BLOB As 'Photo' FROM EMP INNER JOIN BADGE ON EMP.ID = BADGE.EMPID INNER JOIN UDFEMP ON UDFEMP.ID = EMP.ID INNER JOIN DEPT on UDFEMP.DEPT = DEPT.ID INNER JOIN BADGSTAT ON BADGE.STATUS = BADGSTAT.ID INNER JOIN MMOBJS ON MMOBJS.EMPID = BADGE.EMPID where EMP.SSNO = @SSNO", connections)
    cmd.Parameters.AddWithValue("@SSNO", DataGridView1.CurrentRow.Cells(0).Value)

    Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
    'dr.Read()
    If dr.HasRows Then

        While dr.Read

            Dim b(dr.GetBytes(PictureCol, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte
            dr.GetBytes(PictureCol, 0, b, 0, b.Length)


            Dim fs As New System.IO.FileStream(DestFilePath, IO.FileMode.OpenOrCreate, IO.FileAccess.Write)
            fs.Write(b, 0, b.Length)
            fs.Close()
        End While
    End If
    dr.Close()
    connections.Close() ' close connections

End Sub