如何在vb.net中使用Windows窗体在Access数据库中进行查询 [英] How to make queries in Access Database using windows form in vb.net

问题描述

虽然条件和值是变量..例如，我有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  到 < span class =code-keyword> 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  < span class =code-keyword> 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 < span class =code-keyword> 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）
 结束 尝试 
 < span class =code-keyword> 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  
 结束 < span class =code-keyword> 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（ < span class =code-string> EXCEL）
 如果 proc.MainWindowTitle.ToString =   然后 
 proc.Kill（）
 < span class =code-keyword>结束 如果 
 下一步 
 如果 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（发件人< span class =code-keyword>作为 对象，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 System
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 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 Sub

End 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 lines

2nd - You are checking the value of chk_Action.Checked but using cbo_Reason.Text

3rd (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  AND

which does not make any sense at all. If just Supplier is checked then you get

Select * From Customers Where SupplierName = 'cbo_Supplier.Text AND  AND

Ironically 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.Text

Try something like this instead

Dim cmd As OleDbCommand = New OleDbCommand()
Dim sqlQry As String = "Select * From Customers "
'Only add "WHERE" if at least one of the boxes is checked
sqlQry &= IIf(chk_Supplier.Checked Or chk_Action.Checked Or chk_Reason.Checked, " WHERE ", "")
'Only add the parameter for Supplier if Supplier box is checked
If chk_Supplier.Checked Then
    sqlQry &= "SupplierName = @Supplier"
    cmd.Parameters.AddWithValue("@Supplier", cbo_Supplier.Text)
End If
If 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 If
If 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 If
cmd.CommandText = sqlQry

i 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数据库中进行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！