Thread: Classic paging

    #1
  1. Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2006
    Location
    Ringgold, GA
    Posts
    69
    Rep Power
    10

    Classic paging


    Greets,

    I have a script that does some pagination links. I use it where ever i need it, but the way it works is that it loops through the entire table and outputs only the records that meet a criteria. This as we know it can get very labor intensive if the table has a large amount of rows.

    I've started a new script that uses something like below for the SQL statement;

    Code:
    Select Top 15 MediaTitle, MediaRating, MediaType, MediaGenre, MediaOwner, ID From MediaCatalog Where ID >= 1 Order By ID asc
    Which works GREAT and anywhere in the table I use this it comes back with an answer quickly, by changing the number 1 in the code above.

    However I have a problem. A lot of my tables have holes in them. What I mean is that in the past I have deleted a record here and there. So that I cannot accurately predict the links on the left and right of the current page in the pagination links. So depending on how big the hole is sometimes I get the same set of records 2 or three times in a row when pressing a "next" button.

    See this example here. This is the old code that works correctly, but is slow with large tables.

    See new here.

    Currently I'm using a primary key "ID" to query.

    Does this make sense? Anyone have a simple idea?
    killgorack.com
  2. #2
  3. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Oct 2012
    Posts
    49
    Rep Power
    3
    ' call your SQL command here and loop through the 15 recordsets using a do/while, for next, etc.
    ' create your html table rows here and output the results
    if flag = 0 then
    firstrec = objrs("id")
    flag = 1
    end if
    ' move to next recordset
    ' end loop here and close table
    nextrec = objrs("id")

    use nextrec +1 as the variable for the pagination value moving FORWARD.

    For example if this page is being viewed:

    KillGorack A site for nothing really..

    The nextrec value will be 76 because Kung Fu Panda's ID is 75.

    You *could* place the
    nextrec = objrs("id")
    inside your loop but we really only need the LAST record ID so I placed it outside the loop, just be sure to call it before closing your recordset.

    Then...

    <a href="alltable2.asp?application=Mediacatalog&jump= <%=nextrec+1%>">FORWARD</a>

    Nowwww to go backwards...

    <a href="alltable2.asp?application=Mediacatalog&jump= <%=firstrec-1%>&dir=rev">PREVIOUS</a>

    This will set the ID to the value of your first table row, minus one.... and to take care of the ordering and gaps try this code where you make your SQL query.

    string1 = "Select Top 15 MediaTitle, MediaRating, MediaType, MediaGenre, MediaOwner, ID From MediaCatalog Where ID >= value Order By ID asc"

    dir = request.querystring("dir")
    if dir = "rev" then
    ' reverse direction so we want to change our SQL command to order the other way now
    string1 = "Select Top 15 MediaTitle, MediaRating, MediaType, MediaGenre, MediaOwner, ID From MediaCatalog Where ID <=" & FIRSTREC & "Order By ID desc"
    end if


    I think that should work. The code isn't tested but basically you insert another querystring into the PREVIOUS link which sets the value of "dir" (direction) to "rev" (reverse) and upon that value being set, change the SQL string entirely to accomodate a backward query.
  4. #3
  5. Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2006
    Location
    Ringgold, GA
    Posts
    69
    Rep Power
    10
    Thanks for the reply!,

    The first button works simply by making jump=1 which works with no issues

    The previous button, as you describe here would that not reverse the order of the data when going backwards?
    I guess I could roll through the data backwards and output that way starting at the last record and going to the first. (will try this next)

    The pagination links like here I need to figure out yet...

    The next button works exactly as you describe.
    The last button need to figure this out.

    I'm trying to make it as simple as I can. I start with

    Code:
          
          '=====================================================
          ' Getting Ready for Paging..
          '=====================================================
              If Request.QueryString("Jump") <> "" Then
                Jump = Request.QueryString("Jump")
              Else
                Jump = 1
              End If
          '=====================================================
          
                
          '=====================================================
          ' Assembling the SQL and cracking it open..
          '=====================================================
            SQL = "Select Top " & AppShowRows & " " & SelectedFields & " From " & applicationname & " Where ID >= " & Jump & " " & SortString
            response.write sql
            Rs.Open SQL, Conn, 3, 3
            Records = Rs.RecordCount
          '=====================================================
    Then just loop through the selected rows.

    The end goal of course is to get the exact function of here while using the top functionality in the SQL..
    Last edited by KillGorack; December 23rd, 2012 at 12:45 PM.
  6. #4
  7. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,594
    Rep Power
    278
    this is what you need
    Code:
    dim PageNumber, PageSize
    'PageNumber=2 ' Starts from 1
    ' in your case 
     If Request.QueryString("Jump") <> "" Then
                PageNumber= Request.QueryString("Jump")
              Else
                PageNumber= 1
              End If
    PageSize=15
    
    SQL = "Select Top (" & PageSize & ") " * From yourTable Where IDFiled not in (Select Top (" & PageSize * (PageNumber - 1) & ") " * From yourTable order by IDFiled ) order by IDFiled 
     Rs.Open SQL, Conn, 1, 3
    do while not rs.eof
    
    response.write rs("Title") ' or any field
    rs.moveNext
    loop
    I hope it is self explaining code. just select top where not in prev pages and do not forget order by in both selects
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree
  8. #5
  9. Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2006
    Location
    Ringgold, GA
    Posts
    69
    Rep Power
    10
    That will work exactly what we need ;0), getting the stuff in there now. See the link below it comes with an error, the "Where not in" is new to me. If we get the error resolved this format will give me exactly what I'm looking for

    in the mean tiI'll google the error, thanks in advance!

    http://www.killgorack.com/alltable2....n=MediaCatalog

    In case the sql is different while I edit it.. See it below...

    Code:
    Select Top (15) MediaTitle, MediaRating, MediaGenre, MediaType, MediaOwner, ID From MediaCatalog Where ID not in (Select Top (0) MediaTitle, MediaRating, MediaGenre, MediaType, MediaOwner, ID From MediaCatalog Order By ID asc ) Order By ID asc
    Last edited by KillGorack; December 28th, 2012 at 12:36 PM.
  10. #6
  11. Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2006
    Location
    Ringgold, GA
    Posts
    69
    Rep Power
    10
    Got it. Was calling too many fields when all I needed was the ID field. Working great, flushing it out now. Thanks for this, I'm resolved


    See it working here
    KillGorack A site for nothing really..
    Last edited by KillGorack; December 28th, 2012 at 04:37 PM.

Similar Threads

  1. Classic ASP Ajax Recordset Paging
    By sharmajv in forum ASP Development
    Replies: 7
    Last Post: September 25th, 2012, 02:46 PM
  2. Classic ASP / XML
    By badmp3 in forum ASP Development
    Replies: 0
    Last Post: June 22nd, 2008, 08:20 PM
  3. Classic asp to asp.net
    By catbollu in forum ASP Development
    Replies: 1
    Last Post: January 30th, 2008, 01:15 AM
  4. Help Me In Paging Table Database. help me to paging my table
    By mcsehustler in forum ASP Development
    Replies: 3
    Last Post: January 2nd, 2006, 11:48 PM
  5. help with asp paging lossing passed value in paging
    By ayecona in forum ASP Development
    Replies: 2
    Last Post: March 17th, 2004, 03:16 PM

IMN logo majestic logo threadwatch logo seochat tools logo