Paged Search in ASP

chewiee · #1 (**permalink**) 02-03-04, 19:19

I've been trying to do a paged search in asp using mysql.

I want to be able to allow my clients to page 400 records across x amount of pages.

I've been searching across the net for ages today (started at about 10am and I've still not got a solution.)

Heres my code:

<%

Dim rs
Dim strSQL

Dim intPageLimit
Dim intTotalRecords
Dim intTotalPages
Dim intCurrentPage
Dim intCurrentRecords

Dim blnCheck
Dim i

strSQL = "SELECT * from sj_tblProducts order by sj_tblProducts_recid"

intPageLimit = 15 ' Current amount of records to display on one page

intTotalRecords = rscount(strSQL)
intTotalPages = (intTotalRecords) / intPageLimit ' Round up the current page totals so we get the correct amount of pages to display
intCurrentPage = request.querystring("page") ' What page of records are we on?

if intTotalPages > int(intTotalPages) then intTotalPages = int(intTotalPages) + 1

Function GetMySQLDSN (server, dbname, username, password)
if request.servervariables("SERVER_SOFTWARE") = "Microsoft-IIS/5.0" then
GetMySQLDSN = "Driver=MySQL ODBC 3.51 Driver;SERVER=" & server & ";DATABASE=" & dbname & ";UID=" & username & ";PASSWORD=" & password
else
GetMySQLDSN = "Driver={MySQL};SERVER=" & server & ";DATABASE=" & dbname & ";UID=" & username & ";PASSWORD=" & password
end if
End Function

function ExecuteMySQLWithRS(Site, Database, username, password, SQL)
dim strDSN
dim rs

'Response.Write ("")
strDSN = GetMySQLDSN(Site, Database, username, password)
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open SQL, strDSN
set ExecuteMySQLWithRS = rs
set rs = nothing
end function

Function rsCount(strSQL)
Dim rs
Dim i
i = 0
set rs = ExecuteMySQLWithRS(strServer, strDatabase, strUsername, strPassword, strSQL)
if rs.bof and rs.eof then
i = 0
else
while not rs.eof
i = i + 1
rs.movenext
wend
end if
set rs = nothing
rsCount = i
End Function

Function records_movetorecord(rs, intID)
Dim i
rs.movefirst
for i = 1 to intID
rs.movenext
next
End Function

intCurrentRecords = 0

set rs = ExecuteMySQLWithRS(strServer, strDatabase, strUsername, strPassword, strSQL)
if rs.eof and rs.bof then
strResult = "No Products found in this category"
else
for i = 1 to intPageLimit * intCurrentPage
rs.movenext
next

i = 0
while not i >= intPageLimit or rs.eof
Response.write (i & ":" & rs("sj_tblProducts_recid") & " ")
i = i + 1
rs.movenext
wend

end if

set rs = nothing

%>
<HTML>
<HEAD>
<TITLE>Test</TITLE>
</HEAD>
<BODY>

intPageLimit = <% =intPageLimit %> 
intTotalRecords = <% =intTotalRecords %> 
intTotalPages = <% =intTotalPages %> 

<%

for i = 1 to intTotalPages %>
<a href="test.asp?page=<% =i %>"><% =i %></a>

<%
next

%>
</BODY>
</HTML>

rokslide · #2 (**permalink**) 02-04-04, 00:41

What version of ADO are you using? There are a lot of methods and propertiers you can use to help with this (not the least of which is record count).

chewiee · #3 (**permalink**) 02-04-04, 05:48

Erm its on a Cobalt Raq4, ChiliSoft ASP.

I don't know what version that is though.

Chewiee

rokslide · #4 (**permalink**) 02-04-04, 18:12

Okie,... I don't know enough about ChiliSoft ASP to comment on what version might come with it...

In short I would suggest finding out what version it is, then checking the reference manual to find what methods and properties are available to you. The way you are going about it at the moment is definately the long way,... your basic strategies look fairly good though.

As a side point (and you might be doing this already) store the search results in a session variable or something so that you don't need to requery every time they move to a new page.

chewiee · #5 (**permalink**) 02-04-04, 18:47

AFAIK its ADO 2.0 compliant.

If its longwinded for that, please someone tell me what I can do to make it more graceful.

I've changed it a little, as it needed tweaking, starting from 0 in places etc. I've got it working, but I have not made it into a session object.

Any help in this area would be great.

Chewiee

rokslide · #6 (**permalink**) 02-04-04, 19:23

Okie, I don't know if these methods work properly in ADO 2.0 but...

Move will let you move multiple records at once so if you are after page 5 for example you want to start at record 5 * number of record per page so move that distance in 1 step, not my looping through each individual one.

Use the RecordCount to determine the total number of records instead of looping through each one and incrementing a counter...

HTH

chewiee · #7 (**permalink**) 02-04-04, 19:37

I know for a fact that RecordCount does not work with cASP and mySQL.

This is why its got the subroutine. But what about the rest of the code? All messy there too?

This is something I have been meaning to write for the last couple of years, and its got to a point now where I have to write it

I need to learn this as I keep coming up against this issue and shying away from it!

Chewiee

rokslide · #8 (**permalink**) 02-04-04, 19:50

*ack* RecordCount doesn't work? Or does it work like ADO record count the returned result is dependant on the correct cursor being used?

The MS ADO components will, more often then not return -1 for their record count unless you use (I think) a static cursor. Then the true record count will be returned.

Your general approach looks sound. If you can't use the features I have meantioned then it will never look that great.

chewiee · #9 (**permalink**) 02-04-04, 20:01

I've been staring at this for two days now, and I was beginning to think that there was an easier method!

I have even had to learn a little about recursion! I might print the code out and frame it... this client has been awaiting this update for ages, and I bet they won't even appreciate the effort and just say the immortal words, "Yeah, it looks great!"

Cheers for spending the time looking at it, it all seems ok, records 1 - 15 are appearing on page 1 and 16 - 30 are appearing on page 2 etc.

As for this ASP recursion routine, if anyone is interested, here is the code:

Function Return_CategoryParentID(intID)
' This executes the SQL Server Stored procedure on the server
Dim strResult
Dim rs
Dim strSQL

strSQL = "SELECT sj_tblCategory_ParentID, sj_tblCategory_recid from sj_tblcategory where sj_tblCategory_recid =" & intID
set rs = ExecuteMySQLWithRS (strServer, strDatabase, strUsername, strPassword, strSQL)
if rs.bof and rs.eof then
strResult = "None"
else
if rs("sj_tblCategory_ParentID") <> 0 then
strResult = Return_CategoryParentID( rs("sj_tblCategory_ParentID") )
else
strResult = rs("sj_tblCategory_recid")
end if

end if
set rs = nothing

Return_CategoryParentID = strResult
End Function

chewiee · #10 (**permalink**) 02-04-04, 20:04

Ignore the function comment, its been converted from a stored procedure... at the moment mySQL does not support these, and I am having to reverse engineer the code for a client...

Chewiee

rokslide · #11 (**permalink**) 02-04-04, 20:08

One of my friends needed some help to do this once... I lept in and gave a hand... the biggest problem comes when you move to the last page and then move back again (if that makes sense).

Flicking through individual pages is normally fine.

Just make sure that you can go all the way forward in a single click and all the way back in a single click as well.

chewiee · #12 (**permalink**) 02-04-04, 20:18

If you wanna demo it buddy, msn me

g i r a f f e d o g * h o t mmm a i l . cooom

You know what to do

Chewiee

Internet Services

Web Development

Digital Marketing

Consumer Tech