disconnected recordsets and eof

[Steven Scaife]

I know this may sound stupid, but when you use a disconnected recordset can
you check for eof or bof, i assume you can just i get an error even when
checking for this

I just dim the RS but dont use set creatobject(recordset) i assume this bit
isn't needed if you arent using a dsn.

Then i use the following statement

set RS = conn.execute(SQL) 'set the RS to open SQL statement 1
if RS.eof or RS.bof then
'blah blah blah
else
do sometbhning
end if

But it errors, any ideas why, below is my connection variables do i still
have to use set rs= server.createobject("adodb.recordset"), i assumed that
this wasn't needed if you used set rs = conn.execute(sql)

thanks for your time

<%
'The declaration section all the regulary used identifiers are in here
dim conn 'the variable to hold the conn connection
dim strconn 'the variable to hold the connection string details
dim strSQL 'the var for the sql string
dim physicaldbpath 'the var holding the physical path of the directory.
Dim RS 'the var for the Recordset
%>
<!--#include file="../connection/connstring.inc"-->
<%
set conn = server.createobject("adodb.connection") 'sets the var string to
a db connection
conn.open strconn 'opens the database
%>

[Aaron [SQL Server MVP]]

> if RS.eof or RS.bof then

What is this going to prove, that "if rs.eof then" alone is not? This OR
check drives me bananas, it's extra code that serves absolutely no purpose
and often confuses the issue.

> But it errors, any ideas why

Of course not. Just like you can't tell me why my car "errors" unless I
give you more information. Can you tell us the actual error message, maybe?
And verify the line that it occurs on?

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

[Steven Scaife]

I get error

ADODB.Field (0x80020009)
Either BOF or EOF is True, or the current record has been deleted. Requested
operation requires a current record.
/testing/Search2.asp

my line of code that it errors on is

set RS = conn.execute(SQL3) 'set the RS to open SQL statement 3
if RS.eof then

Because of the message i put the bof and eof in cos i didn't understand it

thanks for your time

"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:OHpi3QzbEHA.2468@TK2MSFTNGP09.phx.gbl...

> > if RS.eof or RS.bof then

>
> What is this going to prove, that "if rs.eof then" alone is not? This OR
> check drives me bananas, it's extra code that serves absolutely no purpose
> and often confuses the issue.
>

> > But it errors, any ideas why

>
> Of course not. Just like you can't tell me why my car "errors" unless I
> give you more information. Can you tell us the actual error message,

maybe?

> And verify the line that it occurs on?
>
> --
> [url]http://www.aspfaq.com/[/url]
> (Reverse address to reply.)
>
>

[Bob Barrows [MVP]]

Steven Scaife wrote:

> I know this may sound stupid, but when you use a disconnected
> recordset can you check for eof or bof, i assume you can just i get
> an error even when checking for this
>
> I just dim the RS but dont use set creatobject(recordset) i assume
> this bit isn't needed if you arent using a dsn.

?? Where did you get this idea? :-)
The use or non-use of a dsn in your connection's connection string is
totally irrelevant to the question of whether or not you need to explicitly
create your ADODB.Recordset object.

If you use
Set rs = conn.execute ...
or
Set rs = cmd.execute ...

Then you do not need to explicitly create the ADODB.recordset object since
the Execute method implicitly creates one behind the scenes.

If you want to have more control over cursor type, location, etc., then you
need to explicitly create the object and set these properties before you
open the recordset.

>
> Then i use the following statement
>
> set RS = conn.execute(SQL) 'set the RS to open SQL statement 1
> if RS.eof or RS.bof then
> 'blah blah blah
> else
> do sometbhning
> end if
>
> But it errors, any ideas why, below is my connection variables do i
> still have to use set rs= server.createobject("adodb.recordset"), i
> assumed that this wasn't needed if you used set rs = conn.execute(sql)

That is correct. See above.

I do not see a disconnected recordset here. A disconnected recordset is a
recordset that is opened against a data source and then disconnected from
the data source by setting its ActiveConnection property to Nothing.

And how do you expect us to troubleshoot an error when we don't know what
the error is? :-)

Bob Barrows
PS. There is no need to test both BOF and EOF immediately after opening the
recordset. A simple test for EOF will suffice.
--
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.

[Aaron [SQL Server MVP]]

> I get error

>
> ADODB.Field (0x80020009)
> Either BOF or EOF is True, or the current record has been deleted.

Requested

> operation requires a current record.
> /testing/Search2.asp
>
> my line of code that it errors on is
>
> set RS = conn.execute(SQL3) 'set the RS to open SQL statement 3
> if RS.eof then

Great, now can you show more code!? The error above did not happen on these
lines unless something really funky happened before it.

> Because of the message i put the bof and eof in cos i didn't understand it

Don't just do things BECAUSE you don't understand them. Documentation is
out there for a reason...

A

[Steven Scaife]

Thanks for your help i sorted it, its late in the day and i finding it hard
to pick up on my mistakes.

There was a problem with my if statements they should have been nested but
they weren't.

Sorry for wasting your time

although you cleared up the bof eof thingy for me

ta