Previous/next page buttons not displaying correct number of records 1

[pandapark]

Hi

I've used the code below to naviagte through a Database using previous and next buttons. works fine except only one record per page is displayed. I'd like it to show the number I set in the interval per page i.e. 10 in this example

any help much appreciated
Ian

Set objCon = Server.CreateObject("ADODB.Connection")

objCon.CursorLocation = 3
objCon.ConnectionString = "Driver=SQL Server;Server=NAPLES;Database=FOI;User ID=aspgen;Password=genasp"
objCon.Open

Set objRS = Server.CreateObject("ADODB.RecordSet")

strSQL = "SELECT * FROM tblPublications_new"
set objRS=ObjCon.execute(strSQL)

'setting the number of records to display on each page
Interval = 10

'requesting the Starting Point from the page calling its self
'the querystring will be empty the first time the page is called
StartingPoint = request.queryString("StartingPoint")

'typecast the string to an integer
StartingPoint = CInt(StartingPoint)

'check to see if the querystring was empty
if StartingPoint = "" then
StartingPoint = 1
end if

'loop through the recordset to find the starting point
if StartingPoint > 1 then

for count=1 to StartingPoint

if objRS.EOF then
Exit For
else

objRS.movenext
end if

next
end if



'loop through the recordset from the startingpoint to the interval
for count=1 to Interval

if objRS.EOF then
exit for
else


b = "<table>"
b = b & "<tr>"
b = b & "<td>" & objRS("ID") & "</td>"
b = b & "<td>" & objRS("Title") & "</td>"
b = b & "</tr>"
b = b & "</table>"

objRS.movenext

end if

next

'displaying the previous page link when needed
if StartingPoint > 0 then 'checks to see if it needs to be displayed
b = b & " <font face='arial'><a href='main.asp?page=536&StartingPoint=" & StartingPoint-Interval & "'>Previous Page</a></font>"
end if

b = b & "&nbsp;&nbsp;"


'displaying the links for the pages
if objRS.RecordCount Mod Interval = 0 then

'displays pages if there is an evenly divisible number of records per page
'eg. if there are 50 records and 10 per page, 5 pages
for I=0 to (objRS.RecordCount/Interval)-1

if Interval*I = StartingPoint then

'displays the current page link as red
b = b & " <font face='arial' color='red'><b><u>" & I+1 & "</u></b>&nbsp</font>"

else

'displays the page links
b = b & " <font face='arial'><a href='main.asp?page=536&StartingPoint=" & Interval*I & "'>" & I+1 & "</a>&nbsp;</font>"

end if

next

else

'displays pages if there is not an evenly divisible number of records per page
'eg. if there are 52 records and 10 per page, 6 pages
for I=0 to (objRS.RecordCount/Interval)

if Interval*I = StartingPoint then

'displays the current page link as red
b = b & " <font face='arial' color='red'><b><u>" & I+1 & "</u></b>&nbsp</font>"

else

'displays the page links
b = b & " <font face='arial'><a href='main.asp?page=536&StartingPoint=" & Interval*I & "'>" & I+1 & "</a>&nbsp;</font>"

end if

next

end if

b = b & "&nbsp;"


'Displays the link for the Next Page if there is a next page to come
if StartingPoint+Interval < objRS.Recordcount then

b = b & " <font face='arial'><a href='main.asp?page=536&StartingPoint=" & StartingPoint+Interval & "'>Next Page</a></font>"

end if

objCon.Close
Set objCon = Nothing

objBODY = b

 

[vbkris]

try this:
response.write (objRS.RecordCount/Interval)-1

what does it print???

Known is handfull, Unknown is worldfull

 

[pandapark]

Hi kris

on startup it shows

14.114.114.114.114.114.114.114.114.114.1

this always shows on next page until you get to the last record/page when it only shows

14.1

 

[vbkris]

try this:
response.write (objRS.RecordCount/Interval)-1 &"<Br>"

i want to know what comes in one output...

Known is handfull, Unknown is worldfull

 

[pandapark]

Hi Kris

If I put the response.write before objRS.movenext it shows

14.1
14.1
14.1
14.1
14.1
14.1
14.1
14.1
14.1
14.1

until the last page when it shows just
14.1

if i put response.write outside of ObjRS.movenext and Next it always just shows

14.1

 

[vbkris]

try this code:

sql = "SQL HERE"
rs.cursortype=3
rs.cursorLocation=3
rs.pagesize=10
rs.open sql,con
dim currentpage,rowcount
currentpage=trim(request.querystring("currentpage"))
if currentpage="" then
	currentpage=1
end if 
rs.absolutepage=cInt(currentpage)
for i=1 to rs.pagesize
 if not(rs.eof) then

  rs.movenext
 end if
next 
for i=1 to rs.pagecount 
 if i=cint(currentpage) then
   href=""
 else
 href="href='THASPFILE.asp?currentpage="&i&"'"
 end if
%>
                <a <%=href%>><%=i%></a> 
<%
		next
%>

Does this work???

Known is handfull, Unknown is worldfull

 

[pandapark]

Hi Kris

Yes it does.............except I've made some clanger somewhere because when the page first loads it misses the first 10 records (but does display 10 per page) and navigates through ok and the href
href="href='main.asp?page=536&currentpage="&i&"'"
shows 16 which when clicked doesn't show anything
I know I've made a mistake comewhere
Thanks
here it is in full now


