RecordCount Property and SQL

[Scott]

When using an access database, I could retrieve the recordcount with code
below

For r = 1 to RS.RecordCount

When I try the same syntax on a SQL database, the RecordCount always
returns -1


Is there another quick way to get the Record Count with SQL so I could run a
similiar statement?

[Aaron Bertrand [MVP]]

[url]http://www.aspfaq.com/2193[/url]

--
Aaron Bertrand, SQL Server MVP
[url]http://www.aspfaq.com/[/url]

Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.


"Scott" <salexbailey@msn.com> wrote in message
news:eRxx3v9TDHA.2092@TK2MSFTNGP10.phx.gbl...

> When using an access database, I could retrieve the recordcount with code
> below
>
> For r = 1 to RS.RecordCount
>
> When I try the same syntax on a SQL database, the RecordCount always
> returns -1
>
>
> Is there another quick way to get the Record Count with SQL so I could run

a

> similiar statement?
>
>

[Bob Barrows]

Scott wrote:

> When using an access database, I could retrieve the recordcount with
> code below
>
> For r = 1 to RS.RecordCount
>
> When I try the same syntax on a SQL database, the RecordCount always
> returns -1
>
>
> Is there another quick way to get the Record Count with SQL so I
> could run a similiar statement?

In addition to Aaron's article, you should be aware that using a client-side
cursor will be more expensive in terms of resources and performance than the
default server-side forward-only cursor. You should consider using GetRows
to stuff your data into an array so you can forego the recordset loop and do
a much faster array loop instead. This has the added benefit of allowing you
to immediately close your recordset and connection, allowing the connection
to be used by another user which can also boost your overall performance and
scalability.

Bob Barrows