在VB.NET中使用2个组合框来填充数据视图

Question

我有问题理解为什么我的一个组合框显示过滤搜索，但另一个没有，我使用相同的代码为两个组合框，但修改了一些SQL查询链接到我的数据库。我还注意到，当我删除或注释掉任何一个组合框的代码时，过滤搜索发生在没有被注释或删除的组合框上。我还使用了"If, else"。表述，但仍然不成立。我还希望两个组合框都用于过滤数据视图。只需记住，一旦从组合框中选择了项目，就会按下搜索按钮，将数据过滤/显示到datagridview中。

亲切的问候下面是我的代码和表单:

[显示冗余数据]https://i.stack.imgur.com/JEQI4.png

[ComboBox Brand works as expected] https://i.stack.imgur.com/6YyBf.png

[组合框类别显示所有内容而不是显示所选类别]https://i.stack.imgur.com/oEfII.png

Private Sub BtnSearch_Click(sender As Object, e As EventArgs) Handles BtnSearch.Click
If Not CmbBrand.SelectedIndex & CmbCategory.SelectedIndex = Nothing Then
BrandDisplay()
ElseIf CmbBrand.SelectedIndex & Not CmbCategory.SelectedIndex = Nothing Then
CategoryDisplay()
ElseIf Not CmbBrand.SelectedIndex & Not CmbCategory.SelectedIndex = Nothing Then
If DbConnect() Then 
DgvRecord.Rows.Clear()
Dim SQLCmd As New OleDbCommand 
With SQLCmd 
.Connection = cn 
.CommandText = "Select * " &
"From TblStock " &
"Where STCategory Like @CategorySearch" 
.Parameters.AddWithValue("@CategorySearch", "%" & CmbCategory.Text & "%") 
Dim rs As OleDbDataReader = .ExecuteReader() 
SQLCmd.ExecuteReader()
While rs.Read 
Dim NewStockRow As New DataGridViewRow()
NewStockRow.CreateCells(DgvRecord)
NewStockRow.SetValues({rs("StockID"), rs("STDateTime"), rs("STCategory"), rs("STBrand"), rs("STItemDescription"), rs("STSerialNumber"), rs("StockIn"), rs("StockOut"), rs("Stock")})
NewStockRow.Tag = rs("StockID")
DgvRecord.Rows.Add(NewStockRow)
End While
rs.Close()
If DgvRecord.Rows(0).Selected = True Then
MessageBox.Show("Please select a Category from the drop down list", "Category", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
End If
End With
End If
End If
cn.Close()
End Sub
Private Sub BrandDisplay()
If DbConnect() Then 
DgvRecord.Rows.Clear()
Dim SQLCmd As New OleDbCommand 
With SQLCmd 
.Connection = cn 
.CommandText = "Select * " &
"From TblStock " &
"Where STBrand Like @BrandSearch" 
.Parameters.AddWithValue("@BrandSearch", "%" & CmbBrand.Text & "%") 
Dim rs As OleDbDataReader = .ExecuteReader() 
SQLCmd.ExecuteReader()
While rs.Read 
Dim NewStockRow As New DataGridViewRow()
NewStockRow.CreateCells(DgvRecord)
NewStockRow.SetValues({rs("StockID"), rs("STDateTime"), rs("STCategory"), rs("STBrand"), rs("STItemDescription"), rs("STSerialNumber"), rs("StockIn"), rs("StockOut"), rs("Stock")})
NewStockRow.Tag = rs("StockID")
DgvRecord.Rows.Add(NewStockRow)
End While
rs.Close() 
If DgvRecord.Rows(0).Selected = True Then
MessageBox.Show("Please select a Brand from the drop down list", "Brand", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
End If
End With
End If
cn.Close()
End Sub
Private Sub CategoryDisplay()
If DbConnect() Then 
DgvRecord.Rows.Clear()
Dim SQLCmd As New OleDbCommand 
With SQLCmd 
.Connection = cn 
.CommandText = "Select * " &
"From TblStock " &
"Where STCategory Like @CategorySearch" 
.Parameters.AddWithValue("@CategorySearch", "%" & CmbCategory.Text & "%")
Dim rs As OleDbDataReader = .ExecuteReader() 
SQLCmd.ExecuteReader()
While rs.Read 
Dim NewStockRow As New DataGridViewRow()
NewStockRow.CreateCells(DgvRecord)
NewStockRow.SetValues({rs("StockID"), rs("STDateTime"), rs("STCategory"), rs("STBrand"), rs("STItemDescription"), rs("STSerialNumber"), rs("StockIn"), rs("StockOut"), rs("Stock")})
NewStockRow.Tag = rs("StockID")
DgvRecord.Rows.Add(NewStockRow)
End While
rs.Close() 
If DgvRecord.Rows(0).Selected = True Then
MessageBox.Show("Please select a Category from the drop down list", "Category", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
End If
End With
End If
cn.Close()
End Sub
```

Answer 1

将用户界面代码与数据库代码分开是个好主意。您的事件过程代码应该相当简短。

在使用连接、命令和数据读取器的方法中声明它们，以便它们可以被处置。使用……使用block为我们完成这些;它们也拉近了联系。将连接字符串直接传递给连接的构造函数。

对于每种可能性，我们有不同的CommandText和ParametersCollection。对于Sql Server，请使用Add方法，而不是AddWithValue。

Private Sub BtnSearch_Click(sender As Object, e As EventArgs) Handles BtnSearch.Click
Dim dt = GetSearchData(CmbBrand.Text, CmbCategory.Text)
DGVRecord.DataSource = dt
End Sub
Private Function GetSearchData(Brand As String, Category As String) As DataTable
Dim dt As New DataTable
Dim sqlString = "Select * From From TblStock "
Using cn As New SqlConnection("Your connection string"),
cmd As New SqlCommand()
cmd.Connection = cn
If Not String.IsNullOrEmpty(Brand) AndAlso Not String.IsNullOrEmpty(Category) Then
cmd.CommandText = sqlString & "Where STCategory = @CategorySearch And STBrand = @BrandSearch;"
cmd.Parameters.Add("@CategorySearch", SqlDbType.VarChar).Value = Brand
cmd.Parameters.Add("@BrandSearch", SqlDbType.VarChar).Value = Category
ElseIf Not String.IsNullOrEmpty(Brand) Then
cmd.CommandText = sqlString & "Where STBrand = @BrandSearch;"
cmd.Parameters.Add("@BrandSearch", SqlDbType.VarChar).Value = Category
ElseIf Not String.IsNullOrEmpty(Category) Then
cmd.CommandText = sqlString & "Where STCategory = @CategorySearch;"
cmd.Parameters.Add("@CategorySearch", SqlDbType.VarChar).Value = Brand
Else
cmd.CommandText = sqlString & ";"
End If
cn.Open()
Using reader = cmd.ExecuteReader()
dt.Load(reader)
End Using
End Using
Return dt
End Function

Answer 2

为了更好地理解，您需要更改前面的"if…然后……其他的……"。如果组合框没有被选中，它的值将是-1，所以你可以这样做:

Dim bBrandIsSelected as boolean = CmbBrand.SelectedIndex <> -1
Dim bCategoryIsSelected as boolean = CmbCategory.SelectedIndex <> -1

现在您可以更轻松地构建代码，如:

If bBrandIsSelected AndAlso bCategoryIsSelected then
' do something
else
if bBrandIsSelected then  
BrandDisplay()
else
if bCategoryIsSelected then
CategoryDisplay()
End if
End if
End if