ItemName to Listbox from SQLTable and its value to textboxes-CodePudding

I want to load the ItemName from sql table to Listbox and get ItemId, ItemName and Details values to textboxes.

so far I have this code everytime I open or load the window its shows the itemName in the listbox. which is good.

Dim cmd As New SqlCommand("select * FROM Items")
cmd.Connection = connection
Dim adp As New SqlDataAdapter(cmd)
Dim dt As New DataTable()
adp.Fill(dt)
ListBox1.DataSource = dt
ListBox1.DisplayMember = "ItemName"

but the problems is: what do I have to do, if I click one of the items in the listbox and it will automatically show the itemId, ItemName and Details in the textboxes from sql table. I need some help. thank you!

CodePudding user response：

To sync the selection of a ListBox item with TextBoxes use a BindingSource. When you add DataBindings to the TextBox you pass the name of property you wish to bind to, the BindingSource variable and the DataMember (the column name) you wish to bind.

Most database objects need to be disposed. Connection and Command have Dispose methods that need to be called. Luckily vb.net provides Using...End Using blocks to handle this for us. This will also closed the connection. Connections should be declared with the Using block in the method where they are used so they can be disposed.

You don't need a DataAdapter. Just use a DataReader and load the DataTable.

You can pass the connection string directly to the constructor of the connection. You can also pass the CommandText and the Connection to the constructor of the Command.

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("Your connection string"),
            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