RecordCount

Discussion in 'ASP General' started by Mark Watkins, Oct 31, 2003.

  1. Mark Watkins

    Mark Watkins Guest

    I know for a fact that in my database, I have three records under the users
    table. However when I execute this code:

    --------------BEGIN CODE----------------

    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open "Driver={Microsoft Access Driver (*.mdb)};
    DBQ=C:\Inetpub\wwwroot\spruce.mdb;"
    strQuery = "SELECT * FROM schedule"
    Set rst = Server.CreateObject("ADODB.recordset")
    rst.Open strQuery, objConn
    Set est = Server.CreateObject("ADODB.recordset")
    est.Open "SELECT * FROM users", objConn
    est.MoveFirst
    Response.Write est.RecordCount

    ----------------------END CODE ------------------------------

    It prints out "-1" instead of "3"

    Any idea why this may be happening? Am I using RecordCount correctly? All
    I wanna do is know the number of records resulting from my query without
    using a silly loop.
     
    Mark Watkins, Oct 31, 2003
    #1
    1. Advertising

  2. Mark Watkins

    Chris Barber Guest

    Default cursor type is 'firehose' forward-only server-side which doesn't
    populate the recordcount property.

    Try doing:

    ..MoveLast
    ..MoveFirst

    to populate the recordcount or consider a client-side cursor (all data gets
    transferred to the client).

    Chris.

    "Mark Watkins" <> wrote in message
    news:...
    I know for a fact that in my database, I have three records under the users
    table. However when I execute this code:

    --------------BEGIN CODE----------------

    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open "Driver={Microsoft Access Driver (*.mdb)};
    DBQ=C:\Inetpub\wwwroot\spruce.mdb;"
    strQuery = "SELECT * FROM schedule"
    Set rst = Server.CreateObject("ADODB.recordset")
    rst.Open strQuery, objConn
    Set est = Server.CreateObject("ADODB.recordset")
    est.Open "SELECT * FROM users", objConn
    est.MoveFirst
    Response.Write est.RecordCount

    ----------------------END CODE ------------------------------

    It prints out "-1" instead of "3"

    Any idea why this may be happening? Am I using RecordCount correctly? All
    I wanna do is know the number of records resulting from my query without
    using a silly loop.
     
    Chris Barber, Oct 31, 2003
    #2
    1. Advertising

  3. Mark Watkins

    dlbjr Guest

    rs.Open strSQL, objConn, adLockReadOnly, adCmdTable
    dblrecordCount = rs.RecordCount

    -dlbjr

    Discerning resolutions for the alms
     
    dlbjr, Oct 31, 2003
    #3
  4. Mark Watkins

    Chris Barber Guest

    Not meaning to offend but Mark isn't really going to be able to learn
    anything from such a short reply that makes no attempt to explain why the
    issue is occurring?
    Brings to mind the adage about 'Give a man a meal and he'll eat for a day
    but give him the knowledge and tools to grow his own and he'll never want
    for food again'?

    Chris.

    "dlbjr" <> wrote in message
    news:epjob.159$Qy4.13102@typhoon01...
    rs.Open strSQL, objConn, adLockReadOnly, adCmdTable
    dblrecordCount = rs.RecordCount

    -dlbjr

    Discerning resolutions for the alms
     
    Chris Barber, Oct 31, 2003
    #4
  5. Mark Watkins

    Bob Barrows Guest

    Chris Barber wrote:
    > Default cursor type is 'firehose' forward-only server-side which
    > doesn't populate the recordcount property.
    >
    > Try doing:
    >
    > .MoveLast
    > .MoveFirst
    > to populate the recordcount


    There are some problems with this advice:
    1. Since it's a forward-only cursor, the MoveFirst method will usually not
    be supported. Some providers, however, will support it, but their method of
    supporting it may not be to your liking: MoveFirst causes the recordset to
    be requeried, which can have a large impact on performance. If the provider
    does not support MoveFirst with forward-only cursors, and error will be
    raised.
    2. Even if the MoveFirst is supported, it will still be a forward-only
    cursor, and RecordCount will still contain -1 after the MoverFirst. This is
    different from the behavior of DAO recordsets.


    >or consider a client-side cursor (all
    > data gets transferred to the client).
    >

    That will definitely work. However, you do not need a client-side cursor to
    get a recordcount: there are several server-side cursor types that will
    support record-count: static, keyset, dynamic, and with the Jet provider,
    Table.

    However, I do not recommend opening one of the non-default cursor types
    merely to get a record count. The non-default cursor types require more
    resources and do not perform as well as the default due to the extra
    functionality offered. There are other ways to get a record count from a
    default forward-only cursor. My favorite is to use GetRows to stuff the data
    from the recordset into an array. This has two benefits:
    1. I can immediately close the recordset and connection, allowing other
    threads on the server to use the connection instead of creating a new one.
    2. I can work with the data in the array, which will be much quicker than
    using a cursor to work with it.

    Once the data is in the array, you can use Ubound to determine the number of
    records:

    est.Open ...
    if not est.EOF then arResults = est.GetRows
    est.close: set est = nothing
    objConn.close:set objConn=nothing
    if isArray(arResults) then
    response.write Ubound(arResults,2) & " records"
    else
    response.write "no records"
    end if

    HTH,
    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, Oct 31, 2003
    #5
  6. "Bob Barrows" <> wrote in message
    news:O%...

    > Once the data is in the array, you can use Ubound to determine the

    number of
    > records:
    >
    > est.Open ...
    > if not est.EOF then arResults = est.GetRows
    > est.close: set est = nothing
    > objConn.close:set objConn=nothing
    > if isArray(arResults) then
    > response.write Ubound(arResults,2) & " records"
    > else
    > response.write "no records"
    > end if


    Plus 1.

    Haven't we had this conversation already. :)

    http://groups.google.com/groups?selm=#
     
    Chris Hohmann, Oct 31, 2003
    #6
  7. Mark Watkins

    Bob Barrows Guest

    Chris Hohmann wrote:
    > "Bob Barrows" <> wrote in message
    > news:O%...
    >
    >> Once the data is in the array, you can use Ubound to determine the

    > number of
    >> records:
    >>
    >> est.Open ...
    >> if not est.EOF then arResults = est.GetRows
    >> est.close: set est = nothing
    >> objConn.close:set objConn=nothing
    >> if isArray(arResults) then
    >> response.write Ubound(arResults,2) & " records"

    response.write Ubound(arResults,2) + 1 & " records"

    >> else
    >> response.write "no records"
    >> end if

    >
    > Plus 1.
    >
    > Haven't we had this conversation already. :)
    >
    >

    http://groups.google.com/groups?selm=#

    Wow! This is an example of a mistake I would never make in my own code but
    which keeps slipping into my air code examples! Thanks for the re-catch!

    Bob

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, Oct 31, 2003
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Steve Caliendo

    RecordCount of a datareader

    Steve Caliendo, Jun 2, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    1,417
    John Oakes
    Jun 2, 2004
  2. Daan
    Replies:
    4
    Views:
    15,614
    chris22smith
    Mar 18, 2009
  3. Replies:
    2
    Views:
    2,862
    Peter Rilling
    Oct 13, 2005
  4. =?Utf-8?B?UGF1bA==?=

    DataReader and RecordCount

    =?Utf-8?B?UGF1bA==?=, Oct 21, 2005, in forum: ASP .Net
    Replies:
    9
    Views:
    38,052
    Patrick.O.Ige
    Oct 22, 2005
  5. =?Utf-8?B?YmVybmFkb3U=?=

    datasource.recordcount? How to obtain this?

    =?Utf-8?B?YmVybmFkb3U=?=, Feb 9, 2006, in forum: ASP .Net
    Replies:
    7
    Views:
    3,522
    =?Utf-8?B?UGhpbGxpcCBXaWxsaWFtcw==?=
    Feb 9, 2006
Loading...

Share This Page