title: Filter Query On DataGridView
body: <p>I'm trying to build a Multi-parameter query to filter information on a <code>DataGridView</code>. The following code has been generated using the QUERY WIZARD on Visual Studio.</p>
  
  <p>The problem I'm experiencing is that the code doesn't fail, but never returns a result. I'm fairly certain that the issue is with the Booleans that are being used at the end of the query, but I'm not entirely sure.</p>
  
  <p>I call my code for filtering like so:</p>
  
  <pre><code>Private Sub FILTER_Accept_Button_Click(sender As Object, e As EventArgs) Handles FILTER_Accept_Button.Click
      Try
          Me.SparesTableAdapter.RunFilter(Me.Spares_DatabaseDataSet.Spares, Name_Of_PersonToolStripTextBox.Text, Site_NameToolStripTextBox.Text, Filter_Supplier_textbox.Text, FilterPartNo.Text, FilterJobNo.Text, FilterOrdered.Checked, FilterInvoiceRecieved.Checked, FilterPaidInvoices.Checked, FilterReceivedItems.Checked, FilterUrgent.Checked)
  
      Catch ex As System.Exception
          System.Windows.Forms.MessageBox.Show(ex.Message)
      End Try
  End Sub
  </code></pre>
  
  <p>The above code calls the <code>RunFilter</code> function:</p>
  
  <pre><code>Public Overridable Overloads Function RunFilter(ByVal dataTable As Spares_DatabaseDataSet.SparesDataTable, ByVal Name_Of_Person As String, ByVal Site_Name As String, ByVal Spares_Supplier As String, ByVal Part_Number As String, ByVal SAP_Job_Number As String, ByVal Date_Ordered As Boolean, ByVal Invoice_Received As Boolean, ByVal Invoice_Paid As Boolean, ByVal Date_Item_Received As Boolean, ByVal Marked_As_Urgent As Boolean) As Integer
              Me.Adapter.SelectCommand = Me.CommandCollection(6)
              If (Name_Of_Person Is Nothing) Then
                  Me.Adapter.SelectCommand.Parameters(0).Value = Global.System.DBNull.Value
              Else
                  Me.Adapter.SelectCommand.Parameters(0).Value = CType(Name_Of_Person, String)
              End If
              If (Site_Name Is Nothing) Then
                  Me.Adapter.SelectCommand.Parameters(1).Value = Global.System.DBNull.Value
              Else
                  Me.Adapter.SelectCommand.Parameters(1).Value = CType(Site_Name, String)
              End If
              If (Spares_Supplier Is Nothing) Then
                  Me.Adapter.SelectCommand.Parameters(2).Value = Global.System.DBNull.Value
              Else
                  Me.Adapter.SelectCommand.Parameters(2).Value = CType(Spares_Supplier, String)
              End If
              If (Part_Number Is Nothing) Then
                  Me.Adapter.SelectCommand.Parameters(3).Value = Global.System.DBNull.Value
              Else
                  Me.Adapter.SelectCommand.Parameters(3).Value = CType(Part_Number, String)
              End If
              If (SAP_Job_Number Is Nothing) Then
                  Me.Adapter.SelectCommand.Parameters(4).Value = Global.System.DBNull.Value
              Else
                  Me.Adapter.SelectCommand.Parameters(4).Value = CType(SAP_Job_Number, String)
              End If
              If (Date_Ordered = False) Then
                  Me.Adapter.SelectCommand.Parameters(5).Value = CType("", String)
              Else
                  Me.Adapter.SelectCommand.Parameters(5).Value = CType("Not Completed", String)
              End If
              If (Invoice_Received = False) Then
                  Me.Adapter.SelectCommand.Parameters(6).Value = CType("", String)
              Else
                  Me.Adapter.SelectCommand.Parameters(6).Value = CType("Not Completed", String)
              End If
              If (Invoice_Paid = False) Then
                  Me.Adapter.SelectCommand.Parameters(7).Value = CType("", String)
              Else
                  Me.Adapter.SelectCommand.Parameters(7).Value = CType("Not Completed", String)
              End If
              If (Date_Item_Received = False) Then
                  Me.Adapter.SelectCommand.Parameters(8).Value = CType("", String)
              Else
                  Me.Adapter.SelectCommand.Parameters(8).Value = CType("Not Completed", String)
              End If
  
              If (Marked_As_Urgent = False) Then
                  Me.Adapter.SelectCommand.Parameters(9).Value = CType(False, Boolean)
              Else
                  Me.Adapter.SelectCommand.Parameters(9).Value = CType(Marked_As_Urgent, Boolean)
              End If
  
              If (Me.ClearBeforeFill = True) Then
                  dataTable.Clear()
              End If
              Dim returnValue As Integer = Me.Adapter.Fill(dataTable)
              Return returnValue
          End Function
  </code></pre>
  
  <p>The Query that is being used (in the <code>Me.CommandCollection(6)</code>):</p>
  
  <pre><code>"SELECT ID, [Name Of Person], [SAP Job Number], [Site Name], [Asset Description], [Spares Supplier], [Supplier Contact Name], [Supplier Contact Phone Number], [Supplier Contact Email], [Spares Description], [Part Number], [Quantity To Order], Cost, Comments, [Request Date], [Date Ordered], [Ordered By], [Invoice Received], [Invoice Paid], [Method Of Payment], [Date Item Received], [Quote Attatchment], [Marked As Urgent]  FROM Spares  WHERE ([Name Of Person] LIKE ? + '%') AND ([Site Name] LIKE ? + '%') AND ([Spares Supplier] LIKE ? + '%') AND ([Part Number] LIKE ? + '%') AND ([SAP Job Number] = ?) AND([Date Ordered] = ?) AND ([Invoice Received] = ?) AND ([Invoice Paid] = ?) AND ([Date Item Received] = ?) AND ([Marked As Urgent] = ?)"
  </code></pre>
  
  <p>Perhaps the above query is incorrect in some fashion? Any pointers would be great.</p>
  
  <p>Thanks.</p>
  
  <p><strong>UPDATE 1</strong></p>
  
  <p>I have updated my code, I've decided to just write my own filter string:</p>
  
  <pre><code>Public Sub Call_Filter()
  
      Dim myCon = New OleDbConnection(My.Settings.Database_string)
  
      Try
          myCon.Open()
      Catch ex As Exception
          MsgBox("Sorry, An Error Occurred" &amp; vbNewLine &amp; _
                 "Database Not Found!" &amp; vbNewLine &amp; vbNewLine &amp; _
                 "Error Message: " &amp; ex.Message, MsgBoxStyle.OkOnly, "Could Not Locate Database")
          Main.VIEW_SavingMessage.Visible = False
          Exit Sub
      End Try
  
      Dim SQRText As String = "SELECT ID, [Name Of Person], [SAP Job Number], [Site Name], [Asset Description], [Spares Supplier], [Supplier Contact Name], [Supplier Contact Phone Number], " &amp; _
                               "[Supplier Contact Email], [Spares Description], [Part Number], [Quantity To Order], Cost, Comments, [Request Date], [Date Ordered], [Ordered By], [Invoice Received], " &amp; _
                               "[Invoice Paid], [Method Of Payment], [Date Item Received], [Quote Attatchment], [Marked As Urgent] " &amp; _
      "FROM Spares " &amp; _
      "WHERE ([Name Of Person] = @NameOfPerson OR @NameOfPerson = '' OR @NameOfPerson IS NULL) AND " &amp; _
      "([SAP Job Number] = @SAPJobNo OR @SAPJobNo = '' OR @SAPJobNo IS NULL)"
  
  
      Dim sqr As OleDbCommand = New OleDbCommand(SQRText, myCon)
  
      sqr.Parameters.Add("@NameOfPerson", OleDbType.VarChar).Value = Main.Name_Of_PersonToolStripTextBox.Text
      If Main.FilterJobNo.Text &lt;&gt; "" Then
          sqr.Parameters.Add("@SAPJobNo", OleDbType.Integer).Value = Main.FilterJobNo.Text
      Else
          sqr.Parameters.Add("@SAPJobNo", OleDbType.Integer).Value = DBNull.Value
      End If
  
      Try
          Dim command = sqr.ExecuteNonQuery()
      Catch ex As Exception
          MsgBox("Sorry, An error occured!" &amp; vbNewLine &amp; _
     "The database rejected the Delete request" &amp; vbNewLine &amp; vbNewLine &amp; _
     "Error Message: " &amp; ex.Message, MsgBoxStyle.OkOnly, "Database Refused Parameterised Query")
          Exit Sub
      End Try
  
      Main.SparesTableAdapter.Fill(Main.Spares_DatabaseDataSet.Spares)
  
  
      myCon.Close()
  
  End Sub
  </code></pre>
  
  <p>This works, but doesn't update the <code>Datagridview1</code> that is bound to the <code>SparesTableAdapter</code> when filled. Am I missing something here?</p>
  
  <p>Thanks</p>