RecordSet Creation Problem

[HKM]

Hello,

I have a query engine that builds the SQL Query to obtain the
recordSet. Following is an Exmaple Query that my QueryBuilder
outputted

SELECT * FROM BookInfo WHERE BookName LIKE '*cobol*'

When I use this query to build recordset I get empty recordSet back
when the BookInfo table has entries that qualify for this criteria. To
add to it, if I execute this query seperately under MS ACCESS Query
Menu, it works fine. It gives me a perfect output.

Does anyone know why this should happen ?

I'm using Microsoft.Jet.O LEDB.4.0 as my provider and access database
is locally stored.

Thanks in advance,

-HKM

[TC]

Re: RecordSet Creation Problem

Show us the code!

TC


"HKM" <hkm_19@yahoo.c om> wrote in message
news:441d9d10.0 311041441.6c50c b87@posting.goo gle.com...[color=blue]
> Hello,
>
> I have a query engine that builds the SQL Query to obtain the
> recordSet. Following is an Exmaple Query that my QueryBuilder
> outputted
>
> SELECT * FROM BookInfo WHERE BookName LIKE '*cobol*'
>
> When I use this query to build recordset I get empty recordSet back
> when the BookInfo table has entries that qualify for this criteria. To
> add to it, if I execute this query seperately under MS ACCESS Query
> Menu, it works fine. It gives me a perfect output.
>
> Does anyone know why this should happen ?
>
> I'm using Microsoft.Jet.O LEDB.4.0 as my provider and access database
> is locally stored.
>
> Thanks in advance,
>
> -HKM[/color]

[HKM]

Re: RecordSet Creation Problem

Here is the code..

' Code Get the connection
Public Function GetDBConnection (FileName As String) As
ADODB.Connectio n
Dim conn As ADODB.Connectio n
Set conn = New ADODB.Connectio n
conn.Open "Provider=Micro soft.Jet.OLEDB. 4.0; Data Source=" &
FileName
Set GetDBConnection = conn
End Function

' code to generate the record Set from query
Public Function GetRecordSetUsi ngQuery(Query As String) As
ADODB.recordSet
Dim conn As ADODB.Connectio n
Dim recordSet As ADODB.recordSet
Set conn = GetDBConnection ("E:\temp\db1.m db")
Set recordSet = New ADODB.recordSet
recordSet.Open Query, conn, adOpenDynamic, adLockOptimisti c
Set GetRecordSetUsi ngQuery = recordSet
End Function

-- Code above is a part of common module that is used by all the
forms.

Following is the Part of Query builder.

Dim Criteria AS String
Criteria = "SELECT * FROM BookInfo WHERE "

If BookNameCheck.V alue = True And IsNull(BookName Box.Value) = False
Then
Criteria = " BookName Like '*" & BookNameBox.Val ue & "*'"
cnt = cnt + 1
End If
If AuthorNameCheck .Value = True And IsNull(AuthorNa meBox.Value) =
False Then
If cnt > 0 Then
Criteria = Criteria & " AND "
End If
Criteria = Criteria & " AuthorName Like '*" & AuthorNameBox.V alue
& "*'"
cnt = cnt + 1
If CategoryIDCheck .Value = True AND IsNull(Category Box.Value) = False
Then
If cnt > 0 Then
Criteria = Criteria & " AND "
End If
Criteria = Criteria & " CategoryID = " & CategoryBox.Val ue + 1
cnt = cnt + 1
End If

<snip for other other searchable parameters>

Dim rs As ADODB.recordSet
Set rs = Search.GetRecor dSetUsingQuery( Criteria)
If rs.EOF = True Then
MsgBox "Search Did Not Retrieve Any Results", vbInformation, "Book
Search"
Else
DoCmd.OpenForm "SearchResults" , , , , acFormReadOnly, acDialog,
Criteria
End If

Down here it always get empty recordset back for the queries that are
using *LIKE* but if I just include Criteria it will build following
query
SELECT * FROM BookInfo WHERE CategoryID = 1 and this works fine. I
dont understand why it doesnt work the same way when my query is using
LIKE.

Thanks
-HKM



