ADO can't find records

[Rob Hendrickx]

Hello, i've got a problem.I want to search through a database
with an ado object, but it can't find it.

Here's my code:

dim Mydb as connection
dim Mydyn as recordset
dim filter as string
dim group as string

set mydb = new connection

mydb.cursorlocation = aduseclient
mydb.open "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source=C:\Program Files\Microsoft
Visual Studio\VB98\Bestellingsdatabase1.mdb;"

Group = DataCombo1.Text
Filter = "select * from recipes where groups like '" & group & "*'"

Set Mydyn = New Recordset
Mydyn.Open filter, Mydb, adOpenStatic, adLockOptimistic


If Mydyn.RecordCount = 0 Then
If (MsgBox("there aren't any recipes with the current group " & vbCrLf
& "Do you want to fill in a recipe", vbYesNo, "no recipe found!")) = vbYes
Then
frmrecipes.Show 1
End If
End If


With Mydyn
Do While Not .EOF
Combo1.AddItem .Fields("recipes")
.MoveNext
Loop


I always get the message to fill in a recipe, so the recordcount is 0. And
I know that there are records. When I ommit the "where" clausule of the sql
string, the computer will find all the records. Is it something with the
sql string ???

with regards
Rob Hendrickx

[Simon Sellick]

Rob,

You could try replacing '*' with '%' - I'm not sure which Access prefers
from a non-Access connection, but SQL Server and Oracle both use '%' as the
wildcard. Also, try putting the wildcard at both ends of the search, just
to prove the point.

Filter = "select * from recipes where groups like '%" & group & "%'"

I think you're right that the SQL is the problem, but I can't see anything
obviously wrong with it.

HTH

Simon.

"Rob Hendrickx" <Rob_hendrickx@hotmail.com> wrote:
>
>Hello, i've got a problem.I want to search through a database
>with an ado object, but it can't find it.
>
>Here's my code:
>
>dim Mydb as connection
>dim Mydyn as recordset
>dim filter as string
>dim group as string
>
>set mydb = new connection
>
>mydb.cursorlocation = aduseclient
>mydb.open "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source=C:\Program Files\Microsoft
>Visual Studio\VB98\Bestellingsdatabase1.mdb;"
>
>Group = DataCombo1.Text
> Filter = "select * from recipes where groups like '" & group & "*'"
>
>Set Mydyn = New Recordset
>Mydyn.Open filter, Mydb, adOpenStatic, adLockOptimistic
>
>
>If Mydyn.RecordCount = 0 Then
> If (MsgBox("there aren't any recipes with the current group " & vbCrLf
>& "Do you want to fill in a recipe", vbYesNo, "no recipe found!")) = vbYes
>Then
> frmrecipes.Show 1
> End If
>End If
>
>
>With Mydyn
> Do While Not .EOF
> Combo1.AddItem .Fields("recipes")
> .MoveNext
>Loop
>
>
>I always get the message to fill in a recipe, so the recordcount is 0. And
>I know that there are records. When I ommit the "where" clausule of the
sql
>string, the computer will find all the records. Is it something with the
>sql string ???
>
>with regards
>Rob Hendrickx
>
>
>

[rob]

"Rob Hendrickx" <Rob_hendrickx@hotmail.com> wrote:
>
>Hello, i've got a problem.I want to search through a database
>with an ado object, but it can't find it.
>
>Here's my code:
>
>dim Mydb as connection
>dim Mydyn as recordset
>dim filter as string
>dim group as string
>
>set mydb = new connection
>
>mydb.cursorlocation = aduseclient
>mydb.open "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source=C:\Program Files\Microsoft
>Visual Studio\VB98\Bestellingsdatabase1.mdb;"
>
>Group = DataCombo1.Text
> Filter = "select * from recipes where groups like '" & group & "*'"
>
>Set Mydyn = New Recordset
>Mydyn.Open filter, Mydb, adOpenStatic, adLockOptimistic
>
>
>If Mydyn.RecordCount = 0 Then
> If (MsgBox("there aren't any recipes with the current group " & vbCrLf
>& "Do you want to fill in a recipe", vbYesNo, "no recipe found!")) = vbYes
>Then
> frmrecipes.Show 1
> End If
>End If
>
>
>With Mydyn
> Do While Not .EOF
> Combo1.AddItem .Fields("recipes")
> .MoveNext
>Loop
>
>
>I always get the message to fill in a recipe, so the recordcount is 0. And
>I know that there are records. When I ommit the "where" clausule of the
sql
>string, the computer will find all the records. Is it something with the
>sql string ???
>
>with regards
>Rob Hendrickx
>
> have you solved your problem, jay
>

[Rob Hendrickx]

Well thanks a lot, you saved me a couple hours of frustation.
When I replace the * into a % it really works!!!!!!
thanks!!



"rob" <jaypons@hotmail.com> wrote:
>
>"Rob Hendrickx" <Rob_hendrickx@hotmail.com> wrote:
>>
>>Hello, i've got a problem.I want to search through a database
>>with an ado object, but it can't find it.
>>
>>Here's my code:
>>
>>dim Mydb as connection
>>dim Mydyn as recordset
>>dim filter as string
>>dim group as string
>>
>>set mydb = new connection
>>
>>mydb.cursorlocation = aduseclient
>>mydb.open "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source=C:\Program Files\Microsoft
>>Visual Studio\VB98\Bestellingsdatabase1.mdb;"
>>
>>Group = DataCombo1.Text
>> Filter = "select * from recipes where groups like '" & group & "*'"
>>
>>Set Mydyn = New Recordset
>>Mydyn.Open filter, Mydb, adOpenStatic, adLockOptimistic
>>
>>
>>If Mydyn.RecordCount = 0 Then
>> If (MsgBox("there aren't any recipes with the current group " & vbCrLf
>>& "Do you want to fill in a recipe", vbYesNo, "no recipe found!")) = vbYes
>>Then
>> frmrecipes.Show 1
>> End If
>>End If
>>
>>
>>With Mydyn
>> Do While Not .EOF
>> Combo1.AddItem .Fields("recipes")
>> .MoveNext
>>Loop
>>
>>
>>I always get the message to fill in a recipe, so the recordcount is 0.
And
>>I know that there are records. When I ommit the "where" clausule of the
>sql
>>string, the computer will find all the records. Is it something with the
>>sql string ???
>>
>>with regards
>>Rob Hendrickx
>>
>> have you solved your problem, jay
>>
>