如何在vb.net中使用Windows窗体在Access数据库中进行查询

本文介绍了如何在vb.net中使用Windows窗体在Access数据库中进行查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 虽然条件和值是变量..例如，我有6个复选框和6个组合框，复选框表示我需要作为条件制作的字段名称，组合框是将使用的值为每个标准设置。我无法完成的是，如果我检查了多个复选框以添加到SELECT查询中，并因此当我从组合框中选择一个值以反映到SELECT语句中的值时...以下是我的代码，但它不工作，因为我需要它...请帮助我。我需要能够选择一个或多个复选框，然后从组合框中选择值。 Imports 系统 Imports System.IO Imports System.Data Imports System.Management Imports System.Data.OleDb Imports System.ComponentModel Imports Microsoft.Office .Interop Imports Excel = Microsoft.Office.Interop.Excel Public 类报告 Dim cnnOLEDB As 新 OleDbConnection Dim cmdOLEDB 如 新 OleDbCommand Dim cmdInsert 正如 新 OleDbCommand Dim cmdUpdate 正如 新 OleDbCommand Dim cmdDelete 作为 新 OleDbCommand Dim SystemUserName 作为 字符串 = Environment.UserName Dim strConnectionString = Provider = Microsoft.Jet.OLEDB.4.0; Data Source =& System.Environment.CurrentDirectory& \SupplierDB.mdb ' Dim strConnectionString =Provider = Microsoft.Jet.OLEDB.4.0; Data Source = \\mob-fs-01 \Prepaid Complaint Handling\ppp\ SupplierDB.mdb Dim IntialFolder 作为 String = My.Computer.FileSystem.SpecialDirectories.Desktop + \Alerting Tool Extracted Files Dim ChkVar1，ChkVar2，ChkVar3，ChkVar4，ChkVar5，ChkVar6，ChkVar7，ChkVar8 As 字符串 私有 Sub SetFontAndColors（） 使用 我 .DataGridViewReports.DefaultCellStyle .Font = 新字体（ Segoe UI， 9 ） .ForeColor = Color.DarkOrange 。 BackColor = Color.GhostWhite .SelectionForeColor = Color.Black .SelectionBackColor = Color.Orange ' .Font = New Font（Control.DefaultFont，FontStyle.Bold） End 使用 结束 Sub 私有 Sub Export_Report（） 如果 cnnOLEDB.State = ConnectionState.Open 然后 cnnOLEDB.Close（） 结束 如果 cnnOLEDB.ConnectionString = strConnectionString cnnOLEDB.Open（） ' 验证datagridview是否有数据 如果（（ Me .DataGridViewReports.Columns.Count = 0 ）或（ Me .DataGridViewReports.Rows.Count = 0 ））然后 退出 Sub 结束 如果 ' 创建要导出的数据集 Dim dset As 新 DataSet ' 将表添加到数据集 dset.Tables.Add（） ' 向该表添加列 对于 i 作为 整数 = 0 至 我 .DataGridViewReports.ColumnCount - 1 dset.Tables（ 0 ）。Columns.Add（ Me .DataGridViewReports.Columns（i）.HeaderText） 下一步 ' 向表中添加行 Dim dr1 As DataRow 对于 i 作为 整数 = 0 要 我 .DataGridViewReports.RowCount - 1 dr1 = dset.Tables（ 0 ）。NewRow 对于 j As 整数 = 0 到 Me .DataGridViewReports.Columns.Count - 1 dr1（j）= Me .DataGridViewReports.Rows（i）.Cells（j）.Value Next dset.Tables （ 0 ）。Rows.Add （dr1） 下一步 Dim excel 作为 新 Microsoft.Office.Interop.Excel.ApplicationClass Dim wBook As Microsoft.Office.Interop.Excel.Workbook Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet wBook = excel.Workbooks.Add（） wSheet = wBook.ActiveSheet（） Dim dt As System.Data.DataTable = dset.Tables（ 0 ） Dim dc As System.Data.DataColumn Dim dr As System.Data.DataRow Dim colIndex 作为 整数 = 0 Dim rowIndex As 整数 = 0 对于 每个 dc 在 dt.Columns colIndex = colIndex + 1 excel.Cells（ 1 ，colIndex）= dc.ColumnName Next 对于 每个 dr 在 dt.Rows rowIndex = rowIndex + 1 colIndex = 0 对于 每个 dc 在 dt.Columns colIndex = colIndex + 1 excel.Cells（rowIndex + 1 ，colIndex）= dr（dc.ColumnName） 下一步 下一步 Dim time As DateTime = DateTime.Now Dim format 作为 字符串 = MMM d yyyy on-HH mm ss Dim myPath As 字符串 Dim myFile As 字符串 myFile = 生成警报报告& - & （time.ToString（format））& 。xlsx myPath = Environ（ userprofile）& \desktop \Alerting Tool Extracted Files \& myFile wSheet.Columns.AutoFit（） Dim strFileName As 字符串 = myPath Dim blnFileOpen As Boolean = False 尝试 Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite（strFileName） fileTemp.Close（） Catch ex As Exception blnFileOpen = 错误 结束 尝试 如果 System.IO.File.Exists（strFileName）然后 System.IO.File.Delete（strFileName） 结束 如果 wBook.SaveAs（strFileName） excel.Workbooks.Open（strFileName） excel.Visible = False MsgBox（ 数据保存到桌面。） 杀死Excell已打开的流程 Dim proc As System.Diagnostics.Process 对于 每个 proc 在 System.Diagnostics.Process.GetProcessesByName（ EXCEL） 如果 proc.MainWindowTitle.ToString = 然后 proc.Kill （） 结束 如果 下一步 如果 cnnOLEDB.State = ConnectionState.Open 那么 cnnOLEDB.Close（） 结束 如果 结束 Sub 私有 Sub ExecuteQuery（） 如果 cnnOLEDB.State = ConnectionState.Open 然后 cnnOLEDB.Close（） 结束 如果 cnnOLEDB.Connect ionString = strConnectionString cnnOLEDB.Open（） 尝试 如果 chk_Supplier.Checked = True 然后 ChkVar1 = SupplierName ='& cbo_Supplier.Text 如果 chk_Action.Checked = True 然后 ChkVar2 = ActionType ='& cbo_Action.Text 如果 chk_Action.Checked = True 然后 ChkVar3 = Reason ='& cbo_Reason.Text Dim sqlQRY，sqlQRY1，sqlQRY2，AN As 字符串 AN = AND sqlQRY1 = 选择*来自客户 sqlQRY2 = ChkVar1 sqlQRY = sqlQRY1& sqlQRY2& AN& ChkVar2& AN& ChkVar3 Dim da As OleDbDataAdapter Dim ds As DataSet = 新 DataSet da = 新 OleDbDataAdapter（sqlQRY，cnnOLEDB） Dim cb As OleDbCommandBuilder = 新 OleDbCommandBuilder（da） da.Fill（ds， Customers） DataGridViewReports.DataSource = ds DataGridViewReports.DataMember = Customers lbl_RowCount.Text = DataGridViewReports.RowCount Catch ex 作为 OleDbEx ception MsgBox（ex.ToString） 结束 尝试 End Sub Private Sub btnReport_reset_Click（发件人作为 对象 ，e As EventArgs）句柄 btnReport_reset.Click chk_ServiceName.Checked = False chk_Supplier.Checked = False chk_Shortcode.Checked = False chk_DateSent.Checked = False cbo_ServiceName.Text = cbo_Supplier.SelectedIndex = 0 cbo_Shortcode.Text = cbo_DateSent.Text = chk_FeedbackDate.Checked = False chk_Action.Checked = False chk_Reason.Checked = 错误 chk_Feedback.Checked = 错误 cbo_FeedbackDate.Text = cbo_Action.SelectedIndex = 0 cbo_Reason.SelectedIndex = 0 cbo_Feedback.SelectedIndex = 0 DataGridViewReports.DataSource = Nothing 结束 Sub 私有 Sub btnReport_Run_Click（发件人作为 对象，e As EventArgs）句柄 btnReport_Run.Click ExecuteQuery（） 结束 Sub 私有 Sub Reports_FormClosing（发件人作为 对象，e 作为 FormClosingEventArgs）句柄 我 .FormClosing Dim proc As System.Diagnostics.Process 对于 每个 proc 在 System.Diagnostics.Process.GetProcessesByName（ EXCEL） 如果 proc.MainWindowTitle.ToString = 然后 proc.Kill（） 结束 如果 下一步 如果 cnnOLEDB.State = ConnectionState.Open 那么 cnnOLEDB.Close（） 结束 如果 结束 Sub 私有 Sub Reports_Loa d（发件人作为 对象，e 作为 EventArgs）句柄 MyBase .Load cnnOLEDB.ConnectionString = strConnectionString cnnOLEDB.Open （） SetFontAndColors（） cbo_DateSent.Value =今天 cbo_FeedbackDate.Value =今天 结束 Sub 私有 Sub btnReport_Export_Click （发件人作为 对象，e 作为 EventArgs ）句柄 btnReport_Export.Click Export_Report（） 结束 Sub 私人 Sub chk_DateSent_CheckedChanged（sender As Object ，e As EventArgs）句柄 chk_DateSent.CheckedChanged 如果 chk_DateSent.Checked 那么 cbo_DateSent.Enabled = True 其他 cbo_DateSent.Enabled = 错误 结束 如果 结束 Sub 私有 Sub chk_FeedbackDate_CheckedChanged（发件人作为 对象，e As EventArgs）句柄 chk_FeedbackDate.CheckedChanged 如果 chk_FeedbackDate.Checked 然后 cbo_FeedbackDate.Enabled = True Else cbo_FeedbackDate.Enabled = False End 如果 结束 Sub 结束 类 解决方案 这里有几个问题要处理（除了发布与你的问题无关的代码） 首先，在编写sql时发现错误...你还没有关闭单引号任何cbo_xxxx.Text行 2nd - 你正在检查chk_ 动作的值。检查但是使用cbo_ 原因 .Text 3rd（但最重要的） - 你不应该使用字符串连接来构建一个sql查询，特别是如果涉及用户输入。使用参数化查询 [ ^ ]以避免 SQL Injection [ ^ ]。使用命令参数的另一个好处是你不必担心那些单引号。 4th - 你正在添加AND这个词无论您是否添加了任何过滤器。如果您在未选中复选框的情况下调试此代码，则最终使用SQL 选择 * 来自客户其中 AND AND 根本没有任何意义。如果只检查了供应商，那么您将获得 选择 * 来自客户其中 SupplierName = ' cbo_Supplier.Text AND AND 具有讽刺意味的是，如果检查了Suppler和Action，你最终会得到几乎存在的东西（如果不是上面的错误1）选择* From Customers Where SupplierName ='cbo_Supplier。 Text AND ActionType ='cbo_Action.Text AND Reason ='cbo_Reason.Text 尝试这样的事情 Dim cmd As OleDbCommand = 新 OleDbCommand（） Dim sqlQry 作为 字符串 = 选择*来自客户 ' 如果至少有一个，则只添加WHERE检查框 sqlQry& = IIf（chk_Supplier.Checked 或 chk_Action.Checked 或者 chk_Reason.Checked， WHERE， ） ' 仅在选中供应商框时添加参数 b 如果 chk_Supplier.Checked 然后 sqlQry& = SupplierName = @Supplier cmd .Parameters.AddWithValue（ @ Supplier，cbo_Supplier.Text） 结束 如果 如果 chk_Action.Checked 那么 ' 如果选中供应商框，则仅添加AND（即我们已经有了一个过滤器） sqlQry& = IIf（chk_Supplier.Checked， AND ， ） sqlQry& = ActionType = @Action cmd.Parameters.AddWithValue（ @ Action，cbo_Action.Text） 结束 如果 如果 chk_Reason.Checked 那么 ' 如果我们已经有一个先前的过滤器，则只添加AND ' 即检查供应商和/或行动中的一个或两个 sqlQry& = IIf（chk_Supplier.Checked 或 chk_Action.Checked， AND， ） sqlQry& = Reason = @Reason cmd.Parameters.AddWithValue（ @ Reason，cbo_Reason.Text） 结束 如果 cmd.CommandText = sqlQry 我把你的方法与我的方法混淆如下..感谢您的支持。 尝试 如果 cnnOLEDB.State = ConnectionState.Open 然后 cnnOLEDB.Cl ose（） 结束 如果 cnnOLEDB.ConnectionString = strConnectionString cnnOLEDB.Open（） Dim sqlQry As 字符串 = 选择*来自客户 ' 如果至少选中其中一个框，则只添加WHERE sqlQry& = IIf（SupplierName.Checked） 或 Supplier_Feedback.Checked 或 Reason.Checked 或 ServiceName.Checked 或 DateAdded.Checked 或 SupplierFeedbackDate.Checked， WHERE， ） ' 仅在选中供应商框时添加参数 b 如果 SupplierName.Checked 然后 如果 cbo_Supplier.Text = vbNullString 那么 sqlQry& = SupplierName IS NOT NULL Else sqlQry& = SupplierName ='& cbo_Supplier.Text& ' 结束 如果 结束 如果 如果 Supplier_Feedback.Checked 那么 如果 cbo_Feedback.Text = vbNullString 那么 ' 如果选中供应商框，则仅添加AND（即我们已经有过滤器） sqlQry& = IIf（SupplierName.Checked， AND， ） sqlQry& = Supplier_Feedba ck IS NOT NULL 否则 ' 如果选中供应商框，则仅添加AND（即我们已经有了一个过滤器） sqlQry& = IIf（SupplierName.Checked， AND ， ） sqlQry& = Supplier_Feedback ='& cbo_Feedback.Text& ' 结束 如果 结束 如果 如果 Reason.Checked 那么 如果 cbo_Reason.Text = vbNullString 那么 sqlQry& = IIf（SupplierName.Checked 或 Supplier_Feedback.Checked， AND， ） sqlQry& = Reason IS NOT NULL Else ' 如果我们已经有一个先前的过滤器，则只添加AND ' 即检查供应商和/或行动中的一个或两个 sqlQry& = IIf（SupplierName.Checked 或 Supplier_Feedback.Checked， AND， ） sqlQry& = Reason ='& cbo_Reason.Text& ' 结束 如果 结束 如果 如果 ServiceName.Checked 那么 如果 TXTServiceName.Text = vbNullString 那么 ' 即检查供应商和/或行动中的一个或两个 sqlQry& = IIf（SupplierName.Checked 或 Supplier_Feedback.Checked 或 Reason.Checked， AND， ） sqlQry& = ServiceName IS NOT NULL 否则 ' 如果我们已经有一个先前的过滤器，则只添加AND ' 即检查供应商和/或行动中的一个或两个 sqlQry& = IIf（SupplierName.Checked 或 Supplier_Feedback.Checked 或 Reason.Checked， AND， ） sqlQry& = ServiceName LIKE'％& TXTServiceName.Text& ％' 结束 如果 结束 如果 如果 DateAdded.Checked 那么 ' 如果我们已经有一个先前的过滤器，则只添加AND ' 即检查供应商和/或行动中的一个或两个 sqlQry& = IIf（SupplierName.Checked 或 Supplier_Feedback.Checked 或 Reason.Checked 或 ServiceName.Checked， AND， ） sqlQry& = DateAdded> =＃ + cbo_DateSent.Value + ＃和DateAdded< =＃ + cbo_DateSentTo.Value + ＃ 结束 如果 如果 SupplierFeedbackDate.Checked 那么 ' 如果我们已经安装了先前的过滤器，则只添加AND ' 即检查供应商和/或行动中的一个或两个 sqlQry& = IIf（SupplierName.Checked 或 Supplier_Feedback.Checked 或 Reason.Checked 或 ServiceName.Checked 或 DateAdded.Checked ， AND， ） sqlQry& = SupplierFeedbackDate> =＃ + cbo_FeedbackDate.Value + ＃和SupplierFeedbackDate< =＃ + cbo_FeedbackDateTo.Value + ＃ End 如果 SQLText.Text = sqlQry Dim da As OleDbDataAdapter Dim ds As DataSet = 新 DataSet da = 新 OleDbDataAdapter（sqlQry，cnnOLEDB） Dim cb As OleDbCommandBuilder = 新 OleDbCommandBuilder（da） da.Fill（ds， 客户） DataGridViewReports.DataSource = ds DataGridViewReports.DataMember = 客户 lbl_RowCount.Text = DataGridViewReports.RowCount Catch ex As OleDbException 我ssageBox.Show（ex.Message） 结束 尝试 cnnOLEDB.Close（ ） while the criteria and the values are variables .. for example i have 6 check-boxes and 6 combo-boxes the check-boxes represents the field name that i need to make as criteria and the combo boxes are the values that will be set for each criteria. what i cannot accomplish is that if i checked more than one check-box to be added to the SELECT Query and accordingly when i choose a values from combo-boxes to be reflected to the values in the SELECT statement as well.. below are my codes but it is not working as i needed it to ... please help me. i need to be able to select one or many of the check-boxes and then select the values from the combo-boxes.Imports SystemImports System.IOImports System.DataImports System.ManagementImports System.Data.OleDbImports System.ComponentModelImports Microsoft.Office.InteropImports Excel = Microsoft.Office.Interop.ExcelPublic Class Reports Dim cnnOLEDB As New OleDbConnection Dim cmdOLEDB As New OleDbCommand Dim cmdInsert As New OleDbCommand Dim cmdUpdate As New OleDbCommand Dim cmdDelete As New OleDbCommand Dim SystemUserName As String = Environment.UserName Dim strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\SupplierDB.mdb" 'Dim strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\mob-fs-01\Prepaid Complaint Handling\ppp\SupplierDB.mdb" Dim IntialFolder As String = My.Computer.FileSystem.SpecialDirectories.Desktop + "\Alerting Tool Extracted Files" Dim ChkVar1, ChkVar2, ChkVar3, ChkVar4, ChkVar5, ChkVar6, ChkVar7, ChkVar8 As String Private Sub SetFontAndColors() With Me.DataGridViewReports.DefaultCellStyle .Font = New Font("Segoe UI", 9) .ForeColor = Color.DarkOrange .BackColor = Color.GhostWhite .SelectionForeColor = Color.Black .SelectionBackColor = Color.Orange ' .Font = New Font(Control.DefaultFont, FontStyle.Bold) End With End Sub Private Sub Export_Report() If cnnOLEDB.State = ConnectionState.Open Then cnnOLEDB.Close() End If cnnOLEDB.ConnectionString = strConnectionString cnnOLEDB.Open() 'verfying the datagridview having data or not If ((Me.DataGridViewReports.Columns.Count = 0) Or (Me.DataGridViewReports.Rows.Count = 0)) Then Exit Sub End If 'Creating dataset to export Dim dset As New DataSet 'add table to dataset dset.Tables.Add() 'add column to that table For i As Integer = 0 To Me.DataGridViewReports.ColumnCount - 1 dset.Tables(0).Columns.Add(Me.DataGridViewReports.Columns(i).HeaderText) Next 'add rows to the table Dim dr1 As DataRow For i As Integer = 0 To Me.DataGridViewReports.RowCount - 1 dr1 = dset.Tables(0).NewRow For j As Integer = 0 To Me.DataGridViewReports.Columns.Count - 1 dr1(j) = Me.DataGridViewReports.Rows(i).Cells(j).Value Next dset.Tables(0).Rows.Add(dr1) Next Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass Dim wBook As Microsoft.Office.Interop.Excel.Workbook Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet wBook = excel.Workbooks.Add() wSheet = wBook.ActiveSheet() Dim dt As System.Data.DataTable = dset.Tables(0) Dim dc As System.Data.DataColumn Dim dr As System.Data.DataRow Dim colIndex As Integer = 0 Dim rowIndex As Integer = 0 For Each dc In dt.Columns colIndex = colIndex + 1 excel.Cells(1, colIndex) = dc.ColumnName Next For Each dr In dt.Rows rowIndex = rowIndex + 1 colIndex = 0 For Each dc In dt.Columns colIndex = colIndex + 1 excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName) Next Next Dim time As DateTime = DateTime.Now Dim format As String = "MMM d yyyy on- HH mm ss " Dim myPath As String Dim myFile As String myFile = "Alerting Report Generated " & " - " & (time.ToString(format)) & ".xlsx" myPath = Environ("userprofile") & "\desktop\Alerting Tool Extracted Files\" & myFile wSheet.Columns.AutoFit() Dim strFileName As String = myPath Dim blnFileOpen As Boolean = False Try Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName) fileTemp.Close() Catch ex As Exception blnFileOpen = False End Try If System.IO.File.Exists(strFileName) Then System.IO.File.Delete(strFileName) End If wBook.SaveAs(strFileName) excel.Workbooks.Open(strFileName) excel.Visible = False MsgBox("Data saved to your Desktop.") 'Kill Excell opened process Dim proc As System.Diagnostics.Process For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL") If proc.MainWindowTitle.ToString = "" Then proc.Kill() End If Next If cnnOLEDB.State = ConnectionState.Open Then cnnOLEDB.Close() End If End Sub Private Sub ExecuteQuery() If cnnOLEDB.State = ConnectionState.Open Then cnnOLEDB.Close() End If cnnOLEDB.ConnectionString = strConnectionString cnnOLEDB.Open() Try If chk_Supplier.Checked = True Then ChkVar1 = "SupplierName = '" & cbo_Supplier.Text If chk_Action.Checked = True Then ChkVar2 = "ActionType = '" & cbo_Action.Text If chk_Action.Checked = True Then ChkVar3 = "Reason = '" & cbo_Reason.Text Dim sqlQRY, sqlQRY1, sqlQRY2, AN As String AN = " AND " sqlQRY1 = "Select * From Customers Where " sqlQRY2 = ChkVar1 sqlQRY = sqlQRY1 & sqlQRY2 & AN & ChkVar2 & AN & ChkVar3 Dim da As OleDbDataAdapter Dim ds As DataSet = New DataSet da = New OleDbDataAdapter(sqlQRY, cnnOLEDB) Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da) da.Fill(ds, "Customers") DataGridViewReports.DataSource = ds DataGridViewReports.DataMember = "Customers" lbl_RowCount.Text = DataGridViewReports.RowCount Catch ex As OleDbException MsgBox(ex.ToString) End Try End Sub Private Sub btnReport_reset_Click(sender As Object, e As EventArgs) Handles btnReport_reset.Click chk_ServiceName.Checked = False chk_Supplier.Checked = False chk_Shortcode.Checked = False chk_DateSent.Checked = False cbo_ServiceName.Text = "" cbo_Supplier.SelectedIndex = 0 cbo_Shortcode.Text = "" cbo_DateSent.Text = "" chk_FeedbackDate.Checked = False chk_Action.Checked = False chk_Reason.Checked = False chk_Feedback.Checked = False cbo_FeedbackDate.Text = "" cbo_Action.SelectedIndex = 0 cbo_Reason.SelectedIndex = 0 cbo_Feedback.SelectedIndex = 0 DataGridViewReports.DataSource = Nothing End Sub Private Sub btnReport_Run_Click(sender As Object, e As EventArgs) Handles btnReport_Run.Click ExecuteQuery() End Sub Private Sub Reports_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing Dim proc As System.Diagnostics.Process For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL") If proc.MainWindowTitle.ToString = "" Then proc.Kill() End If Next If cnnOLEDB.State = ConnectionState.Open Then cnnOLEDB.Close() End If End Sub Private Sub Reports_Load(sender As Object, e As EventArgs) Handles MyBase.Load cnnOLEDB.ConnectionString = strConnectionString cnnOLEDB.Open() SetFontAndColors() cbo_DateSent.Value = Today cbo_FeedbackDate.Value = Today End Sub Private Sub btnReport_Export_Click(sender As Object, e As EventArgs) Handles btnReport_Export.Click Export_Report() End Sub Private Sub chk_DateSent_CheckedChanged(sender As Object, e As EventArgs) Handles chk_DateSent.CheckedChanged If chk_DateSent.Checked Then cbo_DateSent.Enabled = True Else cbo_DateSent.Enabled = False End If End Sub Private Sub chk_FeedbackDate_CheckedChanged(sender As Object, e As EventArgs) Handles chk_FeedbackDate.CheckedChanged If chk_FeedbackDate.Checked Then cbo_FeedbackDate.Enabled = True Else cbo_FeedbackDate.Enabled = False End If End SubEnd Class 解决方案 You have several issues to deal with here (apart from posting so much code that isn't relevant to your problem)Firstly, spot the error in the preparation of the sql ... you haven't closed the single quotes on any the cbo_xxxx.Text lines2nd - You are checking the value of chk_Action.Checked but using cbo_Reason.Text3rd (and yet the most important) - you should NEVER use string concatenation to build a sql query, especially if user input is involved. Use Parameterized Queries[^] to avoid SQL Injection[^]. One of the other nice things about using command parameters is that you don't have to worry about those single quotes.4th - you are adding the word "AND" regardless of whether you have added in any filters. If you debug this code with no checkboxes checked you end up with SQL of Select * From Customers Where AND ANDwhich does not make any sense at all. If just Supplier is checked then you get Select * From Customers Where SupplierName = 'cbo_Supplier.Text AND ANDIronically if both Suppler and Action are checked you end up with something that is almost there (if it wasn't for error 1 above)Select * From Customers Where SupplierName = 'cbo_Supplier.Text AND ActionType = 'cbo_Action.Text AND Reason = 'cbo_Reason.TextTry something like this insteadDim cmd As OleDbCommand = New OleDbCommand()Dim sqlQry As String = "Select * From Customers "'Only add "WHERE" if at least one of the boxes is checkedsqlQry &= IIf(chk_Supplier.Checked Or chk_Action.Checked Or chk_Reason.Checked, " WHERE ", "")'Only add the parameter for Supplier if Supplier box is checkedIf chk_Supplier.Checked Then sqlQry &= "SupplierName = @Supplier" cmd.Parameters.AddWithValue("@Supplier", cbo_Supplier.Text)End IfIf chk_Action.Checked Then 'Only add "AND" if Supplier box was checked (i.e. we already have a filter) sqlQry &= IIf(chk_Supplier.Checked, " AND ", "") sqlQry &= "ActionType = @Action" cmd.Parameters.AddWithValue("@Action", cbo_Action.Text)End IfIf chk_Reason.Checked Then 'Only add " AND " if we have a previous filter in place already 'I.e. one or both of Supplier and/or Action is checked sqlQry &= IIf(chk_Supplier.Checked Or chk_Action.Checked, " AND ", "") sqlQry &= "Reason = @Reason" cmd.Parameters.AddWithValue("@Reason", cbo_Reason.Text)End Ifcmd.CommandText = sqlQryi mixed up your method with mine as below .. thanks for your support.Try If cnnOLEDB.State = ConnectionState.Open Then cnnOLEDB.Close() End If cnnOLEDB.ConnectionString = strConnectionString cnnOLEDB.Open() Dim sqlQry As String = "Select * From Customers " 'Only add "WHERE" if at least one of the boxes is checked sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked Or Reason.Checked Or ServiceName.Checked Or DateAdded.Checked Or SupplierFeedbackDate.Checked, " WHERE ", "") 'Only add the parameter for Supplier if Supplier box is checked If SupplierName.Checked Then If cbo_Supplier.Text = vbNullString Then sqlQry &= "SupplierName IS NOT NULL" Else sqlQry &= "SupplierName = '" & cbo_Supplier.Text & "'" End If End If If Supplier_Feedback.Checked Then If cbo_Feedback.Text = vbNullString Then 'Only add "AND" if Supplier box was checked (i.e. we already have a filter) sqlQry &= IIf(SupplierName.Checked, " AND ", "") sqlQry &= "Supplier_Feedback IS NOT NULL" Else 'Only add "AND" if Supplier box was checked (i.e. we already have a filter) sqlQry &= IIf(SupplierName.Checked, " AND ", "") sqlQry &= "Supplier_Feedback = '" & cbo_Feedback.Text & "'" End If End If If Reason.Checked Then If cbo_Reason.Text = vbNullString Then sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked, " AND ", "") sqlQry &= "Reason IS NOT NULL" Else 'Only add " AND " if we have a previous filter in place already 'I.e. one or both of Supplier and/or Action is checked sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked, " AND ", "") sqlQry &= "Reason = '" & cbo_Reason.Text & "'" End If End If If ServiceName.Checked Then If TXTServiceName.Text = vbNullString Then 'I.e. one or both of Supplier and/or Action is checked sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked Or Reason.Checked, " AND ", "") sqlQry &= "ServiceName IS NOT NULL" Else 'Only add " AND " if we have a previous filter in place already 'I.e. one or both of Supplier and/or Action is checked sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked Or Reason.Checked, " AND ", "") sqlQry &= "ServiceName LIKE '%" & TXTServiceName.Text & "%'" End If End If If DateAdded.Checked Then 'Only add " AND " if we have a previous filter in place already 'I.e. one or both of Supplier and/or Action is checked sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked Or Reason.Checked Or ServiceName.Checked, " AND ", "") sqlQry &= "DateAdded >= #" + cbo_DateSent.Value + "# And DateAdded <= #" + cbo_DateSentTo.Value + "#" End If If SupplierFeedbackDate.Checked Then 'Only add " AND " if we have a previous filter in place already 'I.e. one or both of Supplier and/or Action is checked sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked Or Reason.Checked Or ServiceName.Checked Or DateAdded.Checked, " AND ", "") sqlQry &= "SupplierFeedbackDate >= #" + cbo_FeedbackDate.Value + "# And SupplierFeedbackDate <= #" + cbo_FeedbackDateTo.Value + "#" End If SQLText.Text = sqlQry Dim da As OleDbDataAdapter Dim ds As DataSet = New DataSet da = New OleDbDataAdapter(sqlQry, cnnOLEDB) Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da) da.Fill(ds, "Customers") DataGridViewReports.DataSource = ds DataGridViewReports.DataMember = "Customers" lbl_RowCount.Text = DataGridViewReports.RowCount Catch ex As OleDbException MessageBox.Show(ex.Message) End Try cnnOLEDB.Close() 这篇关于如何在vb.net中使用Windows窗体在Access数据库中进行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！