p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

activex_data_objects thread: Empty Recordsets vs Closed Recordsets


Message #1 by Terrence Joubert <Terrence@v...> on Mon, 23 Jul 2001 14:25:17 +0400
Hi,

 

I have a recordset retrieving data from a table with the following SELECT

statement

 

strSQL = 'SELECT * FROM tbUser WHERE ID = 1'

 

My retrieval goes like this

 

objRs.Open strSQL, objConn

 

If the recordset retrieved no row, how can I test for that. I tried all the

following methods:

 

1. Testing the RecordCount propoerty

2. Testing the EOF, and BOF properties

 

and none of them seems to work.

 

In MSDN, Microsoft writes this:

If you open a Recordset object containing no records, the BOF and EOF

properties are both set to True 

and the value of the Recordset object's RecordCount property setting depends

on the cursor type. -1 will 

be returned for dynamic cursors (CursorType = adOpenDynamic) and 0 will be

returned for other cursors.



Can someone please advise on a method that I can use to get around this?

 

Thank you

Terrence



Message #2 by "Reed Mohn, Anders" <Anders.Reed.Mohn@i...> on Mon, 23 Jul 2001 13:34:34 +0200
Wrox's: ADO 2.6 Programmers Reference says:



"If the Recordset does not support approximate positioning or

bookmarks, it must be fully populated before an accurate 

RecordCount value can be returned. One way to achieve this

is by using MoveLast to move to the last row in the recordset,

assuming the recordset supports MoveLast. If .. not .. all rows

have to be read before an accurate count can be determined."



What did RecordCount return when you tried?



Cheers,

Anders :)







> -----Original Message-----

> From: Terrence Joubert [mailto:Terrence@v...]

> Sent: 23. juli 2001 12:25

> To: ActiveX_Data_Objects

> Subject: [activex_data_objects] Empty Recordsets vs Closed Recordsets

> 

> 

> Hi,

>  

> I have a recordset retrieving data from a table with the 

> following SELECT

> statement

>  

> strSQL = 'SELECT * FROM tbUser WHERE ID = 1'

>  

> My retrieval goes like this

>  

> objRs.Open strSQL, objConn

>  

Message #3 by Terrence Joubert <Terrence@v...> on Mon, 23 Jul 2001 15:37:29 +0400
Hi Anders,



Thanks for the tip.

I still get this error message: 

"Operation is not allowed when the object is closed."



It seems like the recordset does not open at all whenever

no row is returned from its attempt to retrieve. 



According to Microsoft logic, it should be open, but it is

empty, at least this is how I understand it from the MSDN.



Thanks again. :-)



Terrence







-----Original Message-----

From: Reed Mohn, Anders [mailto:Anders.Reed.Mohn@i...] 

Sent: Monday, 23 July, 2001 3:35 PM

To: ActiveX_Data_Objects

Subject: [activex_data_objects] RE: Empty Recordsets vs Closed Recordsets





Wrox's: ADO 2.6 Programmers Reference says:



"If the Recordset does not support approximate positioning or bookmarks, it

must be fully populated before an accurate 

RecordCount value can be returned. One way to achieve this

is by using MoveLast to move to the last row in the recordset, assuming the

recordset supports MoveLast. If .. not .. all rows have to be read before an

accurate count can be determined."



What did RecordCount return when you tried?



Cheers,

Anders :)







> -----Original Message-----

> From: Terrence Joubert [mailto:Terrence@v...]

> Sent: 23. juli 2001 12:25

> To: ActiveX_Data_Objects

> Subject: [activex_data_objects] Empty Recordsets vs Closed Recordsets

> 

> 

> Hi,

>  

> I have a recordset retrieving data from a table with the

> following SELECT

> statement

>  

> strSQL = 'SELECT * FROM tbUser WHERE ID = 1'

>  

> My retrieval goes like this

>  

> objRs.Open strSQL, objConn

>  

Message #4 by "Reed Mohn, Anders" <Anders.Reed.Mohn@i...> on Mon, 23 Jul 2001 15:01:38 +0200
You're absolutely sure that your objRs.Open is succesful?



Cheers,

Anders :)







> -----Original Message-----

> From: Terrence Joubert [mailto:Terrence@v...]

