Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Posts
    7

    Unhappy Unanswered: Paged Search in ASP

    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 ("<!-- " & vbcrlf & sql & vbcrlf & " -->")
    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 = "<p>No Products found in this category</p>"
    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") & "<BR>")
    i = i + 1
    rs.movenext
    wend

    end if

    set rs = nothing

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

    intPageLimit = <% =intPageLimit %><BR/>
    intTotalRecords = <% =intTotalRecords %><BR/>
    intTotalPages = <% =intTotalPages %><BR/>


    <%

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

    <%
    next

    %>
    </BODY>
    </HTML>

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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).

  3. #3
    Join Date
    Feb 2004
    Posts
    7

    ADO Version

    Erm its on a Cobalt Raq4, ChiliSoft ASP.

    I don't know what version that is though.

    Chewiee

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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.

  5. #5
    Join Date
    Feb 2004
    Posts
    7

    ADO Compliance and Chilisoft ASP

    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

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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

  7. #7
    Join Date
    Feb 2004
    Posts
    7

    RecordCound

    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

  8. #8
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    *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.

  9. #9
    Join Date
    Feb 2004
    Posts
    7

    Cheers Buddy

    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

  10. #10
    Join Date
    Feb 2004
    Posts
    7

    Addendum

    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

  11. #11
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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.

  12. #12
    Join Date
    Feb 2004
    Posts
    7

    Demo

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •