VB.NET synchronized Combobox and label from SQL Server

Question

I'm trying to synchronize the reading of a SQL Server table with 2 columns (nom_unité and cout_unité).

The first column (nom_unité) will be populated into a combobox and I want the second column (cout_unité) to be synchronized into a label with the first combobox (meaning, when I change the combobox value, the label should change too refering to the table).

I can do this with 2 comboboxes :

    Dim connection As New SqlConnection("Data Source=xxx")

    Dim dt As New DataTable
    Dim sqlquery As String

    connection.Open()

    sqlquery = "select * from liste_unités"

    Dim SQL As New SqlDataAdapter(sqlquery, connection)
    SQL.Fill(dt)

    Dim cmd As New SqlCommand(sqlquery, connection)

    ComboBoxC1L1.DataSource = dt
    ComboBoxC1L1.DisplayMember = "nom_unité"

    ComboBox1.DataSource = dt
    ComboBox1.DisplayMember = "cout_unité"

but I do not know how to do it with a label (instead of ComboBox1).

I believe I can achieve it with something like that :

Dim sqlcmd As New SqlCommand("select * from liste_unités", connection)
Dim myreader As SqlDataReader
        
myreader = sqlcmd.ExecuteReader()
myreader.Read()

If myreader.HasRows Then
   Label1.Text = myreader.Item("cout_unité").ToString
End If

but this is only reading the first row and not changing the label value when changing the first combobox selected value.

How to do it the easiest and most efficient way ?

Thank you :)

Answer 1

As you have assigned the datasource of the combobox to a datatable which contains the information you need, you can get that information when the value of the combobox changes.

I started a new Windows Forms project and put a combobox (named "cbNomUnité") and a label (named "lblCoutUnité") on Form1 and used this code:

Imports System.Data.SqlClient

Public Class Form1

    Dim connStr As String = "Server=.\SQLEXPRESS;Database=Testing;Trusted_Connection=true;"

    Sub PopulateCB()
        Dim sql = "SELECT nom_unité, cout_unité FROM liste_unités"
        Dim dt As New DataTable

        Using conn As New SqlConnection(connStr),
                da As New SqlDataAdapter(sql, conn)
            da.Fill(dt)
        End Using

        cbNomUnité.DataSource = dt
        cbNomUnité.DisplayMember = "nom_unité"

    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cbNomUnité.SelectedIndexChanged
        Dim cb = DirectCast(sender, ComboBox)

        If cb.SelectedIndex >= 0 Then
            Dim val = DirectCast(cb.SelectedItem, DataRowView).Row.Field(Of String)("cout_unité")
            lblCoutUnité.Text = val

        End If

    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        PopulateCB()

    End Sub

End Class

To get a program that does this:

_{(Refresh the page to see the animation again.)}