Recordset.count using oracle

Can somebody post the code to open a recordset, and obtain the record count (not -1)?  This solution must work for Oracle.  I have tried other solutions, like using adOpenForwardOnly, but I am still having difficulty.  Please do not use constants, like adOpenForwardOnly - use a number instead, as I do not include that file in my ASP pages.

I have looked at, but they seem to only have answers for MS SQLServer or Access.

Thank you.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Here's an alternate method that will work for you:

use the tsql count() function like so:

set rs=conn.execute ("select count (field) as countRec from table where condition)

response.write rs("countRec")
fallon12345Author Commented:
I could do that, but I prefer not to as it will slow my performance.  I want to display the number of records along with all of the records on the same page.  Using your recommendation, I would have to run two separate sql statements.  Since my sql statements are dynamically built via any number of 15 different search criteria, you can see how this is a problem.  Therefore, I do not want to count the records in a sql statement
Oracle only supports a static read-only cursor, so says MSDN.

It should be that hard to use what jmcvinney suggested if you are building your statements dynamically anyways.

Have you tried a client side cursor?

You count open the rs and loop thru' incrementing a count, but I don't think this is good solution.

Could you do a getrows or something to put the recordset into an array, then do a UBound on the array?

jmcvinney's solution seems the best way.
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

You need a cursor type that makes the recordset navigatable in both directions (always required for that sort of operations and intuitivly makes sense).
The static cursor supports that operation. Its the cursortype property of the connection used, like:

MyAdoConnection.CursorType = adUseStatic

You can verify the navigatability by trying a MyRecordSet.MovePrev. If this causes a runtime error you will get no recordcount.

The solution provided by jmcvinney will certainly work fine, but as you already noticed, it will increase the responsetime dramatically.

Usually the location of the cursor doesn't matter. But clientside cursors are a good alternative with relativly small recordsets. The client side cursor will also ensure the flexibility you need, if the driver-provided cursor-engine doesn't support it.

MyAdoConnection.CursorLocation = adUseClient

With huge masses of data, the client-side cursor will become rather slow, as it fetches all the data.

Hope that helps...


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
When you dynamically build the SQL, just hard code the necessary string stuff to include the count()
I don't can't seem to use the connection object's cursortype. The only cursor property is the Cursorlocation. Is that a typo?
This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.  If you are an EE Pro user, you can also choose Power Search to find all your open questions.

This is the Community Support link, if help is needed.  Also a question is posted there specific to these changes that apply to the experts here.  Also, I am including the link to our All Topics, since many new ones have recently been added.
Thank you,
Moderator @ Experts Exchange
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.