getrows breaks if WHERE in sql

[DavidT]

Hello all willing to assist. This should be simple but it's killing me. I
have this query (Access2k):

strsql="SELECT M.NewRingNum "

strsql= strsql & "FROM tblMain M "

strsql= strsql & "WHERE M.NewRingNum LIKE 'PH*' "

strsql= strsql & "ORDER BY M.NewRingNum;"

If run in access it returns a column of information begining with PH like
you would assume. If I insert it into an ASP page and try to set it to
getrows I get an error. Specifically:

Error Type:
ADODB.Recordset (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested
operation requires a current record.


I'm doing it like this:

strConn="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" &
server.mappath("RingTracker_Web.mdb") & ";"


set rsTMP=server.createobject("adodb.recordset")

rsTMP.open strsql, strconn


alldata=rstmp.getrows '<< Error happens here


rstmp.close

set rstmp=nothing

set strconn=nothing

If I comment out the WHERE part, it works. ANY thoughts are appreciated. I'm
stumped.

TIA

David

[Ray at]

Your problem isn't with the fact that you're using WHERE. It's that when
you're using WHERE, you're getting no records back, and you're trying to
GetRows on an empty recordset. That means that there are no records that
are like 'PH*'. With ADO, use % instead of * for your wildcard (even with
Access).

Always check for EOF!

If Not rstemp.EOF Then alldata = rstemp.GetRows()
rsTemp.Close : Set rsTemp = Nothing
'''close and destroy connection

If Not IsEmpty(alldata) Then
'''do stuff
Else
Response.Write "0 records"
End If

Ray at work


"DavidT" <david.timm@t-mobile.com> wrote in message
news:ewSkYjMIEHA.2744@TK2MSFTNGP10.phx.gbl...

> Hello all willing to assist. This should be simple but it's killing me. I
> have this query (Access2k):
>
> strsql="SELECT M.NewRingNum "
>
> strsql= strsql & "FROM tblMain M "
>
> strsql= strsql & "WHERE M.NewRingNum LIKE 'PH*' "
>
> strsql= strsql & "ORDER BY M.NewRingNum;"
>
> If run in access it returns a column of information begining with PH like
> you would assume. If I insert it into an ASP page and try to set it to
> getrows I get an error. Specifically:
>
> Error Type:
> ADODB.Recordset (0x800A0BCD)
> Either BOF or EOF is True, or the current record has been deleted.

Requested

> operation requires a current record.
>
>
> I'm doing it like this:
>
> strConn="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" &
> server.mappath("RingTracker_Web.mdb") & ";"
>
>
> set rsTMP=server.createobject("adodb.recordset")
>
> rsTMP.open strsql, strconn
>
>
> alldata=rstmp.getrows '<< Error happens here

[DavidT]

Ray...Ya da MAN! It was the % that was killing y RS. That's why I would get
data by running the query in Access. I didn't know that was an ADO thing. I
thought it was SQL Server vs Access thing. I made the switch and it works!

Normally I'd check for EOF but there will ALWAYS be values for this RS.
Always. 100% of the time.

Thanks again. You made my Monday.
David - also at work.

"Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
message news:exL25vMIEHA.700@TK2MSFTNGP09.phx.gbl...

> Your problem isn't with the fact that you're using WHERE. It's that when
> you're using WHERE, you're getting no records back, and you're trying to
> GetRows on an empty recordset. That means that there are no records that
> are like 'PH*'. With ADO, use % instead of * for your wildcard (even with
> Access).
>
> Always check for EOF!
>
> If Not rstemp.EOF Then alldata = rstemp.GetRows()
> rsTemp.Close : Set rsTemp = Nothing
> '''close and destroy connection
>
> If Not IsEmpty(alldata) Then
> '''do stuff
> Else
> Response.Write "0 records"
> End If
>
> Ray at work
>
>
> "DavidT" <david.timm@t-mobile.com> wrote in message
> news:ewSkYjMIEHA.2744@TK2MSFTNGP10.phx.gbl...

> > Hello all willing to assist. This should be simple but it's killing me.

I

> > have this query (Access2k):
> >
> > strsql="SELECT M.NewRingNum "
> >
> > strsql= strsql & "FROM tblMain M "
> >
> > strsql= strsql & "WHERE M.NewRingNum LIKE 'PH*' "
> >
> > strsql= strsql & "ORDER BY M.NewRingNum;"
> >
> > If run in access it returns a column of information begining with PH

like

> > you would assume. If I insert it into an ASP page and try to set it to
> > getrows I get an error. Specifically:
> >
> > Error Type:
> > ADODB.Recordset (0x800A0BCD)
> > Either BOF or EOF is True, or the current record has been deleted.

> Requested

> > operation requires a current record.
> >
> >
> > I'm doing it like this:
> >
> > strConn="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" &
> > server.mappath("RingTracker_Web.mdb") & ";"
> >
> >
> > set rsTMP=server.createobject("adodb.recordset")
> >
> > rsTMP.open strsql, strconn
> >
> >
> > alldata=rstmp.getrows '<< Error happens here

>
>

[Ray at]

Cool. Glad it worked.

IMO, there is no such thing as ALWAYS, but that's just me. :]

Rya at work

"DavidT" <david.timm@t-mobile.com> wrote in message
news:O4Z1d4MIEHA.2576@TK2MSFTNGP09.phx.gbl...

> Ray...Ya da MAN! It was the % that was killing y RS. That's why I would

get

> data by running the query in Access. I didn't know that was an ADO thing.

I

> thought it was SQL Server vs Access thing. I made the switch and it works!
>
> Normally I'd check for EOF but there will ALWAYS be values for this RS.
> Always. 100% of the time.
>
> Thanks again. You made my Monday.
> David - also at work.
>
> "Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
> message news:exL25vMIEHA.700@TK2MSFTNGP09.phx.gbl...

> > Your problem isn't with the fact that you're using WHERE. It's that

when

> > you're using WHERE, you're getting no records back, and you're trying to
> > GetRows on an empty recordset. That means that there are no records

that

> > are like 'PH*'. With ADO, use % instead of * for your wildcard (even

with

> > Access).
> >
> > Always check for EOF!

[DavidT]

Funny thing was...not 10 seconds after sending that I said "I wonder...."
and proved myself wrong. =)

I then added code to comepnsate for EOF and humbly change my reply to ALMOST
always.

Thanks again.


"Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
message news:#GBa58MIEHA.3720@tk2msftngp13.phx.gbl...

> Cool. Glad it worked.
>
> IMO, there is no such thing as ALWAYS, but that's just me. :]
>
> Rya at work
>
> "DavidT" <david.timm@t-mobile.com> wrote in message
> news:O4Z1d4MIEHA.2576@TK2MSFTNGP09.phx.gbl...

> > Ray...Ya da MAN! It was the % that was killing y RS. That's why I would

> get

> > data by running the query in Access. I didn't know that was an ADO

thing.

> I

> > thought it was SQL Server vs Access thing. I made the switch and it

works!

> >
> > Normally I'd check for EOF but there will ALWAYS be values for this RS.
> > Always. 100% of the time.
> >
> > Thanks again. You made my Monday.
> > David - also at work.
> >
> > "Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
> > message news:exL25vMIEHA.700@TK2MSFTNGP09.phx.gbl...

> > > Your problem isn't with the fact that you're using WHERE. It's that

> when

> > > you're using WHERE, you're getting no records back, and you're trying

to

> > > GetRows on an empty recordset. That means that there are no records

> that

> > > are like 'PH*'. With ADO, use % instead of * for your wildcard (even

> with

> > > Access).
> > >
> > > Always check for EOF!

>
>