Recordset Question

[Nervotrep]

Hello!

rs.Open "SELECT * FROM Customers", cn, adOpenKeyset, adLockOptimistic

The recordset opens just fine and allows me to .Addnew
Also, rs.EOF is false

So why does rs.Recordcount return a -1 when that recordset actually contains records?

Could it be some permissions problem with my SQL server SA account?

Thanks in advance!

[si_the_geek]

The 'default' is -1, which means the recordcount is unknown at the moment.

There are ways to get it to work, but that slows things down a bit, so it is best to avoid it unless you really need it (generally using EOF does the job just as well).

[disruptivehair]

The 'default' is -1, which means the recordcount is unknown at the moment.

There are ways to get it to work, but that slows things down a bit, so it is best to avoid it unless you really need it (generally using EOF does the job just as well).

In my experiments I haven't found EOF to be any faster but my ADO code is probably not that great anyway.

To the original poster:

As I've learned from other people here if you set the recordset's cursor location to adUseClient, you can then access the recordcount property. I haven't found it to be any faster or slower than any other method but your mileage may vary.

HTH

[si_the_geek]

In my experiments I haven't found EOF to be any faster but my ADO code is probably not that great anyway.

It tends to only be obvious if you are dealing with lots of data, or with certain options (such as ForwardOnly, which makes filling lists etc very quick), but I prefer to 'push' the proper methods anyway, to save future problems.

[disruptivehair]

It tends to only be obvious if you are dealing with lots of data, or with certain options (such as ForwardOnly, which makes filling lists etc very quick), but I prefer to 'push' the proper methods anyway, to save future problems.

I don't know what the proper methods are...I tend to just go with whatever works the first time. Still learning! I don't even have any books or references on ADO.

[si_the_geek]

I don't have any computer related books (except for one about a dodgy printer language I needed for one project), I generally just use the help files and pages at MSDN, along with my experience and common sense (this one is sometimes lacking!).

If you want us to check/improve your code, feel free to post it in an appropriate forum (perhaps "Code It Better").

[disruptivehair]

I don't have any computer related books (except for one about a dodgy printer language I needed for one project), I generally just use the help files and pages at MSDN, along with my experience and common sense (this one is sometimes lacking!).

If you want us to check/improve your code, feel free to post it in an appropriate forum (perhaps "Code It Better").

Good idea; I'd love advice/tips so I can improve. I'll post some today.

[szlamany]

This RECORDCOUNT issue comes up so often...

As SI already said, -1 means there are records, but since they have not all been given to the client from the server, the actual count is not known.

I always ask "why do you need to know the number of records?".

Old ACCESS style programming would have probably bound the entire RECORDSET to the client - and in those systems you would move back and forth through a recordset.

We like to only use FORWARDONLY, READONLY recordsets - and load them entirely into a control (flex grid, drop down, print to the printer object). Then we destroy the RECORDSET and work the data in the client and do updates and such with ACTION QUERIES. With that said we never need to know a recordcount up front - we simply load rows by rows until EOF...

[leinad31]

Hello!

rs.Open "SELECT * FROM Customers", cn, adOpenKeyset, adLockOptimistic

The recordset opens just fine and allows me to .Addnew
Also, rs.EOF is false

So why does rs.Recordcount return a -1 when that recordset actually contains records?

Could it be some permissions problem with my SQL server SA account?

Thanks in advance!

You loading the entire table just to add a record? If you already know how to use SELECT queries then its time you also learned to use INSERT, UPDATE and DELETE queries.