Member Avatar for Sevyt

I got a small problem with getting a filter to work for my database.
When i test them seperatly it works but when i all add them together they stop working.

This is the code i use currently.
All of the fields in the database are text, except for the id which is autonumberic but not really used for the program.

The line thats right above the one thats turned on does work, but as you can see that one will only allow you to filter if you type the exact name.
So if i would search for Testname it would find it, but if i would type Test it would not find anything.

Does anybody see anything wrong?

 Private Sub BtnZoek_Click(sender As Object, e As EventArgs) Handles BtnZoek.Click
        Dim StrNaam As String
        Dim StrVoornaam As String
        Dim StrFamNr As String
        Dim StrArchKast As String
        Dim StrOpmerking As String


        StrNaam = TxtNaam.Text
        StrVoornaam = TxtVoornaam.Text
        StrFamNr = TxtFamNr.Text
        StrArchKast = TxtArchiefKast.Text
        StrOpmerking = TxtOpmerking.Text

        Zoeken("C:\FB.accdb", StrNaam, StrVoornaam, StrFamNr, StrArchKast, StrOpmerking)
    End Sub

    Public Sub Zoeken(ByVal DBase As String, ByVal Naam As String, ByVal Voornaam As String, ByVal FamNr As String, ByVal ArchKast As String, ByVal Opmerking As String)

        Dim sql As String
        Dim connetionString As String
        Dim connection As OleDbConnection
        Dim command As OleDbCommand
        Dim ds As New DataSet
        Dim oledbadapter As New OleDbDataAdapter

        connetionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & DBase
        'sql = "Select * from FamBestand WHERE Naam LIKE '" & Naam + "%" & "' OR Voornaam = '" & Voornaam + "%" & "' OR FamilieNummer = '" & FamNr & "' OR ArchiefKast LIKE '" & ArchKast & "'"
        sql = "Select * from FamBestand WHERE Naam LIKE '" & Naam + "%" & "' AND Voornaam LIKE '" & Voornaam + "%" & "' OR FamilieNummer = '" & FamNr & "' OR ArchiefKast LIKE '" & ArchKast & "'"

        'sql = "Select * from FamBestand WHERE Naam LIKE '" & Naam + "%" & "' AND Voornaam LIKE '" & Voornaam + "%" & "'"
        'sql = "Select * from FamBestand WHERE FamilieNummer LIKE '" & FamNr & "'"
        'sql = "Select * from FamBestand WHERE ArchiefKast LIKE '" & ArchKast + "%" & "'"
        'sql = "Select * from  FamBestand WHERE Opmerking Like '" & Opmerking + "%" & "'"


        'sql = "Select * from FamBestand WHERE Naam LIKE '" & Naam & "' OR Voornaam LIKE '" & Voornaam & "' OR FamilieNummer = '" & FamNr & "' OR ArchiefKast LIKE '" & ArchKast & "'"
        connection = New OleDbConnection(connetionString)

        Try
            connection.Open()
            ds.Clear()
            command = New OleDbCommand(sql, connection)
            oledbadapter.SelectCommand = command
            oledbadapter.Fill(ds, "Create DataView")
            oledbadapter.Dispose()
            command.Dispose()
            connection.Close()
            FamBestandDataGridView.DataSource = ds.Tables(0).DefaultView
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

    End Sub
Edited by Sevyt
  • 2 Contributors
  • 2 Replies
  • 216 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Sevyt

Dani AI

Generated

A quick recap: correctly pointed out the WHERE logic (mixing AND and OR) and reports the problem went away after switching to a variable (dynamic) query. The underlying cause in cases like this is operator precedence — AND binds tighter than OR, so an expression like A AND B OR C is parsed as (A AND B) OR C unless parentheses are used. (learn.microsoft.com)

A robust pattern for this kind of multi-field search is:

  • build the WHERE clause from only the non-empty inputs,
  • use parameterized queries (never inline user text),
  • use the OleDb ? placeholders and add parameters in the same order as the placeholders,
  • decide whether matching should be "starts with" (term%) or "contains" (%term%) and apply the Access wildcard appropriate to the provider. The OLE DB / ACE provider works with ANSI-92 wildcards (so % is correct for OleDb), and OleDb requires positional ? parameters. (learn.microsoft.com)

Example (VB.NET sketch) — dynamic WHERE + parameters:

Dim clauses As New List(Of String)
Dim parms As New List(Of OleDbParameter)

If Not String.IsNullOrWhiteSpace(txtName.Text) Then
  clauses.Add("Naam LIKE ?")
  parms.Add(New OleDbParameter("p1", "%" & txtName.Text.Trim() & "%"))
End If

' ... repeat for other inputs ...

Dim sql As String = "SELECT * FROM FamBestand" & If(clauses.Count > 0, " WHERE " & String.Join(" AND ", clauses), "")

Using cn As New OleDbConnection(connStr)
  Using cmd As New OleDbCommand(sql, cn)
    For Each p In parms
      cmd.Parameters.Add(p)
    Next
    Dim da As New OleDbDataAdapter(cmd)
    Dim dt As New DataTable()
    da.Fill(dt)
    ' bind dt to grid
  End Using
End Using

Practical tips: log the final SQL and parameter values while debugging; wrap mixed AND/OR groups in parentheses to make intent explicit; trim inputs and ignore empty strings; prefer explicit parameter types instead of AddWithValue for production. These practices keep the logic correct (the issue flagged), avoid injection, and make the variable-query approach used reliable. (support.microsoft.com)

Recommended Answers

All 2 Replies

Member Avatar for Begginnerdev

Have you verified the logic of the select?

I see (1) AND (2) OR (3) OR (4)

If this statement gets qualified, you can have a maximum of 3 outcomes.

(1) and (2) are true
(3) is true
(4) is true

Is this correct?

Member Avatar for Sevyt

Sorry just noticed i posted the wrong code.
What you said was correct, they were all supposed to be AND.

The problem was eventually fixed with a variable query.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.