> Sent: 23. juli 2001 13:37

> To: ActiveX_Data_Objects

> Subject: [activex_data_objects] RE: Empty Recordsets vs Closed Records

> ets

> 

> 

> Hi Anders,

> 

> Thanks for the tip.

> I still get this error message: 

> "Operation is not allowed when the object is closed."

> 

> It seems like the recordset does not open at all whenever

> no row is returned from its attempt to retrieve. 

> 

> According to Microsoft logic, it should be open, but it is

> empty, at least this is how I understand it from the MSDN.

> 

> Thanks again. :-)

> 



Message #5 by Terrence Joubert <Terrence@v...> on Mon, 23 Jul 2001 17:17:19 +0400
This is what I cannot understand. 

My Open method is passed only the query and the Connection parameters:



objRs.Open strQuery, objConn



No other parameters are passed. 

After this call, when there are rows within the recordset, the subsequent

operations on the recordset work perfect. The problem is when the recordset 

do not contain any row after calling the Open method.

I want to test whether a row is present or not, and I cannot seem to be able

to do that, neither with the recordCount property nor the EOF, BOF

properties.



Thanks for the help Anders. 



Ciao 

Terrence



 



-----Original Message-----

From: Reed Mohn, Anders [mailto:Anders.Reed.Mohn@i...] 

Sent: Monday, 23 July, 2001 5:02 PM

To: ActiveX_Data_Objects

Subject: [activex_data_objects] RE: Empty Recordsets vs Closed Records ets





You're absolutely sure that your objRs.Open is succesful?



Cheers,

Anders :)







> -----Original Message-----

> From: Terrence Joubert [mailto:Terrence@v...]

> Sent: 23. juli 2001 13:37

> To: ActiveX_Data_Objects

> Subject: [activex_data_objects] RE: Empty Recordsets vs Closed Records 

> ets

> 

> 

> Hi Anders,

> 

> Thanks for the tip.

> I still get this error message:

> "Operation is not allowed when the object is closed."

> 

> It seems like the recordset does not open at all whenever

> no row is returned from its attempt to retrieve.

> 

> According to Microsoft logic, it should be open, but it is empty, at 

> least this is how I understand it from the MSDN.

> 

> Thanks again. :-)

> 



Message #6 by Joel Hollender <jhollender@V...> on Mon, 23 Jul 2001 10:48:35 -0400
To Terrence Joubert:

did you find out how to get around because I have the same problem. I 

am

checking this way so far but I know this is not the right way



temp =3D DupRec.Fields(0).Value

if err.number =3D 3021 then   '' that means no record

 ???????????????

end if



I am checking for this error so I know if there is a record or not



If you know anything please let me know



Thank you

-----Original Message-----

From: Terrence Joubert [mailto:Terrence@v...]

Sent: Monday, July 23, 2001 7:37 AM

To: ActiveX_Data_Objects

Subject: [activex_data_objects] RE: Empty Recordsets vs Closed Records

ets





Hi Anders,



Thanks for the tip.

I still get this error message:

"Operation is not allowed when the object is closed."



It seems like the recordset does not open at all whenever

no row is returned from its attempt to retrieve.



According to Microsoft logic, it should be open, but it is

empty, at least this is how I understand it from the MSDN.



Thanks again. :-)



Terrence







-----Original Message-----

From: Reed Mohn, Anders [mailto:Anders.Reed.Mohn@i...]

Sent: Monday, 23 July, 2001 3:35 PM

To: ActiveX_Data_Objects

Subject: [activex_data_objects] RE: Empty Recordsets vs Closed 

Recordsets





Wrox's: ADO 2.6 Programmers Reference says:



"If the Recordset does not support approximate positioning or 

bookmarks, it

must be fully populated before an accurate

RecordCount value can be returned. One way to achieve this

is by using MoveLast to move to the last row in the recordset, assuming 

the

recordset supports MoveLast. If .. not .. all rows have to be read 

before an

accurate count can be determined."



What did RecordCount return when you tried?



Cheers,

Anders :)







> -----Original Message-----

> From: Terrence Joubert [mailto:Terrence@v...]

> Sent: 23. juli 2001 12:25

> To: ActiveX_Data_Objects

