• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

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 ASP101.com, but they seem to only have answers for MS SQLServer or Access.

Thank you.
1 Solution
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.
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now