Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8

Thread: rs.recordcount

  1. #1
    Regular Coder
    Join Date
    May 2005
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question rs.recordcount

    Hi friends,

    How I do to reduce/change the real value of rs.recordcount in 20% ?

    DIM totalput
    totalput=rs.recordcount
    if rs.recordcount>10 then
    totalput=8
    if rs.recordcount>20 then
    totalput=16

    end if
    end if

    But this not works. Thios not change the real value of rs.recordcount!

    Thank you

    JanLee

  • #2
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,070
    Thanks
    4
    Thanked 8 Times in 8 Posts
    you do realize that if you don't use a static cursor when you open the recordset that the value of rs.recordcount is always -1 don't you? Make sure you are using a static cursor. That is done like this

    rs.open "SELECT * FROM widgets",conn, 3
    or
    rs.open "SELECT * FROM widgets",conn, adOpenStatic

  • #3
    Senior Coder
    Join Date
    Nov 2002
    Location
    North-East, UK
    Posts
    1,265
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Another option is to loop through to count the records

    Code:
    rs_total = rs.RecordCount
    
    If (rs_total = -1) Then
    
      ' count the total records by iterating through the recordset
      rs_total=0
      While (Not rs.EOF)
        rs_total = rs_total + 1
        rs.MoveNext
      Wend
    
      ' reset the cursor to the beginning
      If (rs.CursorType > 0) Then
        rs.MoveFirst
      Else
        rs.Requery
      End If
    
    End If

  • #4
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,070
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Why would you loop through to get the count? That is a very inefficient way of doing it. And if you have result in millions it could cause the server to timeout before the result was displayed.

    You could also use rs.GetRows then just get the upper limit of the array. By using a static cursor or using GetRows, either way is much more efficient than looping through the recordset.


    aRows = rs.GetRows
    totalput = UBound(aRows,2)

  • #5
    Regular Coder
    Join Date
    May 2005
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cool

    Hi, Miranda and Degsy,

    Sorry, but I am busy of work! I had not time to try your scripts!

    The suggestion of Miranda makes sense.

    I find a rude and 'home' solution:

    recordsToShow = 5

    calculo= round(rs.recordcount/rs.pagesize-2)

    For i = 1 To calculo
    rcount = i
    If currentPage > 1 Then
    For x = 1 To (currentPage - 1)
    rcount = 10 + rcount
    Next
    End If

    In this way, instead to show 10 results it show only 6!

    Could I use the following?

    select top 1000 from mytable

    Thank you again

    JanLee

  • #6
    Senior Coder
    Join Date
    Nov 2002
    Location
    North-East, UK
    Posts
    1,265
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Do you want a page to specifically output a certain amount of records or do you want paging?

    The techniques would be different.

    With paging you would grab all of the records and use a counter to tell the script how many to output.

  • #7
    Regular Coder
    Join Date
    May 2005
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Degsy,

    I want a page to specifically output a certain amount of records!

    Now I found other and best way to do this:

    select TOP 20 percent from tableA

    But it is a 'straitjacket'.

    Could I do this: SELECT TOP 20 PERCENT (TOP 1000) FROM TABLEA ????

    or similar thing?

    Thank you for your interest in help me!

    JanLee

  • #8
    Senior Coder
    Join Date
    Nov 2002
    Location
    North-East, UK
    Posts
    1,265
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In Access you can use Percent.

    SELECT TOP 20 Percent FROM table


  •  

    Posting Permissions

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