vb.net - 为什么我的组合框在被选中后不显示数据

Question

我有 2 个链接在一起的组合框，在父组合框中，RoomT 类型在选择一个值后不允许我重新选择不同的值。另外，如果有人这么好心并告诉我如何使用 SQL 查询中组合框中所选值的主键。

这是我得到的：

 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

标签： vb.netsqlitecombobox

Answer 1

事件过程应该包含很少的代码。当然不是数据访问代码。连接打开时不要更新用户界面。如果您将用户界面代码与数据访问代码分开，您将更容易调试和更正代码。尽量让方法只执行由方法名称明确指示的单一任务。

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