Set objCon = Server.CreateObject("ADODB.Connection")

objCon.CursorLocation = 3
objCon.ConnectionString = "Driver=SQL Server;Server=NAPLES;Database=FOI;User ID=aspgen;Password=genasp"
objCon.Open

Set objRS = Server.CreateObject("ADODB.RecordSet")
strSQL = "SELECT * FROM tblPublications_new"

objRS.cursortype=3
objRS.cursorLocation=3
objRS.pagesize=10
set objRS=ObjCon.execute(strSQL)

dim currentpage,rowcount
currentpage=trim(request.querystring("currentpage"))
if currentpage="" then
currentpage=1
end if
objRS.absolutepage=cInt(currentpage)
for i=1 to objRS.pagesize
if not(objRS.eof) then

objRS.movenext
end if
next
for i=1 to objRS.pagecount
if i=cint(currentpage) then
href=""
else
href="href='main.asp?page=536&currentpage="&i&"'"
end if
b = "<a " & href & ">" & i & "</a>"
next


'setting the number of records to display on each page
Interval = 10

'requesting the Starting Point from the page calling its self
'the querystring will be empty the first time the page is called
StartingPoint = request.queryString("StartingPoint")

'typecast the string to an integer
StartingPoint = CInt(StartingPoint)

'check to see if the querystring was empty
if StartingPoint = "" then
StartingPoint = 1
end if

'loop through the recordset to find the starting point
if StartingPoint > 1 then

for count=1 to StartingPoint

if objRS.EOF then
Exit For
else

objRS.movenext
end if

next
end if



'loop through the recordset from the startingpoint to the interval
for count=1 to Interval


if objRS.EOF then
exit for
else

b = b & "<table>"
b = b & "<tr>"
b = b & "<td>" & objRS("ID") & "</td>"
b = b & "<td>" & objRS("Title") & "</td>"
b = b & "</tr>"
b = b & "</table>"

objRS.movenext

end if

next


'displaying the previous page link when needed
if StartingPoint > 0 then 'checks to see if it needs to be displayed
b = b & " <font face='arial'><a href='main.asp?page=536&StartingPoint=" & StartingPoint-Interval & "'>Previous Page</a></font>"
end if

b = b & "&nbsp;&nbsp;"


'displaying the links for the pages
if objRS.RecordCount Mod Interval = 0 then

'displays pages if there is an evenly divisible number of records per page
'eg. if there are 50 records and 10 per page, 5 pages
for I=0 to (objRS.RecordCount/Interval)-1

if Interval*I = StartingPoint then

'displays the current page link as red
b = b & " <font face='arial' color='red'><b><u>" & I+1 & "</u></b>&nbsp</font>"

else

'displays the page links
b = b & " <font face='arial'><a href='main.asp?page=536&StartingPoint=" & Interval*I & "'>" & I+1 & "</a>&nbsp;</font>"

end if

next

else

'displays pages if there is not an evenly divisible number of records per page
'eg. if there are 52 records and 10 per page, 6 pages
for I=0 to (objRS.RecordCount/Interval)

if Interval*I = StartingPoint then

'displays the current page link as red
b = b & " <font face='arial' color='red'><b><u>" & I+1 & "</u></b>&nbsp</font>"

else

'displays the page links
b = b & " <font face='arial'><a href='main.asp?page=536&StartingPoint=" & Interval*I & "'>" & I+1 & "</a>&nbsp;</font>"

end if

next

end if

b = b & "&nbsp;"


'Displays the link for the Next Page if there is a next page to come
if StartingPoint+Interval < objRS.Recordcount then

b = b & " <font face='arial'><a href='main.asp?page=536&StartingPoint=" & StartingPoint+Interval & "'>Next Page</a></font>"

end if

objCon.Close
Set objCon = Nothing

objBODY = b

 

[vbkris]

this code will do,
remove all ut other code,

 Set objCon = Server.CreateObject("ADODB.Connection")

    objCon.CursorLocation = 3
    objCon.ConnectionString = "Driver=SQL Server;Server=NAPLES;Database=FOI;User ID=aspgen;Password=genasp"
    objCon.Open

    Set objRS = Server.CreateObject("ADODB.RecordSet")
    strSQL = "SELECT * FROM tblPublications_new"

    objRS.cursortype=3
    objRS.cursorLocation=3
    objRS.pagesize=10
    set objRS=ObjCon.execute(strSQL)

    dim currentpage,rowcount
    currentpage=trim(request.querystring("currentpage"))
    if currentpage="" then
        currentpage=1
    end if 
    objRS.absolutepage=cInt(currentpage)
    for i=1 to objRS.pagesize
     if not(objRS.eof) then
b = b & "<table>"
b = b & "<tr>"
b = b & "<td>" & objRS("ID") & "</td>"
b = b & "<td>" & objRS("Title") & "</td>"
b = b & "</tr>"
b = b & "</table>"
response.write b
    objRS.movenext
     end if
    next 
    for i=1 to objRS.pagecount 
     if i=cint(currentpage) then
       href=""
     else
     href="href='main.asp?page=536&currentpage="&i&"'"
     end if
    b = "<a " & href & ">" & i & "</a>"
        next

Known is handfull, Unknown is worldfull

 

[pandapark]

thanks kris

I've used the original code now plus some of your's but I've set the objRS.pagesize = 10 and it works a treat
thanks again

 

[vbkris]

no problemmo...

Known is handfull, Unknown is worldfull