> Subject: [activex_data_objects] Empty Recordsets vs Closed Recordsets

>

>

> Hi,

> 

> I have a recordset retrieving data from a table with the

> following SELECT

> statement

> 

> strSQL =3D 'SELECT * FROM tbUser WHERE ID =3D 1'

> 

> My retrieval goes like this

> 

> objRs.Open strSQL, objConn

> 



Message #7 by Terrence Joubert <Terrence@v...> on Mon, 23 Jul 2001 19:03:13 +0400
Thanks Joel,



This worked perfect. I believe the next version of ADO

should have an isOpen property that we can use to check

The Open/Close state of a recordset.



Terrence



-----Original Message-----

From: Joel Hollender [mailto:jhollender@V...] 

Sent: Monday, 23 July, 2001 6:49 PM

To: ActiveX_Data_Objects

Subject: [activex_data_objects] RE: Empty Recordsets vs Closed Records ets





To Terrence Joubert:

did you find out how to get around because I have the same problem. I am

checking this way so far but I know this is not the right way 



temp = DupRec.Fields(0).Value

if err.number = 3021 then   '' that means no record

 ???????????????

end if 



I am checking for this error so I know if there is a record or not 



If you know anything please let me know 



Thank you 

-----Original Message-----

From: Terrence Joubert [mailto:Terrence@v...]

Sent: Monday, July 23, 2001 7:37 AM

To: ActiveX_Data_Objects

Subject: [activex_data_objects] RE: Empty Recordsets vs Closed Records ets





Hi Anders,



Thanks for the tip.

I still get this error message: 

"Operation is not allowed when the object is closed."



It seems like the recordset does not open at all whenever

no row is returned from its attempt to retrieve. 



According to Microsoft logic, it should be open, but it is empty, at least

this is how I understand it from the MSDN.



Thanks again. :-)



Terrence







-----Original Message-----

From: Reed Mohn, Anders [mailto:Anders.Reed.Mohn@i...] 

Sent: Monday, 23 July, 2001 3:35 PM

To: ActiveX_Data_Objects

Subject: [activex_data_objects] RE: Empty Recordsets vs Closed Recordsets





Wrox's: ADO 2.6 Programmers Reference says:



"If the Recordset does not support approximate positioning or bookmarks, it

must be fully populated before an accurate 

RecordCount value can be returned. One way to achieve this

is by using MoveLast to move to the last row in the recordset, assuming the

recordset supports MoveLast. If .. not .. all rows have to be read before an

accurate count can be determined."



What did RecordCount return when you tried?



Cheers,

Anders :)







> -----Original Message-----

> From: Terrence Joubert [mailto:Terrence@v...]

> Sent: 23. juli 2001 12:25

> To: ActiveX_Data_Objects

> Subject: [activex_data_objects] Empty Recordsets vs Closed Recordsets

> 

> 

> Hi,

>  

> I have a recordset retrieving data from a table with the following 

> SELECT statement

>  

> strSQL = 'SELECT * FROM tbUser WHERE ID = 1'

>  

> My retrieval goes like this

>  

> objRs.Open strSQL, objConn

>
Message #8 by "Tomm Matthis" <matthis@b...> on Mon, 23 Jul 2001 11:33:29 -0400
It's already there.....



x =3Drs.State

Where x can be:



adStateClosed, adStateOpen, adStateConnecting, adStateExecuting, or 

adStateFetching.



-- Tomm



> -----Original Message-----

> From: Terrence Joubert [mailto:Terrence@v...]

> Sent: Monday, July 23, 2001 11:03 AM

> To: ActiveX_Data_Objects

> Subject: [activex_data_objects] RE: Empty Recordsets vs Closed Records

> ets

>

>

> Thanks Joel,

>

> This worked perfect. I believe the next version of ADO

> should have an isOpen property that we can use to check

> The Open/Close state of a recordset.

>

> Terrence

>

> -----Original Message-----

> From: Joel Hollender [mailto:jhollender@V...]

> Sent: Monday, 23 July, 2001 6:49 PM

> To: ActiveX_Data_Objects

> Subject: [activex_data_objects] RE: Empty Recordsets vs Closed Records 

ets

>

>

> To Terrence Joubert:

> did you find out how to get around because I have the same problem. I 

