Very strange behavior of "If Not rs.EOF", Need help...

[John]

I can't get my head around this!

I have the following code:

<%
.... Code for connection to the database ...
.... Code for retrieving recordset ...

If Not rs.EOF Then
... Do something...
End If
%>

Very basic. Very simple. Except for the fact the the conditional is
completely ignored when I run the script!! <_<

That's right... I deliberately enter a non existant value in my SQL
query:

<%
sql = "Select * From user_database Where username='non-existant-name'"
rs.Open sql, conn

If Not rs.EOF Then
... Do something...
End If
%>

In this case rs.EOF should be set to true, since the username that I
entered does not exist! Therefor the condition of Not rs.EOF is not
met, and the code inside the conditional should not run, however for
some strange reason it does run... so what's going on here?!

I noticed that if I write it differently it does work like it's
supposed to:

<%
If rs.EOF Then
Response.Write("The record does not exist.<br />")
Else
... Do Something ...
End If
%>

However it should work the other way too, so this is very strange to
say the least! Does anybody have any idea as to what is going on here?
Is there something wrong with my code? Because I noticed that when I
use the 'Else' clause it does work... but since when is it a problem
writing a conditional without an 'Else' clause?

Thanks!

 

[fantum]

i always use...

with rs
if not(.bof and .eof) then

 

[Bob Barrows [MVP]]

So you're wasting keystrokes. how is that relevant to the user's problem?

 

[Bob Barrows [MVP]]

I can't get my head around this!

I have the following code:

<%
... Code for connection to the database ...
... Code for retrieving recordset ...

If Not rs.EOF Then
... Do something...
End If
%>

Very basic. Very simple. Except for the fact the the conditional is
completely ignored when I run the script!! <_<

That's right... I deliberately enter a non existant value in my SQL
query:

<%
sql = "Select * From user_database Where username='non-existant-name'"
rs.Open sql, conn

If Not rs.EOF Then
... Do something...
End If
%>

In this case rs.EOF should be set to true, since the username that I
entered does not exist! Therefor the condition of Not rs.EOF is not
met, and the code inside the conditional should not run, however for
some strange reason it does run... so what's going on here?!

I noticed that if I write it differently it does work like it's
supposed to:

<%
If rs.EOF Then
Response.Write("The record does not exist.<br />")
Else
... Do Something ...
End If
%>

However it should work the other way too, so this is very strange to
say the least! Does anybody have any idea as to what is going on here?
Is there something wrong with my code? Because I noticed that when I
use the 'Else' clause it does work... but since when is it a problem
writing a conditional without an 'Else' clause?

It's puzzling to me as well. Could you whip up an example using the pubs or
Northwind database so we could try the code ourselves? I suspect the root
cause may be found in the stuff you snipped:

<%
.... Code for connection to the database ...
.... Code for retrieving recordset ...

Give us a more complete example so we can see the behavior for ourselves.

 

[John]

Yeah, alright.. so you guys want a more complete code, here it is:

<%
Set conn = Server.CreateObject("ADODB.Connection")
con_str = "Provider=sqloledb;Data Source=[ My Sever's Address
],1433;Initial Catalog=[ Database Name ];User Id=[ db Username
];Password=[ db Password ]"
conn.ConnectionString = con_str
conn.Open

Set rs = Server.CreateObject("ADODB.Recordset")
sql = "Select * From user_database Where username=" &
LCase(Request.Form("username")) ' Non existant username supplied in the
form

On Error Resume Next
rs.Open sql, conn

If Not rs.EOF Then
Response.Write("The record does not exist.<br />")
' Some more code here...
End If

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
%>

1) When I remove the "On Error Resume Next", I do in fact get an error
message saying that the record doesn't exist.

2) I tried the following code just after the "rs.Open sql, conn" to see
the result:
<%
If rs.EOF Then Response.Write("rs.EOF = True") Else
Response.Write("rs.EOF = False")
%>

Worked like a charm.

But like this:
<%
If Not rs.EOF Then

' Some code here...

End If
%>

It totally ignored the fact that rs.EOF it True, and goes on to execute
the code anyway...
Anyone has anything?

 

[Bob Barrows [MVP]]

Yeah, alright.. so you guys want a more complete code, here it is:

<%
Set conn = Server.CreateObject("ADODB.Connection")
con_str = "Provider=sqloledb;Data Source=[ My Sever's Address
],1433;Initial Catalog=[ Database Name ];User Id=[ db Username
];Password=[ db Password ]"
conn.ConnectionString = con_str
conn.Open

Set rs = Server.CreateObject("ADODB.Recordset")
sql = "Select * From user_database Where username=" &
LCase(Request.Form("username")) ' Non existant username supplied in
the form

