指示具有特定 ID 的组合框值?

Question

我做了一个Sub，它将用贷款描述填充我的组合框。我想在不再次查询数据库的情况下获取selectedindexchangedloandescription的loancode。这可能吗？还是应该查询数据库以获取指示的loancode？

Private Sub LoanProducts()
Using cmd As New SqlClient.SqlCommand("SELECT loancode,loandescription FROM LoanProducts", gSQlConn)
Dim dt As New DataTable
Dim da As New SqlClient.SqlDataAdapter
dt.Clear()
da.SelectCommand = cmd
da.Fill(dt)
For Each row As DataRow In dt.Rows
CmbLoanAvail.Items.Add(row("loandescription"))
Next row
End Using
End Sub

预期产出：

每次组合框索引更改时，所选借用描述的借用代码都会显示在文本框中。

Answer 1

将DataTable设置为组合框.DataSource属性并填充.ValueMember和 。DisplayMember具有相应列名的属性。

Private Sub LoanProducts()
Dim query As String = "SELECT loancode, loandescription FROM LoanProducts"
Using command As New SqlCommand(query, gSQlConn)
Dim data As New DataTable
Dim adapter As New SqlClient.SqlDataAdapter With
{
.SelectCommand = cmd
}
adapter.Fill(data)
CmbLoanAvail.ValueMember = "loancode"
CmbLoanAvail.DisplayMember = "loandescription"
CmbLoanAvail.DataSource = data
End Using
End Sub

您可以使用SelectionChangeCommitted事件在用户做出选择时执行某些操作

Private Sub comboBox1_SelectionChangeCommitted(
ByVal sender As Object, ByVal e As EventArgs) Handles comboBox1.SelectionChangeCommitted
Dim combobox As ComboBox = DirectCast(sender, ComboBox)
Dim selectedCode As String = combobox.SelectedValue.ToString()  // returns 'loancode'
End Sub

Answer 2

如果可以将数据库行作为强类型类获取，则可以使用ItemSource、DisplayMember和ValueMember，这将解决您的问题。

如果您提出，您可以使用ComboBoxSelectedIndex属性。此外，您需要将结果集存储在某个类字段中(最好是Pirvate类字段(。请参阅下面的示例代码：

Public Class Form4
Private _result As DataTable
Private Sub Form4_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim dt As New DataTable
dt.Columns.Add("col1")
dt.Columns.Add("col2")
dt.Rows.Add("val11", "val12")
dt.Rows.Add("val21", "val22")
' at this point, we got our result from DB
_result = dt
For Each row As DataRow In dt.Rows
ComboBox1.Items.Add(row("col1"))
Next
End Sub
Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
Dim selectedIndex = ComboBox1.SelectedIndex
Dim selectedRow = _result(selectedIndex)
End Sub
End Class

Answer 3

将数据库对象保留在使用这些对象的方法的本地，以便可以确保关闭和释放它们。请注意第一行Using末尾的逗号。这包括同一Using块中的命令。

在 Using 块之后设置DataSource、DisplayMember和ValueMember，以便在释放连接之前不会运行用户界面代码。

要获取贷款代码，您只需访问ComboBox的SelectValue。

Private Sub LoanProducts()
Dim dt As New DataTable
Using gSqlConn As New SqlConnection("Your connection string"),
cmd As New SqlClient.SqlCommand("SELECT loancode,loandescription FROM LoanProducts", gSqlConn)
gSqlConn.Open()
dt.Load(cmd.ExecuteReader)
End Using
ComboBox1.DataSource = dt
ComboBox1.DisplayMember = "loandescription"
ComboBox1.ValueMember = "loancode"
End Sub
Private Sub ComboBox1_SelectionChangeCommitted(ByVal sender As Object, ByVal e As EventArgs) Handles ComboBox1.SelectionChangeCommitted
MessageBox.Show($"The Loan Code is {ComboBox1.SelectedValue}")
End Sub