使用多个复选框通过dataGridView进行搜索
问题描述:
我已经开始创建一个应用程序,它最终可以让用户选择多个年份组并整理一系列电子邮件地址。使用多个复选框通过dataGridView进行搜索
到目前为止,我已经将一个excel文件导入到一个dataGridView框中,并设法过滤掉了一些项目,比如我可以说我过滤了'7年级'。只要我选择'8年级',以前的搜索就会消失。有时我需要一起选择7,8和9年级。
这就是我到目前为止。
最初我以为我可以创建一个if语句,但它似乎只是搜索声明中最后一件事情。
Public Class Form1
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim dataSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim path As String = "C:\Users\Lenovo\Desktop\Username finder\data.xlsx"
Dim dv As DataView
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Try
MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;")
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [usernames$]", MyConnection)
dataSet = New System.Data.DataSet
MyCommand.Fill(dataSet)
DataGridView1.DataSource = dataSet.Tables(0)
MyConnection.Close()
Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try
End Sub
Private Sub CheckBoxY7_CheckedChanged(sender As Object, e As EventArgs) Handles CheckBoxY7.CheckedChanged
' dv = New DataView(dataSet.Tables(0), "Year = '7' ", "Surname Desc", DataViewRowState.CurrentRows)
' DataGridView1.DataSource = dv
End Sub
Private Sub CheckBoxY8_CheckedChanged(sender As Object, e As EventArgs) Handles CheckBoxY8.CheckedChanged
' dv = New DataView(dataSet.Tables(0), "Year = '8' ", "Surname Desc", DataViewRowState.CurrentRows)
' DataGridView1.DataSource = dv
End Sub
答
每次您选中复选框时,DataView都会丢失前面的RowFilter。你想要做的是使用所有选中的CheckBox控件构建RowFilter。看看这个例子:
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Try
MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;")
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [usernames$]", MyConnection)
dataSet = New System.Data.DataSet
MyCommand.Fill(dataSet)
DataGridView1.DataSource = dataSet.Tables(0)
MyConnection.Close()
Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try
CheckBoxY7.Tag = "7"
CheckBoxY8.Tag = "8"
'etc...
End Sub
Private Sub Year_CheckBox_CheckChanged(ByVal sender As Object, ByVal e As EventArgs) Handles CheckBoxY7.Checked, CheckBoxY8.Checked
Dim row_filter As String = String.Join(" AND ", (From cb As CheckBox In {CheckBoxY7, CheckBoxY8} Where cb.Checked Select $"Year = '{cb.Tag.ToString()}'").ToArray())
DataGridView1.DataSource = New DataView(dataSet.Tables(0), row_filter, "Surname Desc", DataViewRowState.CurrentRows)
End Sub
答
谢谢你的回答。
我已经完成了一个解决方案,为该属性添加一个标签并编写for循环。
For Each ctrl In Panel1.Controls
If (ctrl.GetType() Is GetType(CheckBox)) Then
chkbox = ctrl
If chkbox.Checked = True Then
If FirstLoop Then
qstring = ctrl.Tag
FirstLoop = False
Else
qstring = qstring & " or " & ctrl.Tag
End If
End If
End If
Next
ResultBox.Text = qstring
dv = New DataView(dataSet.Tables(0), qstring, "Surname Desc", DataViewRowState.CurrentRows)
DataGridView1.DataSource = dv
我看到您使用的方法,但它仍然给出错误。 –