|
 |
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:
|
|
|
View Cart
