SQL Server Performance

How to determine rowcount of recordset

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by raydfk, Sep 14, 2005.

  1. raydfk New Member

    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
  2. Madhivanan Moderator

    >>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
  3. ranjitjain New Member

    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 :"
  4. Argyle New Member

Share This Page