RS EOF question

[Mike]

I have some simple code see below.

I have response written out the query and it is valid and returns values
when ran directly against my db, which is Oracle. My OS is Win 2003.

My question is I never get to the point of the loop. It always thinks I am
at the RS.EOF. Similar code works in other asp pages on the same server.
How do I debug this or am I missing something obvious??

Thanks
Mike




CODE

Set Conn = Server.CreateObject("ADODB.Connection")

Conn.Open strConnectString
Set RS = Conn.Execute(SQL)

If RS.Eof then
Response.write "<center><b>"
response.write "There are no values in the system that match your query."
Response.write "<br>Please Try Again!"
response.write "</b><form>"
response.write "<input type='button' value='Retry' onclick=history.back()>"
response.write "</form></center>"
response.end

Else
Do While Not RS.EOF
<stuff>

RS.MoveNext
Loop
End If

Set RS = Nothing
Conn.Close : Set Conn = Nothing

 

[Slim]

I cant see anything wrong although I always use
Do until rs.eof

I cant see hat being the difference.

I suspect that it is your SQL string you did no post

 

[Mike]

The result of this code:

Set RS = Conn.Execute(SQL)

response.write rs.bof & " rs.bof <br>"
response.write rs.eof & " rs.eof <br>"

is
True rs.bof
True rs.eof

 

[Bob Barrows [MVP]]

The result of this code:

Set RS = Conn.Execute(SQL)

response.write rs.bof & " rs.bof <br>"
response.write rs.eof & " rs.eof <br>"

is
True rs.bof
True rs.eof

This obviously means that your query is not returning any records. To debug
it (assuming you are using <ugh!!> dynamic sql) you need to

Response.Write SQL
Response.End

run the page and look at the statement in the browser window. If you've
created it correctly, you should be able to copy it to the clipboard from
the browser window and paste it into the query execution tool for whatever
database you are using and test it.

Bob Barrows

 

[Patrice]

Looks good. You could copy/paste the SQL string to make sure you running the
same query. Try also to use the full name for the object or a simple
COUNT(*) request. I suspect that you don't query against the same data...

 

[Mike]

As I said in my initial post I did response.write it out and paste it in Toad
and ran a query against me oracle db and it returns values.

Here is the query and no I am not using * but I used it for a test and it
too does not return records. If I use a query Select * from
Activity_tracking it returns records.

I just did a test of my query and if I remove the "AND AT_DATE between
'1-Jan-2005' AND '1-Jan-2007'" it returns values. There is something in the
between portion that is causing this??

Mike


Query
Select * from Activity_tracking, activities_ICD, Clients, Projects,
Contracts, methods, curve_definitions, components, runs, ru_cos where
rc_cd_key = cd_key AND pr_cn_key = cn_key AND cn_cl_key = cl_key AND
pr_me_key = me_key AND pr_key = cd_pr_key AND cd_co_key = co_key AND pr_key =
ru_pr_key AND ru_key = rc_ru_key AND AT_RC_RUN_ID = rc_key AND pr_key =
AT_PR_key AND AI_AT_key = AT_key AND AT_DATE between '1-Jan-2005' AND
'1-Jan-2007'

 

[Mike]

It's interesting at least to me if I change the date format so that my query
uses
AND AT_DATE between '2005-Jan-1' AND '2007-Jan-1' it works from asp but not
from my Toad client. I wish I was still using MS SQL 2000

Mike

 

[Bob Barrows [MVP]]

As I said in my initial post I did response.write it out and paste it
in Toad and ran a query against me oracle db and it returns values.

Oh, sorry, i missed that.

Here is the query and no I am not using * but I used it for a test
and it too does not return records. If I use a query Select * from
Activity_tracking it returns records.

I just did a test of my query and if I remove the "AND AT_DATE between
'1-Jan-2005' AND '1-Jan-2007'" it returns values. There is something
in the between portion that is causing this??

I'm not familiar with Oracle, but i suspect the date format is causing a
problem. You should try using the ISO date format (yyyymmdd or yyyy-mm-dd)
and see if it makes a difference. The OLE DB provider you are using may be
misinterpreting the dates.

 

[Mike]

The date format was the issue. From asp it takes one format and from Toad(my
oracle client) it takes a different format. That's why it was difficult to
debug.

Thanks to all

Mike