How to break up query results

[Mdiaz]

Do any of you folks have any snippets of how to break up a query results onto several pages. You know, like only showing ten results and then providing a link at that allows the user to page through more pages.

Thanks...

 

[bastienk]

simple to implement using limits on the server resultset

paging snippet
'-----------------------------------------------------------------------------
' Check to see what paging operation, if any, was requested
'-----------------------------------------------------------------------------     
  Select Case Trim(Request("querySub"))
    Case "Next"
'-----------------------------------------------------------------------------
'                  NEXT BLOCK
'-----------------------------------------------------------------------------
      NextBlock = Request("NextBlock") + Display_Len
      
    Case "Prev"
'-----------------------------------------------------------------------------
'                  PREVIOUS  BLOCK
'-----------------------------------------------------------------------------
      NextBlock = Request("NextBlock") - Display_Len 
      If NextBlock < 0 Then
        NextBlock = 0
      End If

'-----------------------------------------------------------------------------
'                  LAST  BLOCK
'-----------------------------------------------------------------------------
    Case "Last"
      NextBlock = Int(Matches / Display_Len) * Display_Len

      if Matches mod Display_Len = 0 then 
        NextBlock = NextBlock - display_len
      end if
      
'-----------------------------------------------------------------------------
'                 FIRST BLOCK
'-----------------------------------------------------------------------------
    Case Else
        NextBlock = 0 
 
  End Select 
  
  if NextBlock < 0 then NextBlock = 0

'change parameters based on DB type
  If SQL_Type = "MySQL" Then 
    rsInvestigation.CursorLocation = adUseClient
    sql_orderby = sql_orderby & " Limit " & NextBlock & ", " & Display_Len & ";"
  elseif SQL_Type = "SQL" then
    rsInvestigation.CursorLocation = adUseClient
    rsInvestigation.CacheSize = Display_Len
    sql_orderby = sql_orderby & ";"
  End If

form and buttons snippet

<input type=hidden name="NextBlock" value="<%=NextBlock%>"> 
     <input type=hidden name="Matches" value="<%=Matches%>">
     <input type=hidden name="sql_where" value="<%=sql_where%>">
<table width=100% align=center border=0 cellpadding=0 cellspacing=0>
                <tr>
                   <% 
                  if NextBlock <= 0 then prev_status = " disabled"
                  if NextBlock + Display_Len >= matches then next_status = " disabled" 
                %>
                <input type=submit value="First" name="querySub"<% = prev_status %>>
                <input type=submit value="Prev" name="querySub"<% = prev_status %>>
                <input type=submit value="Next" name="querySub"<% = next_status %>>
                <input type=submit value="Last" name="querySub"<% = next_status %>></td>
                </tr>
              </table>

I pass the where portion of the sql back to the server to avoid having to remember the elements to build the where clause...the hidden fields pass back the values to the db to avoid more calls to get totals etc...

hth