SQL Server Performance Forum – Threads Archive

How to determine rowcount of recordset

Hi there, I am running a ASP/IIS website with SQL2000 as database.
Is it possible to determine how many rows a recordset has? For example: SET rs = conn.Execute ("SELECT names FROM customers WHERE age<30") count = rs.RecordCount Response.Write "We have " & count & " customers younger than 30 :"
While Not rs.Eof
<…>
rs.MoveNext
Wend Obviously the `count = rs.RecordCount` doesn’t work in this example. What should I do to get it working? I want to use this information to make a progressbox: it needs to know how many records will be processed before the while/wend loop starts. Thanks,
Ray

---

>>Obviously the `count = rs.RecordCount` doesn’t work in this example. What does it give?
You need to declare the cursorLocation as ClientSide set rs.CursorLocation=AdUseClient
Then write your queries Otherwise immediately after running Execute command, run Select @@RowCount and assign that to other recordset Madhivanan Failing to plan is Planning to fail

---

Hi,
As you need just the recordcount you can even try changing your query and will be more guaranteed to give correct count.
SET rs = conn.Execute ("SELECT count(names) FROM customers WHERE age<30")
if not rs.eof then Response.Write "We have " & rs(0) & " customers younger than 30 :"

---

rs.RecordCount will only work with the following ADO cursor types:
Clientside
Serverside – Static
Serverside – Keyset When doing a conn.Execute you get none of those, you get a Forward-only cursor. For more info about ADO cursors see:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=5997

---

]]>