am

> checking this way so far but I know this is not the right way

>

> temp =3D DupRec.Fields(0).Value

> if err.number =3D 3021 then   '' that means no record

>  ???????????????

> end if

>

> I am checking for this error so I know if there is a record or not

>

> If you know anything please let me know

>

> Thank you

> -----Original Message-----

> From: Terrence Joubert [mailto:Terrence@v...]

> Sent: Monday, July 23, 2001 7:37 AM

> To: ActiveX_Data_Objects

> Subject: [activex_data_objects] RE: Empty Recordsets vs Closed Records 

ets

>

>

> Hi Anders,

>

> Thanks for the tip.

> I still get this error message:

> "Operation is not allowed when the object is closed."

>

> It seems like the recordset does not open at all whenever

> no row is returned from its attempt to retrieve.

>

> According to Microsoft logic, it should be open, but it is empty, at 

least

> this is how I understand it from the MSDN.

>

> Thanks again. :-)

>

> Terrence

>

>

>

> -----Original Message-----

> From: Reed Mohn, Anders [mailto:Anders.Reed.Mohn@i...]

> Sent: Monday, 23 July, 2001 3:35 PM

> To: ActiveX_Data_Objects

> Subject: [activex_data_objects] RE: Empty Recordsets vs Closed 

Recordsets

>

>

> Wrox's: ADO 2.6 Programmers Reference says:

>

> "If the Recordset does not support approximate positioning or

> bookmarks, it

> must be fully populated before an accurate

> RecordCount value can be returned. One way to achieve this

> is by using MoveLast to move to the last row in the recordset,

> assuming the

> recordset supports MoveLast. If .. not .. all rows have to be

> read before an

> accurate count can be determined."

>

> What did RecordCount return when you tried?

>

> Cheers,

> Anders :)

>

>

>

> > -----Original Message-----

> > From: Terrence Joubert [mailto:Terrence@v...]

> > Sent: 23. juli 2001 12:25

> > To: ActiveX_Data_Objects

> > Subject: [activex_data_objects] Empty Recordsets vs Closed 

Recordsets

> >

> >

> > Hi,

> > 

> > I have a recordset retrieving data from a table with the following

> > SELECT statement

> > 

> > strSQL =3D 'SELECT * FROM tbUser WHERE ID =3D 1'

> > 

> > My retrieval goes like this

> > 

> > objRs.Open strSQL, objConn

Message #9 by Joel Hollender <jhollender@V...> on Mon, 23 Jul 2001 11:48:25 -0400
So how can I check if the recordset return any records ?



Thank you for your help:



-----Original Message-----

From: Tomm Matthis [mailto:matthis@b...]

Sent: Monday, July 23, 2001 11:33 AM

To: ActiveX_Data_Objects

Subject: [activex_data_objects] RE: Empty Recordsets vs Closed Records

ets





It's already there.....



x =3Drs.State

Where x can be:



adStateClosed, adStateOpen, adStateConnecting, adStateExecuting, or

adStateFetching.



-- Tomm



> -----Original Message-----

> From: Terrence Joubert [mailto:Terrence@v...]

> Sent: Monday, July 23, 2001 11:03 AM

> To: ActiveX_Data_Objects

> Subject: [activex_data_objects] RE: Empty Recordsets vs Closed 

Records

> ets

>

>

> Thanks Joel,

>

> This worked perfect. I believe the next version of ADO

> should have an isOpen property that we can use to check

> The Open/Close state of a recordset.

>

> Terrence

>

> -----Original Message-----

> From: Joel Hollender [mailto:jhollender@V...]

> Sent: Monday, 23 July, 2001 6:49 PM

> To: ActiveX_Data_Objects

> Subject: [activex_data_objects] RE: Empty Recordsets vs Closed 

Records ets

>

>

> To Terrence Joubert:

> did you find out how to get around because I have the same problem. I 

am

> checking this way so far but I know this is not the right way

>

> temp =3D DupRec.Fields(0).Value

> if err.number =3D 3021 then   '' that means no record

>  ???????????????

> end if

>

> I am checking for this error so I know if there is a record or not

>

> If you know anything please let me know

>

> Thank you

> -----Original Message-----

> From: Terrence Joubert [mailto:Terrence@v...]

