i'm trying to get the image from SQL to picturebox using Listbox in in VB.

here is my sql sample table.

and here is my VB Design form, so what I'm trying to do is when I load the form, if an item in the listbox is selected, i want it to get the image(Binary Data) from sql to picturebox in vb net.

and the code I'm working with it give me this error:

VB full code:

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    connect()
    Dim co As New SqlConnection("Data Source=WXCQSDQSD\SQLEXPRESS;Initial Catalog=food;Integrated Security = True")
    co.Open()
    Dim com As New SqlCommand("SELECT Image from Items where ItemName = '" & ListBox1.SelectedIndex & "'", co)
    Dim img As Byte() = DirectCast(com.ExecuteScalar(), Byte())
    Dim ms As MemoryStream = New MemoryStream(img)

    Dim dt = GetDataFromSql()
    Dim BndSrc As New BindingSource()
    BndSrc.DataSource = dt
    ListBox1.DisplayMember = "ItemName"
    ListBox1.DataSource = BndSrc
    TextBox1.DataBindings.Add("Text", BndSrc, "ItemID")
    TextBox2.DataBindings.Add("Text", BndSrc, "ItemName")
    TextBox3.DataBindings.Add("Text", BndSrc, "Details")
    PictureBox1.Image = Image.FromStream(ms)

End Sub
Private Function GetDataFromSql() As DataTable
    Dim dt As New DataTable
    Using connection As New SqlConnection("Data Source=WXCQSDQSD\SQLEXPRESS;Initial Catalog=food;Integrated Security = True"),
            cmd As New SqlCommand("select * FROM Items", connection)
        connection.Open()
        Using reader = cmd.ExecuteReader
            dt.Load(reader)
        End Using
    End Using
    Return dt
End Function

CodePudding user response：

Imports System.Data.SqlClient Imports System.IO Public Class Form1

Dim con As SqlConnection
Dim cmd As SqlCommand
Dim rdr As SqlDataReader
Dim da As SqlDataAdapter
Private Const cs As String = "ConnectionString"

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    con = New SqlConnection(cs)
    con.Open()
    cmd = New SqlCommand("select * from [dbo].[Item_Details]", con)
    rdr = cmd.ExecuteReader()
    While rdr.Read
        ListBox1.Items.Add(rdr(1))
    End While
    con.Close()
End Sub

Private Sub ListBox1_MouseClick(sender As Object, e As MouseEventArgs) Handles ListBox1.MouseClick
    Try

        con = New SqlConnection(cs)
        con.Open()
        da = New SqlDataAdapter("Select *  from Item_Details where itemname='" & ListBox1.SelectedItem.ToString() & "'", con)
        Dim dt As New DataTable
        da.Fill(dt)
        For Each row As DataRow In dt.Rows
            TextBox1.Text = row(0).ToString()
            TextBox2.Text = row(1).ToString()
            TextBox3.Text = row(2).ToString()
            Dim data As Byte() = row(3)
            Dim ms As New MemoryStream(data)
            PictureBox1.Image = Image.FromStream(ms)
        Next

    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

End Class

CodePudding user response：

Remove the picture retrieval from the Form.Load. We have selected all the data in the GetDataFromSql function. The magic happens in the ListBox1.SelectedIndexChanged method.

The SelectedIndexChanged will occur as a result of the code in the Form.Load and every time the selection changes. When we bind the the list box to to binding source the entire row is added as a DataRowView. We can use this to get the data in the Image column. First check if the field is null. Next cast to a byte array. Take the byte array and pass it to the constructor of a MemoryStream. Streams need to be disposed so it is in a Using block. Finally the stream can be assigned to the Image property of the PictureBox.

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim dt = GetDataFromSql()
    Dim BndSrc As New BindingSource()
    BndSrc.DataSource = dt
    ListBox1.DisplayMember = "ItemName"
    ListBox1.DataSource = BndSrc
    TextBox1.DataBindings.Add("Text", BndSrc, "ItemID")
    TextBox2.DataBindings.Add("Text", BndSrc, "ItemName")
    TextBox3.DataBindings.Add("Text", BndSrc, "Details")
End Sub

Private Function GetDataFromSql() As DataTable
    Dim dt As New DataTable
    Using connection As New SqlConnection("Data Source=WXCQSDQSD\SQLEXPRESS;Initial Catalog=food;Integrated Security = True"),
        cmd As New SqlCommand("select * FROM Items", connection)
        connection.Open()
        Using reader = cmd.ExecuteReader
            dt.Load(reader)
        End Using
    End Using
    Return dt
End Function

Private Sub ListBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ListBox1.SelectedIndexChanged
    Dim row = DirectCast(ListBox1.SelectedItem, DataRowView)
    If row("Image") IsNot Nothing Then
        Dim b() As Byte = DirectCast(row("Image"), Byte()) '< ------ Cast the field to a Byte array.
        Using ms As New System.IO.MemoryStream(b)
            PictureBox1.Image = System.Drawing.Image.FromStream(ms)
        End Using
    Else
        PictureBox1.Image = Nothing
    End If
End Sub