Imports System.Data.OleDb

Public Class Form5
    Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\mohjn\Desktop\test1\1.accdb")
    Dim prices(13) As Integer ' Declare an array to store prices
    Dim comboBoxList As New List(Of ComboBox) From {PRODUCT1, PRODUCT2, PRODUCT3, PRODUCT4, PRODUCT5, PRODUCT6, PRODUCT7, PRODUCT8, PRODUCT9, PRODUCT10, PRODUCT11, PRODUCT12, PRODUCT13, PRODUCT14}

    Private Sub Form5_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' Create a list to store ComboBoxes

        Try
            conn.Open()
            Dim query As String = "SELECT productname, price FROM Table1"
            Dim cmd As New OleDbCommand(query, conn)
            Dim reader As OleDbDataReader = cmd.ExecuteReader()

            ' Loop through the ComboBox list and populate them with product names
            Dim index As Integer = 0 ' Initialize index for the price array
            For Each comboBox As ComboBox In comboBoxList
                While reader.Read()
                    comboBox.Items.Add(reader("productname").ToString())
                    prices(index) = Integer.Parse(reader("price").ToString()) ' Store price in the array
                    index += 1 ' Increment the index for the next ComboBox
                End While

                ' Add event handler for SelectedIndexChanged
                AddHandler comboBox.SelectedIndexChanged, AddressOf ComboBox_SelectedIndexChanged
                ' Move the reader back to the start
                reader.Close()
                reader = cmd.ExecuteReader()
            Next
        Catch ex As Exception
            MessageBox.Show("Error: " & ex.Message)
        Finally
            conn.Close()
        End Try
    End Sub

    ' Event handler for ComboBox SelectedIndexChanged
    Private Sub ComboBox_SelectedIndexChanged(sender As Object, e As EventArgs)
        Dim selectedComboBox As ComboBox = DirectCast(sender, ComboBox)
        Dim selectedProductName As String = selectedComboBox.SelectedItem.ToString()
        Dim selectedIndex As Integer = comboBoxList.IndexOf(selectedComboBox) ' Get the index of the selected ComboBox

        If selectedIndex >= 0 AndAlso selectedIndex < prices.Length Then
            MessageBox.Show("PRICE of " & selectedProductName & ": " & prices(selectedIndex).ToString())
        Else
            MessageBox.Show("PRICE not found for " & selectedProductName)
        End If
    End Sub
End Class