> Sent: Monday, July 23, 2001 7:37 AM

> To: ActiveX_Data_Objects

> Subject: [activex_data_objects] RE: Empty Recordsets vs Closed 

Records ets

>

>

> Hi Anders,

>

> Thanks for the tip.

> I still get this error message:

> "Operation is not allowed when the object is closed."

>

> It seems like the recordset does not open at all whenever

> no row is returned from its attempt to retrieve.

>

> According to Microsoft logic, it should be open, but it is empty, at 

least

> this is how I understand it from the MSDN.

>

> Thanks again. :-)

>

> Terrence

>

>

>

> -----Original Message-----

> From: Reed Mohn, Anders [mailto:Anders.Reed.Mohn@i...]

> Sent: Monday, 23 July, 2001 3:35 PM

> To: ActiveX_Data_Objects

> Subject: [activex_data_objects] RE: Empty Recordsets vs Closed 

Recordsets

>

>

> Wrox's: ADO 2.6 Programmers Reference says:

>

> "If the Recordset does not support approximate positioning or

> bookmarks, it

> must be fully populated before an accurate

> RecordCount value can be returned. One way to achieve this

> is by using MoveLast to move to the last row in the recordset,

> assuming the

> recordset supports MoveLast. If .. not .. all rows have to be

> read before an

> accurate count can be determined."

>

> What did RecordCount return when you tried?

>

> Cheers,

> Anders :)

>

>

>

> > -----Original Message-----

> > From: Terrence Joubert [mailto:Terrence@v...]

> > Sent: 23. juli 2001 12:25

> > To: ActiveX_Data_Objects

> > Subject: [activex_data_objects] Empty Recordsets vs Closed 

Recordsets

> >

> >

> > Hi,

> > 

> > I have a recordset retrieving data from a table with the following

> > SELECT statement

> > 

> > strSQL =3D 'SELECT * FROM tbUser WHERE ID =3D 1'

> > 

> > My retrieval goes like this

> > 

> > objRs.Open strSQL, objConn



Message #10 by "Tomm Matthis" <matthis@b...> on Mon, 23 Jul 2001 12:31:03 -0400
What database are you using?

Normally, you would check like this:

If (rs.BOF and rs.EOF) then

	'There are no records

Else

 	recount =3D rs.RecordCount

	'Do what you will with the data here.

End If.



For some databases, you may have to do a rs.MoveLast followed by a 

rs.MoveFirst in order to rs.RecordCount populated correctly.



-- Tomm



> -----Original Message-----

> From: Joel Hollender [mailto:jhollender@V...]

> Sent: Monday, July 23, 2001 11:48 AM

> To: ActiveX_Data_Objects

> Subject: [activex_data_objects] RE: Empty Recordsets vs Closed Records

> ets

>

>

> So how can I check if the recordset return any records ?

>

> Thank you for your help:

>

Message #11 by Joel Hollender <jhollender@V...> on Mon, 23 Jul 2001 12:37:28 -0400
I am using SQLserver . ODBC Connection



Thank you



-----Original Message-----

From: Tomm Matthis [mailto:matthis@b...]

Sent: Monday, July 23, 2001 12:31 PM

To: ActiveX_Data_Objects

Subject: [activex_data_objects] RE: Empty Recordsets vs Closed Records

ets





What database are you using?

Normally, you would check like this:

If (rs.BOF and rs.EOF) then

	'There are no records

Else

 	recount =3D rs.RecordCount

	'Do what you will with the data here.

End If.



For some databases, you may have to do a rs.MoveLast followed by a

rs.MoveFirst in order to rs.RecordCount populated correctly.



-- Tomm



> -----Original Message-----

> From: Joel Hollender [mailto:jhollender@V...]

> Sent: Monday, July 23, 2001 11:48 AM

> To: ActiveX_Data_Objects

> Subject: [activex_data_objects] RE: Empty Recordsets vs Closed 

Records

> ets

>

>

> So how can I check if the recordset return any records ?

>

> Thank you for your help:

>
Message #12 by "Tomm Matthis" <matthis@b...> on Mon, 23 Jul 2001 13:24:43 -0400
Then you won't have to do rs.MoveFirst / rs.MoveLast then.



