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
In article <11************ *********@b28g2 000cwb.googlegr oups.com>,
qu********@gmai l.com says...

>
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!

[Bob Barrows [MVP]]

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

fantum wrote:

i always use...

with rs
if not(.bof and .eof) then
In article <11************ *********@b28g2 000cwb.googlegr oups.com>,
qu********@gmai l.com says...

>>
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='no n-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!

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows [MVP]]

John wrote:

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.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Justin Piper]

On Wed, 23 Aug 2006 06:30:48 -0500, Bob Barrows [MVP]
<re******@NOyah oo.SPAMcomwrote :

John wrote:

>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!! <_<

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:

If rs were Empty, or similarly invalid, On Error Resume Next would resume
inside the If block.

Option Explicit

Dim rs
On Error Resume Next
If Not rs.Eof Then
WScript.Echo "resumed here"
End If

Considering everybody somehow seems to know about that thing without
actually knowing what it _does_, my money's on that. :)

--
Justin Piper
Bizco Technologies
http://www.bizco.com/

[John]

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

<%
Set conn = Server.CreateOb ject("ADODB.Con nection")
con_str = "Provider=sqlol edb;Data Source=[ My Sever's Address
],1433;Initial Catalog=[ Database Name ];User Id=[ db Username
];Password=[ db Password ]"
conn.Connection String = con_str
conn.Open

Set rs = Server.CreateOb ject("ADODB.Rec ordset")
sql = "Select * From user_database Where username=" &
LCase(Request.F orm("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]]

John wrote:

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

<%
Set conn = Server.CreateOb ject("ADODB.Con nection")
con_str = "Provider=sqlol edb;Data Source=[ My Sever's Address
],1433;Initial Catalog=[ Database Name ];User Id=[ db Username
];Password=[ db Password ]"
conn.Connection String = con_str
conn.Open

Set rs = Server.CreateOb ject("ADODB.Rec ordset")
sql = "Select * From user_database Where username=" &
LCase(Request.F orm("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.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Justin Piper]

On Wed, 23 Aug 2006 10:03:48 -0500, John <qu********@gma il.comwrote:

Set rs = Server.CreateOb ject("ADODB.Rec ordset")
sql = "Select * From user_database Where username=" &
LCase(Request.F orm("username") ) ' Non existant username supplied in the
form
On Error Resume Next
rs.Open sql, conn

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:

Set cmd = CreateObject("A DODB.Command")
With cmd
Set .ActiveConnecti on = conn
Set .CommandType = adCmdText
.CommandText = "Select * From user_database Where username=?"
Set rs = .Execute(,Array (LCase(Request. Form("username" ))))
End With

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...

Like I said, when rs.EOF raises an error, On Error Resume Next will resume
in the If block. With this in mind:

If rs.EOF Then Response.Write "rs.EOF = True"

will appear to give the correct result, while:

If Not rs.EOF Then Response.Write "rs.EOF = False"

will not. The test was actually ignored in both cases, and execution
simply resumed inside the If block.

--
Justin Piper
Bizco Technologies
http://www.bizco.com/

[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...
--
Patrice

"John" <qu********@gma il.coma écrit dans le message de news:
11************* *********@b28g2 00...legr oups.com...

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

<%
Set conn = Server.CreateOb ject("ADODB.Con nection")
con_str = "Provider=sqlol edb;Data Source=[ My Sever's Address
],1433;Initial Catalog=[ Database Name ];User Id=[ db Username
];Password=[ db Password ]"
conn.Connection String = con_str
conn.Open

Set rs = Server.CreateOb ject("ADODB.Rec ordset")
sql = "Select * From user_database Where username=" &
LCase(Request.F orm("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]]

John wrote:

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

<%
Set conn = Server.CreateOb ject("ADODB.Con nection")
con_str = "Provider=sqlol edb;Data Source=[ My Sever's Address
],1433;Initial Catalog=[ Database Name ];User Id=[ db Username
];Password=[ db Password ]"
conn.Connection String = con_str
conn.Open

Set rs = Server.CreateOb ject("ADODB.Rec ordset")
sql = "Select * From user_database Where username=" &
LCase(Request.F orm("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.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.