cursor / recordcount errors

[th3maw]

I am trying to use CursorLocation and recordcount to do some basic record paging.

Despite using code I have used numerous times elsewhere I am unable to get the expected results using CursorLocation and recordCount always returns a value of -1.

I have used this basic example from codefixer (in addition to numerous others) and still get -1

http://www.codefixer.com/tutorials/recordcount.asp

I have tried various tests but my code always falls over either when setting the cursor location (Object or provider is not capable of performing requested operation.) or if it goes beyond that I just get -1 returned despite there being many more records in the table(s) which are displaying correctly when I loop throught the recordset.

I have now got around this problem the slow way (looping through the records once and manually increasing my recordcount, then reconnecting to actually display the data), however I am concerned that this problem will continue to annoy me for future projects and frankly I just want to know why code I have used thousands of times wont work here.

I have check the ADO version and it is above the minimum required. The only real difference in the setup here in that we are using Sybase (which I have never worked with previously), however I cant see how that would cause this problem.

If anyone has any suggestions let me know.

Thanks

maw

 

[FesterSXS]

can you post the exact code you are trying please.

I've looked at the code at the link and there is no CursorLocation mentioned

Tony

Spirax-Sarco - steam traps control valves heat exchangers
Sun Villa - Luxury Florida accommodation

 

[th3maw]

As I said in the title and in my post I am having problems with both cursor location and recordcount (and I believe these problems are linked).

While the example I linked to does not have use .cursorlocation I believe the line -

Recordset.Open sSQL,connection,3,3

- refers to the cursor and locktype.

I have tried various examples and many use .cursorLocation so I mentioned it in my post, however I think it is a general cursor error, apologies if my post was misleading, however I thought that would be a good example to link to as it specifically states it is the perfect way to resolve the -1 recordcount error.

I think I have now managed to resolve it by using server side cursorlocation rather than client (although I am not too sure of the drawbacks of doign this) with this example.

http://www.asp101.com/articles/recordsetpaging/index.asp

If set the cursor location to 3 (adUseClient) with this example I get-

"Object or provider is not capable of performing requested operation."

This error only occurs if I include the line

objRst.CursorLocation = 3

If I comment out this line or set it to adUseServer it now seems to be working.

Still not 100% sure why this is happening, but at least I have something to search on now.

Thanks

maw

 

[Sheco]

You will get a -1 if the recordset does not support the .RecordCount method.

Forward-Only recordsets don't support .RecordCount.

If memory serves, static forward-only is the default for an ADO recordset.

 

[th3maw]

Thanks Sheco, I realise the problem is with .RecordCount not being supported, however I am just confused as to why.

As I stated in my original post this is soemthing I have set up many, many times and have never run into this problem before.

So I was hoping someone might now what might cause adUseClient to not work.

Anyway I have got the actual code working now using the default cursorLocation, I just like to understand what causes these kinds of problems in case I run into them (or something else that might be linked) again.

Thanks

maw

 

[Sheco]

Maybe it is not exactly an ADO problem but rather with the Sybase driver?

In any case, perhaps a plain SQL solution using COUNT and a subquery would be faster than looping through the recordset:
[tt]
SELECT
COUNT(sub.Field1) As RecCount,
sub.Field1,
sub.Field2,
sub.Field3
FROM
(
SELECT
a.Foo As Field1
a.Bar As Field2
b.Lop As Field3
FROM MyTable1 a
INNER JOIN MyTable2 b ON a.KeyField = b.ForiegnKey
AND a.Gaq = 'Wally'
) sub
[/tt]