Expert: Srini Nagarajan - 10/31/2011

Question
hey...i dont know how to do pagination in asp...help me out..i have a page having around 1000 rows of data...i want to show 100 on 1st page and 100 on next and so on..how can i do that..

Thanks in advance
Lisha

Answer
Hi,

Here is the code



<!--#include file="db.asp"-->

<%
displayRecs = 50
recRange = 10
MyColNum = 5 'How many Columns do you want or how many records per row do you want?


' Check for a START parameter
If Request.QueryString("start").Count > 0 Then
startRec = Request.QueryString("start")
Session("ads_REC") = startRec
Else
startRec = Session("ads_REC")
if not isnumeric(startRec) or startRec = "" then
'reset start record counter
startRec = 1
Session("ads_REC") = startRec
End If
End If

' Open Connection to the database
set conn = Server.CreateObject("ADODB.Connection")
conn.Open xDb_Conn_Str

' Build Query
strsql = "select * from [ads]"

If dbwhere <> "" Then
strsql = strsql & " WHERE " & dbwhere
End If

if OrderBy <> "" then
strsql = strsql & " ORDER BY [" & OrderBy & "] " & Session("ads_OT")
end if

'response.write strsql

set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strsql, conn, 1, 2
totalRecs = rs.RecordCount
%>





<table border="0"><tr>


<%
'Avoid starting record > total records
if clng(startRec) > clng(totalRecs) then
startRec = totalRecs
end if
'Set the last record to display
stopRec = startRec + displayRecs - 1

'Move to first record directly for performance reason
recCount = startRec - 1
if not rs.eof then
rs.movefirst
rs.move startRec - 1
end if

recActual = 0
Do While (NOT rs.EOF) AND (recCount < stopRec)
recCount = recCount + 1
If Clng(recCount) >= Clng(startRec) Then
recActual = recActual + 1
x=x


%>

<%

x_file = rs("file")

x=x+1
%>

<td width="89">

<font size="-1"><img border="0" src="<% response.write x_file %>">
</font><%


if x= MyColNum then
Response.Write "</td></tr><tr>"
x=0 'resets x to 0 after x has reached 5 and starts over with a new row
end if




end if

rs.MoveNext
Loop
%>


</td></tr></table>



<%
if totalRecs > 0 then

' Find out if there should be Backward or Forward Buttons on the table.
If startRec = 1 Then
isPrev = False
Else
isPrev = True
PrevStart = startRec - displayRecs
If PrevStart < 1 Then PrevStart = 1 %>
<hr size="1">
<strong>[<< Prev]</strong>
<%
End If

' Display Page numbers
If (isPrev OR (NOT rs.EOF)) Then
If (NOT isPrev) Then Response.Write "<HR SIZE=1>"
x = 1
y = 1

dx1 = ((startRec-1)\(displayRecs*recRange))*displayRecs*recRange+1
dy1 = ((startRec-1)\(displayRecs*recRange))*recRange+1
If (dx1+displayRecs*recRange-1) > totalRecs then
dx2 = (totalRecs\displayRecs)*displayRecs+1
dy2 = (totalRecs\displayRecs)+1
Else
dx2 = dx1+displayRecs*recRange-1
dy2 = dy1+recRange-1
End If

While x <= totalrecs
If x >= dx1 and x <= dx2 Then
If Clng(startRec) = Clng(x) Then %>
<strong><font size="-1"><%=y%></font></strong>
<% Else %>
<strong><%=y%></strong>
<% End If
x = x + displayRecs
y = y + 1
elseif x >= (dx1-displayRecs*recRange) and x <= (dx2+displayRecs*recRange) then
if x+recRange*displayRecs < totalRecs then %>
<strong><%=y%>-<%=y+recRange-1%></strong>
<% else
ny=(totalRecs-1)\displayRecs+1
if ny = y then %>
<strong><%=y%></strong>
<% else %>
<strong><%=y%>-<%=ny%></strong>
<% end if
end if
x=x+recRange*displayRecs
y=y+recRange
else
x=x+recRange*displayRecs
y=y+recRange
End If
Wend
End If

' Next link
If NOT rs.EOF Then
NextStart = startRec + displayRecs
isMore = True %>
<strong>[Next >>]</strong>
<% Else
isMore = False
End If %>
<hr size="1">
<% If stopRec > recCount Then stopRec = recCount %>
<font size="-1">Records <%= startRec %> to <%= stopRec %> of <%= totalRecs %></font>

<% Else %>
<br><br>
<font size="-1">No records found!</font>
<br><br>
<% End If %><%
' Close recordset and connection
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing %>