ADODB.RecordSet, RecordCount always return -1

[Serge Myrand]

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

 

[Serge Myrand]

Great, thank you

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 Myrand]

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

 

[Bob Barrows [MVP]]

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

 

[IPT]

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!

 

[Bob Barrows [MVP]]

Are you Serge?

What is your backend database?

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

 

[IPT]

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 Barrows [MVP]]

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