We cannot test this since we don't have a database or table with that
name. I'm going to try to test it against the pubs database. I will
reply later on today with the result. In the meantime, read on for my
speculations:

As Justin pointed out (and I suspected), I believe the problem is in
this line:

On Error Resume Next

Comment it out and see if you get an error.

rs.Open sql, conn

If Not rs.EOF Then

If checking EOF causes an error, "Resume Next" causes this line to be
executed:

Response.Write("The record does not exist.<br />")
' Some more code here...
End If

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
%>

1) When I remove the "On Error Resume Next", I do in fact get an error
message saying that the record doesn't exist.

Are you sure? I am unaware of an error message saying that a record does
not exist. What's the exact text of the error message? And which line of
code throws the error??

2) I tried the following code just after the "rs.Open sql, conn" to
see the result:
<%
If rs.EOF Then Response.Write("rs.EOF = True") Else
Response.Write("rs.EOF = False")

If this is a single line, then it really should not be legal. It was an
oversight that the vbscript parser fails to generate an error for this
statement. In VB, this would generate an error (single-line if
statements are not supposed to be able to contain Else clauses - the
vbscript developers goofed here.).

%>

Worked like a charm.

What was the result? Did "False" get written to Response? That would
mean that your code is really on two lines as shown.

It's a single-statement IF statement (I think) so the next statement
follows the Response.Write("rs.EOF = False").

But like this:
<%
If Not rs.EOF Then

' Some code here...

End If
%>

It totally ignored the fact that rs.EOF it True, and goes on to
execute the code anyway...
Anyone has anything?

Yes. see above. Checking EOF is causing an error which, because of the
"on error resume next" is causing the following line to be executed.

 

[Patrice]

From the last message I'm not sure what is the current situation...

As Justin said remove on error resume next if not already done (IMO should
be anyway avoided). What is the exact message you have ? (it's not clear if
this your own "record doesn't exist" message or some other one).

My first thought would have been an incorrect SQL statement...

 

[Bob Barrows [MVP]]

Yeah, alright.. so you guys want a more complete code, here it is:

<%
Set conn = Server.CreateObject("ADODB.Connection")
con_str = "Provider=sqloledb;Data Source=[ My Sever's Address
],1433;Initial Catalog=[ Database Name ];User Id=[ db Username
];Password=[ db Password ]"
conn.ConnectionString = con_str
conn.Open

Set rs = Server.CreateObject("ADODB.Recordset")
sql = "Select * From user_database Where username=" &
LCase(Request.Form("username")) ' Non existant username supplied in
the form

On Error Resume Next
rs.Open sql, conn

If Not rs.EOF Then

Wait a second. This should be:

If rs.EOF then

Response.Write("The record does not exist.<br />")
' Some more code here...
End If

If EOF is true, then there are no records. Not the other way aroung

My initial tests show this working as expected, but I'm not getting an
error when opening the recordset or checking EOF. You really need to
address that error message.

 

[Bob Barrows [MVP]]

John wrote:
My initial tests show this working as expected, but I'm not getting an
error when opening the recordset or checking EOF. You really need to
address that error message.

FYI, here is the code I used to test:

<%
Set conn = Server.CreateObject("ADODB.Connection")
con_str = "Provider=sqloledb;Data Source=clnsqldev7;" & _
"Initial Catalog=pubs;User Id=xxxxxxxx;Password=xxxxxx"
conn.ConnectionString = con_str
conn.Open

Set rs = Server.CreateObject("ADODB.Recordset")
sql = "Select * From authors Where au_fname='me'"

' Non existant username supplied in the form

On Error Resume Next
rs.Open sql, conn

If rs.EOF Then
Response.Write("The record does not exist.<br />")
' Some more code here...
End If

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
%>

 

[John]

You aren't quoting the username. So rs.Open fails, rs.EOF raises an error
(the recordset has to be open for EOF to be valid), and On Error Resume
Next resumes inside the block. Try this instead:

Oh my god! I can't believe I missed that... This is so embarrassing you
guys!
I guess I must have been really tired when I wrote that code... head
wasn't working!

Yes, that was indeed the problem! After I quoted the name everything
started working as it should again...

Thanks a lot for all your time, guys.
Sorry to have bothered you with such a stupid mistake!

John

 

[Bob Barrows [MVP]]

Oh my god! I can't believe I missed that... This is so embarrassing
you guys!
I guess I must have been really tired when I wrote that code... head
wasn't working!

Yes, that was indeed the problem! After I quoted the name everything
started working as it should again...

Thanks a lot for all your time, guys.
Sorry to have bothered you with such a stupid mistake!

Are you interested in a technique to help you avoid that mistake in the
future? This technique not only relieves you of the necessity to ever
worry about delimiters again, it also decisively defeats any sql
injection attempts:

parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e