-- Tomm



> -----Original Message-----

> From: Joel Hollender [mailto:jhollender@V...]

> Sent: Monday, July 23, 2001 12:37 PM

> To: ActiveX_Data_Objects

> Subject: [activex_data_objects] RE: Empty Recordsets vs Closed Records

> ets

>

>

> I am using SQLserver . ODBC Connection

>

> Thank you

>

> -----Original Message-----

> From: Tomm Matthis [mailto:matthis@b...]

> Sent: Monday, July 23, 2001 12:31 PM

> To: ActiveX_Data_Objects

> Subject: [activex_data_objects] RE: Empty Recordsets vs Closed Records

> ets

>

>

> What database are you using?

> Normally, you would check like this:

> If (rs.BOF and rs.EOF) then

> 	'There are no records

> Else

>  	recount =3D rs.RecordCount

> 	'Do what you will with the data here.

> End If.

>

> For some databases, you may have to do a rs.MoveLast followed by a

> rs.MoveFirst in order to rs.RecordCount populated correctly.

>

> -- Tomm

>

> > -----Original Message-----

> > From: Joel Hollender [mailto:jhollender@V...]

> > Sent: Monday, July 23, 2001 11:48 AM

> > To: ActiveX_Data_Objects

> > Subject: [activex_data_objects] RE: Empty Recordsets vs Closed 

Records

> > ets

> >

> >

> > So how can I check if the recordset return any records ?

> >

> > Thank you for your help:
Message #13 by "Henrik Kim Christensen" <henrik_k_c@h...> on Wed, 25 Jul 2001 14:12:37 +0200
Hi!



I've had the same problems using MS SQL but Wrox "Pro. ASP Data Access" 

suggested the following:



SELECT COUNT(*) AS xxxx FROM tbl_xxxxx



And commented:

"This COUNT(*) query is particularly useful in many situations. It is often 

advantageous to determine if a table has any records of interest before 

<working on rs>...

Most RDBMS' have optimized their query engines to process the COUNT(*) query 

as quickly as possible and it will most likely be the fastest single query 

you will find against any single table."



I hope this will help you :-)



Regards,

Henrik Kim Christensen



>From: "Tomm Matthis" <matthis@b...>

>Reply-To: "ActiveX_Data_Objects" <activex_data_objects@p...>

>To: "ActiveX_Data_Objects" <activex_data_objects@p...>

>Subject: [activex_data_objects] RE: Empty Recordsets vs Closed Records      

>           ets

>Date: Mon, 23 Jul 2001 13:24:43 -0400

>

>Then you won't have to do rs.MoveFirst / rs.MoveLast then.

>

>-- Tomm

>

> > -----Original Message-----

> > From: Joel Hollender [mailto:jhollender@V...]

> > Sent: Monday, July 23, 2001 12:37 PM

> > To: ActiveX_Data_Objects

> > Subject: [activex_data_objects] RE: Empty Recordsets vs Closed Records

> > ets

> >

> >

> > I am using SQLserver . ODBC Connection

> >

> > Thank you

> >

> > -----Original Message-----

> > From: Tomm Matthis [mailto:matthis@b...]

> > Sent: Monday, July 23, 2001 12:31 PM

> > To: ActiveX_Data_Objects

> > Subject: [activex_data_objects] RE: Empty Recordsets vs Closed Records

> > ets

> >

> >

> > What database are you using?

> > Normally, you would check like this:

> > If (rs.BOF and rs.EOF) then

> > 	'There are no records

> > Else

> >  	recount = rs.RecordCount

> > 	'Do what you will with the data here.

> > End If.

> >

> > For some databases, you may have to do a rs.MoveLast followed by a

> > rs.MoveFirst in order to rs.RecordCount populated correctly.

> >

> > -- Tomm

> >

> > > -----Original Message-----

> > > From: Joel Hollender [mailto:jhollender@V...]

> > > Sent: Monday, July 23, 2001 11:48 AM

> > > To: ActiveX_Data_Objects

> > > Subject: [activex_data_objects] RE: Empty Recordsets vs Closed Records

> > > ets

> > >

> > >

> > > So how can I check if the recordset return any records ?

> > >

> > > Thank you for your help:


  Return to Index