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

How to get an ADO recordcount?

I have an ADO recordset whose CursorType gets reset to adOpenForwardOnly once I get the recordset back. I need to get a recordcount of the recordset depending on which I ReDim an array on the VB side. How do I get a recordcount? I have been after this question for a long time and nobody could give me an answer.
0
mfarid1
Asked:
mfarid1
1 Solution
 
ebelusaCommented:
Try doing an rs.MoveLast before getting a recordcount. [with rs.RecordCount]
0
 
mfarid1Author Commented:
I have tried rs.MoveLast before and there is an error message saying 'Can't fetch by stepping backwards' or something like that. This is happens because my provider changes the CursorType to adOpenForwardOnly.
0
 
mayhewCommented:
Can't you define the cursor type yourself?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
mfarid1Author Commented:
Yes, I can but it is reset by the provider. Thus if I set CursorType to adUseStatic, it is always reset to adOpenForwardOnly
0
 
Éric MoreauSenior .Net ConsultantCommented:
Using a forward only cursor, you can't get a valid recordcount until you reach the end of you recordset. You can't use .MoveLast and .MoveFirst since you have a FORWARD only and you can't go backward (as you already experiment).

You have a couple of solutions:
1. Use a static cursor (instead of Forward only) with which you will be able to movelast and then first.
2. Try a client side cursor (CursorLocation property).
3. Use a query that count (select count(*) from table where ...) to get your recordcount before your actual query. This will often be faster then moving last and then first if you have a huge recordset.
0
 
mfarid1Author Commented:
emoreau, thank you for trying to help me but I already tried your first two solutions. Both failed. That's because no matter what CursorType and CursorLocation I set, my SQL Server provider resets it to adOpenForwardOnly and thus a recordcount of -1.

I am already aware of your 3rd solution but that's not what I want.
0
 
rcleetusCommented:
Perhaps you can clone the recordset and perform a MoveLast method on the clone, thereby getting the RecordCount. You can then safely destroy the clone.

Not the best solution, but if you're provider is automatically setting your cursor type to ForwardOnly, then that's the only way. Of course, the first thing you should do is try to figure out why the Provider is doing that, and whether it is necessary! Seems strange for a provider to dictate the type of cursor you will use.

Also check to make sure you're using ADO 2.1. Versions before that were buggier..

Hope this helps.
0
 
jpfortiCommented:
I think it is faster to do a "select count(*) from my table where ..." query !!!

0
 
mfarid1Author Commented:
How do you do a clone? If you mean another recordset and do a
Set rsAs = rsB
then this does not work because they are pointing to the same recordset.
0
 
Éric MoreauSenior .Net ConsultantCommented:
Set rsAs = rsB.clone
0
 
mfarid1Author Commented:
Unfortunately, Set rsA = rsB.Clone does not work for me because it says this method is not supported by my provider. The provider I am using is MSDASQL. Is there any other provider that I can use? I tried using SQLOLEDB.1 but it returned saying my user id, which is 'sa', is invalid.
0
 
mfarid1Author Commented:
I am using ADO
My Connection.Provider value is:

Driver={SQL Server};Server=MYSERVER;UID=sa;Pwd=;Database=Northwind
0
 
MoondancerCommented:
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.

This is the Community Support link, if help is needed, along with the link to All Topics since many new ones were recently added.

http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
http://www.experts-exchange.com/jsp/zonesAll.jsp
 
Thanks,
Moondancer
Moderator @ Experts Exchange
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

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