Problem paging through results

I'm creating an ASP site working with a mySQL database. I've used all this code before and it has worked fine. This time I'm having a problem being able to generate next and previous links.

this is my results processing code, connection through an inclusive file which is working okay throughout all the other pages:

<%
Dim rsNamesrch
Dim strNamesrch
Dim namesearchterm
Dim CountName
Dim TotalPages
Dim CurPage
Const NumPerPage = 2

If Request.Querystring("namesearch") = "" THEN
namesearchterm = Request.Form("namesearch")
ELSE
namesearchterm = Request.Querystring("namesearch")
END If

If Request.Querystring("Curpage") = "" THEN
CurPage = 1
ELSE
CurPage = Request.Querystring("Curpage")
END If

Set rsNamesrch = Server.CreateObject("ADODB.Recordset")
strNamesrch = "SELECT * FROM Tablename WHERE tName LIKE '%" & namesearchterm & "%' ORDER BY tName ASC"
rsNamesrch.Open strNamesrch, strCon

rsNamesrch.MoveFirst
rsNamesrch.PageSize = NumPerPage
TotalPages = rsNamesrch.PageCount
rsNamesrch.AbsolutePage = CurPage
CountName = 0
Dim NameDesc


Do While Not rsNamesrch.EOF AND CountName < rsNamesrch.PageSize

response.write ("<table width='400' border='0' cellspacing='0' cellpadding='5' align='center'>")
response.write ("<tr><td bgcolor='#999999'><font face=verdana size=3><a href='display.asp?ID=") & rsNamesrch("clientID") & ("'>") & rsNamesrch("tName") & ("</a></font></td></tr>")
response.write ("<tr><td bgcolor='#CCCCCC'><font face=verdana size=2><a href='display.asp?ID=") & rsNamesrch("clientID") & ("'>View full details</a></font></td></tr>")
response.write ("<tr><td>&nbsp;</td></tr></table><br>")
CountName = CountName + 1
rsNamesrch.MoveNext
Loop


If CurPage > 1 THEN
response.write ("<p align='center'><font face='Verdana' size='2'><b><A href='testresults.asp?Curpage=") & CurPage - 1 & ("&namesearch=") & namesearchterm & ("'>Previous</A>")
ELSE
response.write ("<p align='center'><font face='Verdana' size='2'><b>")
END If

response.write (" ... Page ") & CurPage & (" of ") & TotalPages & (" ... ")

If Cint(CurPage) <> Cint(TotalPages) THEN
response.write ("<A href='testresults.asp?Curpage=") & CurPage + 1 & ("&namesearch=") & namesearchterm & ("'>Next</A></b></font></p>")
ELSE
response.write ("</b></font></p>")
End If


rsNamesrch.Close
Set adoConn = Nothing
Set strCon = Nothing
Set rsNamesrch = Nothing
%>

This code generates the first results, but the PageCount and AbsolutePage don't seem to generate anything or accept a value and the next and previous links don't appear.

If I comment out AbsolutePage I get the next link but just to show the first two results over and over again endlessly. The TotalPages value stays at '1' even when there are more results than the pages NumPerPage.

Can anyone suggest an alternate method of code to limit the results per page and where next and previous links can be generated when appropriate?

Or spot what's wrong with this one?

I've tried using LIMIT and offset, but I cannot get the syntax right in my select statement

A previous site I've written using ASP, with a mySQL database, with the same hosting company uses the same code and it works fine. I've even recreated the table on that sites mySQL database, changed the inclusive file for the connection and this code works perfectly on the other site.

I've spent days on this, any ideas would be welcome

Johnie

Try your loop like this


Do While Not rsNamesrch.EOF and rsNamesrch.AbsolutePage = curPage

instead of

Do While Not rsNamesrch.EOF AND CountName < rsNamesrch.PageSize

I'm afraid it's the same result.

The initial results appear to be the same, but it still does not generate the links to the next page.

If I force the URL with telling it, it is page 2 (?Curpage=2&namesearch=e)

it generates a previous and next button, but it states it is page 2 of 1 .. and then not all the results that should be returned are rerturned. Some are missed entirely.

If I comment out the code about PageCount and AbsolutePage and the code to generta the links, and have no limit on a page then .. EVERY coressponding result is returned.

So my problem seems to centre around finding a method of limiting the results per page and generating the next and previous links when they are necessary.

Johnie

It ended up that the server was not giving up any correct value within pagecount and absolute page.

My hosting company deleted my web site and gave me space on a new server. I uploaded my entire site, recreated my database.

And

Voila

The code just worked perfectly.

Live and learn!

Johnie