Problem with SLQ-SelectCommand with a wildcard (%)

[Patrice1981]

Hi all,

I made a dataform1 (with the wizard) wich creates a OleDbDataAdapter1 and a objDataSet1. I made a second OleDbDataAdapter 2 and a DataSet2 for another quiery (on the same dataform) and afterwards i put in the following code:

PrivateSub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim Zoekwoord As String
        Zoekwoord = "%" & TextBox1.Text & "%"
        OleDbDataAdapter2.SelectCommand.Parameters("NmEnVn").Value = Zoekwoord
 
        DataSet2.Clear()
        OleDbDataAdapter2.Fill(DataSet2)
 
End Sub

The OleDbDataAdapter2 has the select-command: SELECT NmEnVn, ID FROM Lijst WHERE (NmEnVn = ?)

I put in a datagrid to verifie the result. With the code above, i don't get a result but not an error either.

When I change:

Zoekwoord = "%" & TextBox1.Text & "%"

in

Zoekwoord = TextBox1.Text

I get a result when TextBox1.Text is exactly one of the names in "NmEnVn". But what I want is that if I type "Co" I will get all the results with "Co" in it, like "Cornelis" and "Coppens" and "Cosemans" ...

Can someone help me pls? Can it be the problem that i'm working in a dataform ?

[thecow95]

Try changing your where clause to a LIKE.

SELECT NmEnVn, ID FROM Lijst WHERE (NmEnVn LIKE '?')

[jmcilhinney]

Try changing your where clause to a LIKE.

SELECT NmEnVn, ID FROM Lijst WHERE (NmEnVn LIKE '?')

The like bit is correct, but you don't want the single quotes or the "?" character will be treated as a literal instead of a place-holder:

SELECT NmEnVn, ID FROM Lijst WHERE NmEnVn LIKE ?