Classic paging

[KillGorack]

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;

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?

[sqlconfused]

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

[KillGorack]

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

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

[gk53]

this is what you need

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

[KillGorack]

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

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

[KillGorack]

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