Why is my combobox not displaying data after being chosen

Question

I have 2 combo boxes that are linked together, In the parent combo box, RoomT Type after a value is selected it does not allow me to reselect to a different value. Also if anyone would be so kind and tell me how i could use the primary key of the selected value from the combo box in SQL queries.

Here is what I got :

 Private Sub FrmBookings_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
    Using con1 As New SQLiteConnection(ConStr)
        Using com As New SQLiteCommand("Select CustomerID, fname FROM customers Left JOIN BOOKING ON booking.BCustomerID = customers.customerID where booking.BookingID is null", con1)
            con1.Open()

            Dim dt As New DataTable()

            dt.Load(com.ExecuteReader)
            cmbCustomerData.DataSource = dt
            cmbCustomerData.DisplayMember = "fname"
            cmbCustomerData.ValueMember = "CustomerID"

        End Using
    End Using



    Using con1 As New SQLiteConnection(ConStr)
        Using da As New SQLiteDataAdapter("Select RoomTypeName, RoomTypeID FROM RoomType", con1)
            Dim dt As New DataTable()
            da.Fill(dt)
            cmbRoomType.Items.Clear()
            For Each row As DataRow In dt.Rows
                cmbRoomType.Items.Add(row("roomTypeName").ToString)

            Next
        End Using
    End Using




End Sub

   Private Sub CmbRoomType_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbRoomType.SelectedIndexChanged



    'clear dt

    cmbRoomType.Items.Clear()

    Using con1 As New SQLiteConnection(ConStr)
        Using da As New SQLiteDataAdapter("Select RoomNumber, RoomID FROM Rooms INNER JOIN RoomType ON roomtype.roomtypeid = rooms.rRoomTypeID WHERE roomtype.roomtypename LIKE '" & cmbRoomType.Text & "'", con1)

            Dim dt As New DataTable()
            da.Fill(dt)

            cmbRoomNumber.Items.Clear()
            For Each AB As DataRow In dt.Rows

                cmbRoomNumber.Items.Add(AB("RoomNumber").ToString())
            Next
        End Using
    End Using
End Sub

Answer 1

Event procedures should contain very little code. Certainly not data access code. DO NOT update the user interface when a connection is open. If you separate your user interface code from your data access code you will have a much easier time debugging and correcting your code. Try to keep methods doing just a single task clearly indicated by the method name.

Private ConStr As String = "Data Source=dbNEAProject.db"
Private Sub FrmBookings_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    FillCustomerDataCombo()
    FillRoomTypeCombo()
End Sub

Private Sub FillCustomerDataCombo()
    Dim dt = GetCustomerData()
    cmbCustomerData.DataSource = dt
    cmbCustomerData.DisplayMember = "fname"
    cmbCustomerData.ValueMember = "CustomerID"
End Sub

Private Function GetCustomerData() As DataTable
    Dim dt As New DataTable()
    Using con1 As New SQLiteConnection(ConStr)
        Using com As New SQLiteCommand("Select CustomerID, fname FROM customers Left JOIN BOOKING ON booking.BCustomerID = customers.customerID where booking.BookingID is null", con1)
            con1.Open()
            dt.Load(com.ExecuteReader)
        End Using
    End Using
    Return dt
End Function

Private Sub FillRoomTypeCombo()
    Dim dt = GetRoomTypeData()
    cmbRoomType.Items.Clear()
    cmbRoomType.DisplayMember = "RoomTypeName"
    cmbRoomType.ValueMember = "RoomTypeID"
    CmbRoomType.DataSource = dt
End Sub

Private Function GetRoomTypeData() As DataTable
    Dim dt As New DataTable()
    Using con1 As New SQLiteConnection(ConStr),
            cmd As New SQLiteCommand("Select RoomTypeName, RoomTypeID FROM RoomType", con1)
        con1.Open()
        dt.Load(cmd.ExecuteReader)
    End Using
    Return dt
End Function

Private Sub CmbRoomType_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbRoomType.SelectedIndexChanged
    Dim dt = GetRoomsOfSelectedType(cmbRoomType.Text)
    cmbRoomNumber.Items.Clear()
    For Each AB As DataRow In dt.Rows
        cmbRoomNumber.Items.Add(AB("RoomNumber").ToString())
    Next
End Sub

Private Function GetRoomsOfSelectedType(type As String) As DataTable
    Dim dt As New DataTable()
    Using con1 As New SQLiteConnection(ConStr),
            cmd As New SQLiteCommand("Select RoomNumber, RoomID FROM Rooms INNER JOIN RoomType ON roomtype.roomtypeid = rooms.rRoomTypeID WHERE roomtype.roomtypename = @Type;", con1)
        cmd.Parameters.Add("@Type", DbType.String).Value = type
        con1.Open
        dt.Load(cmd.ExecuteReader)
    End Using
    Return dt
End Function