"TC" <a@b.c.d> wrote in message news:<106800129 8.293023@teutho s>...[color=blue]
> Show us the code!
>
> TC
>
>
> "HKM" <hkm_19@yahoo.c om> wrote in message
> news:441d9d10.0 311041441.6c50c b87@posting.goo gle.com...[color=green]
> > Hello,
> >
> > I have a query engine that builds the SQL Query to obtain the
> > recordSet. Following is an Exmaple Query that my QueryBuilder
> > outputted
> >
> > SELECT * FROM BookInfo WHERE BookName LIKE '*cobol*'
> >
> > When I use this query to build recordset I get empty recordSet back
> > when the BookInfo table has entries that qualify for this criteria. To
> > add to it, if I execute this query seperately under MS ACCESS Query
> > Menu, it works fine. It gives me a perfect output.
> >
> > Does anyone know why this should happen ?
> >
> > I'm using Microsoft.Jet.O LEDB.4.0 as my provider and access database
> > is locally stored.
> >
> > Thanks in advance,
> >
> > -HKM[/color][/color]

[Marc]

Re: RecordSet Creation Problem

hkm_19@yahoo.co m (HKM) wrote in message news:<441d9d10. 0311050729.330a ed85@posting.go ogle.com>...[color=blue]
> Here is the code..
>
> ' Code Get the connection[/color]

.......
[color=blue]
> Dim Criteria AS String
> Criteria = "SELECT * FROM BookInfo WHERE "
>
> If BookNameCheck.V alue = True And IsNull(BookName Box.Value) = False
> Then[/color]

hkm, ******** I think in the next line after = " Criteria &" is missing
[color=blue]
> Criteria = " BookName Like '*" & BookNameBox.Val ue & "*'"
> cnt = cnt + 1
> End If
> If AuthorNameCheck .Value = True And IsNull(AuthorNa meBox.Value) =[/color]

......
Marc

[HKM]

Re: RecordSet Creation Problem

Yeah, It was a typo. Thats not the problem because i would've got
syntax error for query execution but I'm getting null record set back.

-HKM

M.HovensGreve@u va.nl (Marc) wrote in message news:<ae4914df. 0311051237.41f1 af60@posting.go ogle.com>...[color=blue]
> hkm_19@yahoo.co m (HKM) wrote in message news:<441d9d10. 0311050729.330a ed85@posting.go ogle.com>...[color=green]
> > Here is the code..
> >
> > ' Code Get the connection[/color]
>
> ......
>[color=green]
> > Dim Criteria AS String
> > Criteria = "SELECT * FROM BookInfo WHERE "
> >
> > If BookNameCheck.V alue = True And IsNull(BookName Box.Value) = False
> > Then[/color]
>
> hkm, ******** I think in the next line after = " Criteria &" is missing
>[color=green]
> > Criteria = " BookName Like '*" & BookNameBox.Val ue & "*'"
> > cnt = cnt + 1
> > End If
> > If AuthorNameCheck .Value = True And IsNull(AuthorNa meBox.Value) =[/color]
>
> .....
> Marc[/color]

[Fletcher Arnold]

Re: RecordSet Creation Problem


"HKM" <hkm_19@yahoo.c om> wrote in message
news:441d9d10.0 311061404.7b22b 27d@posting.goo gle.com...[color=blue]
> Yeah, It was a typo. Thats not the problem because i would've got
> syntax error for query execution but I'm getting null record set back.
>
> -HKM
>
> M.HovensGreve@u va.nl (Marc) wrote in message[/color]
news:<ae4914df. 0311051237.41f1 af60@posting.go ogle.com>...[color=blue][color=green]
> > hkm_19@yahoo.co m (HKM) wrote in message[/color][/color]
news:<441d9d10. 0311050729.330a ed85@posting.go ogle.com>...[color=blue][color=green][color=darkred]
> > > Here is the code..
> > >
> > > ' Code Get the connection[/color]
> >
> > ......
> >[color=darkred]
> > > Dim Criteria AS String
> > > Criteria = "SELECT * FROM BookInfo WHERE "
> > >
> > > If BookNameCheck.V alue = True And IsNull(BookName Box.Value) = False
> > > Then[/color]
> >
> > hkm, ******** I think in the next line after = " Criteria &" is missing
> >[color=darkred]
> > > Criteria = " BookName Like '*" & BookNameBox.Val ue & "*'"
> > > cnt = cnt + 1
> > > End If
> > > If AuthorNameCheck .Value = True And IsNull(AuthorNa meBox.Value) =[/color]
> >
> > .....
> > Marc[/color][/color]



If you are using ADO you will need to use % instead of *

PS make sure that your code does not explode if BookNameBox.Val ue has an
apostrophe in it, e.g.
Billy's Bike Book


Fletcher

[TC]

Re: RecordSet Creation Problem

Sorry, I don't know ADO. But Fletcher's suggestion sounds good to me.

TC


(snip)