Professional Web Applications Themes

RecordCount - ASP

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 ...

  1. #1

    Default RecordCount

    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 Guest

  2. #2

    Default Re: RecordCount

    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" <com> wrote in message
    news:phx.gbl...
    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 Guest

  3. #3

    Default Re: RecordCount

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

    -dlbjr

    Discerning resolutions for the alms


    dlbjr Guest

  4. #4

    Default Re: RecordCount

    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" <u> wrote in message
    news:epjob.159$..
    rs.Open strSQL, objConn, adLockReadOnly, adCmdTable
    dblrecordCount = rs.RecordCount

    -dlbjr

    Discerning resolutions for the alms



    Chris Guest

  5. #5

    Default Re: RecordCount

    Chris Barber wrote: 

    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.

     
    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 Guest

  6. #6

    Default Re: RecordCount

    "Bob Barrows" <SPAMcom> wrote in message
    news:O%phx.gbl...
     
    number of 

    Plus 1.

    Haven't we had this conversation already. :)

    http://groups.google.com/groups?selm=%231IXms1NDHA.2208%40TK2MSFTNGP12.phx. gbl


    Chris Guest

Similar Threads

  1. RecordCount Issues
    By supportcenter in forum Coldfusion Database Access
    Replies: 6
    Last Post: November 9th, 01:05 PM
  2. Dynamic RecordCount
    By H3ath0r in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: June 13th, 08:43 PM
  3. Getting a recordcount
    By Andy Levy in forum PHP Development
    Replies: 3
    Last Post: October 29th, 08:55 AM
  4. recordcount -1
    By middletree in forum ASP
    Replies: 8
    Last Post: October 16th, 02:06 PM
  5. RecordCount Property and SQL
    By Scott in forum ASP Database
    Replies: 2
    Last Post: July 22nd, 01:17 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139