Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to get an ADO recordcount?

Posted on 1999-07-22
13
Medium Priority
?
427 Views
Last Modified: 2013-12-25
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
Comment
Question by:mfarid1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 

Expert Comment

by:ebelusa
ID: 1506939
Try doing an rs.MoveLast before getting a recordcount. [with rs.RecordCount]
0
 

Author Comment

by:mfarid1
ID: 1506940
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
 
LVL 5

Expert Comment

by:mayhew
ID: 1506941
Can't you define the cursor type yourself?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:mfarid1
ID: 1506942
Yes, I can but it is reset by the provider. Thus if I set CursorType to adUseStatic, it is always reset to adOpenForwardOnly
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 1506943
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
 

Author Comment

by:mfarid1
ID: 1506944
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
 

Accepted Solution

by:
rcleetus earned 200 total points
ID: 1506945
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
 

Expert Comment

by:jpforti
ID: 1506946
I think it is faster to do a "select count(*) from my table where ..." query !!!

0
 

Author Comment

by:mfarid1
ID: 1506947
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 1506948
Set rsAs = rsB.clone
0
 

Author Comment

by:mfarid1
ID: 1506949
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
 

Author Comment

by:mfarid1
ID: 1506950
I am using ADO
My Connection.Provider value is:

Driver={SQL Server};Server=MYSERVER;UID=sa;Pwd=;Database=Northwind
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6822738
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question