使用SQL server数据库VB.Net中的数据自动填充组合框

l有一个包含两列的数据源

block_name        total_lands
A-0                 5
A-1                 15
A-2                 18
A-3                 18

我有两个组合框，一个用于块名，另一个用于土地编号第一个加载了块名

Private Sub LoadItems()
SQL.ExecQuery("SELECT block_name FROM blocks;")

For Each i As DataRow In SQL.DBDTable.Rows
ComboBox1.Items.Add(i("block_name"))
Next
End Sub

我希望第二个加载从1到数据源行total_lands中的任何数字

例如，如果用户选择块名为A-1，我希望第二个组合框中添加从1到15的项目

我的代码尝试这样做

Private Sub LoadNoOfLands()
SQL.AddParam("@blockname", ComboBox1.Text)
SQL.ExecQuery("SELECT totla_lands FROM blocks WHERE total_lands LIKE @blockname;")
For Each no As DataRow In SQL.DBDTable.Rows
Dim lands As Integer = no("block_lands")
'For Each i As Integer In lands
'
'
'Next 
Next
End Sub

立即加载所需数据，并使用Enumerator.Range为第二个组合框生成数字集合

Public Class Block
Public ReadOnly Property Name As String
Public ReadOnly Property TotalLands As Integer
Public ReadOnly Property Lands As Integer()
Public Sub New(name As String, totalLands As Intger)
Name = name
TotalLands = totalLands
Lands = Enumerable.Range(1, totalLands).ToArray()
End Sub
End Class
Private Function LoadBlocks() As Block()
Dim query = "SELECT block_name, total_lands FROM table"
Dim rows = SQL.ExecuteQuery(query)
Return rows.AsEnumerable().
Select(Function(row) New Block(row.Field(Of String)("block_name"), row.Field(Of Integer)("total_lands"))).
ToArray()
End Function
Private Sub SetupComboBox()
cmbBlocks.DisplayMemeber = "Name"     
cmbBlocks.DataSource = LoadBlocks()
End Sub
Private Sub cmbBlocks_SelectionChangeCommitted(sender As Object, e As EventArgs) Handles cmbBlocks.SelectionChangeCommitted
Dim combobox As ComboBox = DirectCast(sender, ComboBox)
Dim block As Block = DirectCast(combobox.SelectedValue, Block)
cmbLands.DataSource = block.Lands
End

我想出了一个简单的答案

Private Sub LoadNoOfLands()
cmbLands.Items.Clear()
SQL.AddParam("@blockname", cmbBlocks.Text)
SQL.ExecQuery("SELECT total_lands FROM blocks WHERE block_name LIKE @blockname;")
If SQL.HasException(True) Then Exit Sub
For Each i As DataRow In SQL.DBDTable.Rows
Dim lands = i("total_lands")
Dim r = 1
For r = 1 To lands
cmbLands.Items.Add(r).ToString()
Next
Next
End Sub

此函数只是将组合框形式1的数字添加到total_lands值

我为数据创建了一个类。

Public Class Block
Public Property Name As String
Public Property TotalLands As Integer
Public Sub New(BlockName As String, Lands As Integer)
Name = BlockName
TotalLands = Lands
End Sub
Public Overrides Function ToString() As String
Return Name
End Function
End Class

组合框将对项目调用.ToString，以确定要显示的内容。

我们从数据库中获取数据，并将其添加到List(Of Block)中

Public Function FillBlocksList() As List(Of Block)
Dim lst As New List(Of Block)
Using cn As New SqlConnection("Your connection string"),
cmd As New SqlCommand("Select block_name, total_lands From BlockTable;", cn)
cn.Open()
Using reader = cmd.ExecuteReader
Do While reader.Read
Dim b As New Block(reader.GetString(0), reader.GetInt32(1))
lst.Add(b)
Loop
End Using
End Using
Return lst
End Function

为了填充第一个组合，我们得到列表，然后循环使用它，将Block对象添加到组合中。您可能会从Form.Load调用此。

Public Sub FillBlocksCombo()
Dim lst = FillBlocksList()
For Each item In lst
ComboBox1.Items.Add(item)
Next
End Sub

要填充第二个组合，请将选择项强制转换回其基本类型Block，并使用TotalLands属性添加数字。

Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
Dim NumberOfLands = DirectCast(ComboBox1.SelectedItem, Block).TotalLands
For i = 1 To NumberOfLands
ComboBox2.Items.Add(i.ToString)
Next
End Sub

相关内容

最新更新

热门标签：