ADODB.RecordSet, RecordCount always return -1

Hi,

I have an ADODB.RecordSet that RecordCount alway return -1 and this
RecordSet is plenty of record!

R.RecordSet = -1
R.eof = False
R.BOF = False

Is the cursor is lost somewhere?

How can I do to get the number of record in the result set?

Thank you in advance
serge

Bob
Wed Jun 23 10:28:06 CDT 2004

Serge Myrand wrote:
> Hi,
>
> I have an ADODB.RecordSet that RecordCount alway return -1 and this
> RecordSet is plenty of record!
>
Your answer will be found here:
http://www.aspfaq.com/show.asp?id=2193

HTH,
Bob Barrows
--
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.

Serge
Wed Jun 23 09:34:10 CDT 2004

Great, thank you


"Bob Barrows [MVP]" wrote:

> Serge Myrand wrote:
> > Hi,
> >
> > I have an ADODB.RecordSet that RecordCount alway return -1 and this
> > RecordSet is plenty of record!
> >
> Your answer will be found here:
> http://www.aspfaq.com/show.asp?id=2193
>
> HTH,
> Bob Barrows
> --
> 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.

Serge
Wed Jun 23 09:35:02 CDT 2004

I am sorry for posting in the wrong forum, it's bad when searching
answers..

Serge Myrand wrote:

> Hi,
>
> I have an ADODB.RecordSet that RecordCount alway return -1 and this
> RecordSet is plenty of record!
>
> R.RecordSet = -1
> R.eof = False
> R.BOF = False
>
> Is the cursor is lost somewhere?
>
> How can I do to get the number of record in the result set?
>
> Thank you in advance
> serge

IPT
Sat Jun 26 20:49:42 CDT 2004

I went to the web, and still unable to get it work.

My codes:

sSQL = "SELECT * FROM myTable"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sSQL, myConn, 1, 3
recCount = rs.recordcount
rs.Close
Set rs = Nothing
response.write recCount

It returns -1.

Help!

> Serge Myrand wrote:
> > Hi,
> >
> > I have an ADODB.RecordSet that RecordCount alway return -1 and this
> > RecordSet is plenty of record!
> >
> Your answer will be found here:
> http://www.aspfaq.com/show.asp?id=2193
>
> HTH,
> Bob Barrows
> --
> 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.
>
>

Bob
Sun Jun 27 06:58:06 CDT 2004

Are you Serge?

What is your backend database?

IPT wrote:
> I went to the web, and still unable to get it work.

Why bother? If you read the article I linked to, you will find a more
efficient method (using GetRows) for getting the record count than using an
expensive cursor for your recordset.

>
> My codes:
>
> sSQL = "SELECT * FROM myTable"
> Set rs = Server.CreateObject("ADODB.Recordset")
> rs.Open sSQL, myConn, 1, 3

You do not need an ultra-expensive keyset cursor (although it should work).
All you need is a static cursor. The locktype does not matter.
rs.Open sSQL, myConn, 3, 3, 1

> recCount = rs.recordcount
> rs.Close
> Set rs = Nothing
> response.write recCount
>

Does this reflect your actual purpose? You are opening a recordset for the
sole purpose of getting the record count? That's crazy! Don't pull all the
records from your table across the wire simply to count them.

sSQL = "SELECT count(*) FROM myTable"
set rs = conn.execute(sSQL,,1)
recCount = rs(0)
rs.Close
Set rs = Nothing
conn.Close
Set conn= Nothing
response.write recCount

> It returns -1.
>
> Help!

It should not return -1 with a keyset cursor, unless the provider you are
using for your connection does not support bookmarks. That is why I asked
what database you are using, and why I am now asking you to show us your
connection string (with any password information censored of course).

Bob Barrows

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

IPT
Wed Jun 30 07:42:26 CDT 2004

I'm not Serge.

I use mySQL 4.0, does it support bookmark (.AbsolutePage)?

I used to use Ms Access, and has many functions. Since migrate to mySQL, I
need to modify many codes pertaning to SQL systax. I can't use anymore saved
queries, nested select, etc. Even the 'getdate()' has changed to 'Now()'.

Any free database server you may suggest?

Bob
Wed Jun 30 08:29:27 CDT 2004

IPT wrote:
> I'm not Serge.
>
> I use mySQL 4.0, does it support bookmark (.AbsolutePage)?

I have no idea. One way to find out is to use the Supports method.
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthsupports.asp

http://msdn.microsoft.com/library/en-us/ado270/htm/mdprorecordcount.asp


>
> I used to use Ms Access, and has many functions. Since migrate to
> mySQL, I need to modify many codes pertaning to SQL systax. I can't
> use anymore saved queries, nested select, etc. Even the 'getdate()'
> has changed to 'Now()'.
>
> Any free database server you may suggest?

Why not MSDE?

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