Paging through Recordsets

buckster · May 7th, 2004, 02:55 AM

Hi - I am having real problems trying to page through a set of results retrieved from my database. I have pasted my code below so that hopefully someone will be able to help me out.

I know that the code that displays the data works properly because if I purely write out an SQLquery and then run the page the data displays as i want it to with next and previous buttons moving through the records. However I need to include the "if" statements near the top of the code to check which values the user has selected from the form fields. When I do this only the first five results are returned and then upon clicking the "next" button I am returned back to the form. I cannot get it to continue through the recordset to show the remaining results.

Many thanks in advance for any help that I receive.

<html>
<head>
<title>My Website</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

<link rel="stylesheet" href="3lionsstyle.css">

</head>
</html>

<%

'Declare the variables'

Dim formsubmitted
Dim venuechoice,roomtypechoice,roompricechoice

'Set values for each variable'

formsubmitted=Request.ServerVariables("QUERY_STRIN G")<> ""
venuechoice=Trim(Request.QueryString("venue"))
roomtypechoice=Trim(Request.Querystring("roomtype" ))
roompricechoice=Trim(Request.Querystring("roompric e"))

'Set the session to UK locale ID'

session.LCID = 2057

' Create the connection object
set conn = server.createobject("ADODB.connection")

' Open the link to the database
conn.Open = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("EuroData.mdb")

'Check to see if the user has submitted a query via the Searchhotels section of the form'
'If they have then run the other if statements to see what data they require'

if Request.QueryString("Searchhotels")="Search" then

'Check the criteria of the users query and then run the appropriate sql query'

if venuechoice="Select All" and roomtypechoice="Select All" and roompricechoice="Any Price" then
SQLquery="SELECT * FROM hotels ORDER BY location,roomrate DESC"

end if

'Set up the send sub string to display the results'

sub send (str)
response.write str & vbcrlf
end sub

myPageSize=5
set RS = server.createObject("ADODB.Recordset")
RS.CursorLocation = 3
RS.CursorType = 3
RS.CacheSize = myPageSize
RS.open SQLquery, conn, , , adCmdText
RS.PageSize = myPageSize

' Now we can request a certain group of rows (page) from the recordset.

totalPages = RS.PageCount
totalRows = RS.RecordCount
if currentPage<1 then currentPage=1
if currentPage>RS.PageCount then currentPage=RS.PageCount

if not (RS.eof) then
    RS.AbsolutePage = currentPage
end if

if request.servervariables("CONTENT_LENGTH")=0 then
    currentPage=1
else
    ' Get the existing current page number
    currentPage=CInt(request.form("currentPage"))
    ' Increment or decrement current page number
    select case request.form("navigation")
          case "Previous-page"
            currentPage=currentPage-1
        case "Next-page"
            currentPage=currentPage+1
    end select
end if

%>

<div class=resultspage>

<%
'Check to ensure that the query has returned any results'
'If no results were found then display the message to advise the user'

if (RS.BOF and RS.EOF) then
    send "<p class=thetext>No Records Found</p>"
end if

'Set up a table to display the results'

send "<table>"

send "<tr>"
send "<td width=150><p class=headers>" & "Photo"
send "<td width=100><p class=headers>" & "Hotel"
send "<td width=100><p class=headers>" & "Location"
send "<td width=75><p class=headers>" & "Rating"
send "<td width=125><p class=headers>" & "Board Basis"
send "<td width=125><p class=headers>" & "Price per Room"
send "</tr>"

' Use a loop to repeat until the last row in the recordset

' -- Display rows from the recordset
for j = 1 to RS.PageSize
    send "<tr>"
    send "<td width=150><img src=Hotels/" & RS("hotelphoto") & " width=145 alt='Hotel'> </td>"
    send "<td width=100><p class=results>" & RS("hotelname") & "</p></td>"
    send "<td width=100><p class=results>" & RS("location") & "</p></td>"
    send "<td width=75><p class=results>" & RS("rating") & "</p></td>"
    send "<td width=125><p class=results>" & RS("basis") & "</p></td>"
    send "<td width=125><p class=results>" & formatCurrency(RS("roomrate"),2) & "</p></td>"
    send "</tr>"
    ' Move onto the next record
    RS.movenext
    If RS.eof Then Exit For
next

' When all records have been displayed close the table
send "</table>"

myScript=request.ServerVariables("SCRIPT_NAME")
send "<form name=form1 action=" & myScript & " method=post>"
send "<input type=hidden name=currentPage value=" & currentPage & ">"
if currentPage > 1 then
    send "<input type=submit name=navigation value='Previous-page' class='nav'>"
end if
if currentPage<totalPages then
    send "<input type=submit name=navigation value='Next-page' class='nav'>"
end if
send "</form></body></html>"

'Close the recordset'
'Close the connection'

rs.close

Set rs=nothing

conn.close

set conn=nothing
end if
%>

<html>
<body bgcolor="000000">
<%
if formsubmitted=false then
%>
<div id=eurologo><img src="eurologo.jpg"></div>
<div class=maintext><div class=maintextholder>Intro Text</div></div>

<div class=formholder>
<form name="form1" method="get" action="webhelp.asp">
   <div class=topformpos>
    <div class="formheader">Hotel Search</div>
    <div class="theformtext">Venue</div>
      <select name="venue" id="select">
        <option>Select All</option>
      <option>Porto</option>
      <option>Faro</option>
      <option>Leiira</option>
      <option>Lisbon</option>
      <option>Guimaraes</option>
      <option>Aveiro</option>
      <option>Coimbra</option>
      <option>Braga</option>
      </select>
    <p>
     <div class="theformtext">Accomodation Type</div>
    <select name="roomtype" id="select">
      <option>Select All</option>
      <option>Self Catering</option>
      <option>Half Board</option>
      <option>Bed and Breakfast</option>
      <option>Room Only</option>
    </select>
    <p><div class="theformtext">Max Price Per Room (Â£)</div>
    <select name="roomprice" id="select">
        <option>Any Price</option>
        <option>200</option>
        <option>150</option>
        <option>100</option>
        <option>60</option>
      </select>
    <input type="submit" name="Searchhotels" value="Search">
    </div>
    </form>
</div>
<%end if%>
</body>
</html>

Imar · May 7th, 2004, 03:27 AM

Hi there,

I haven't looked at all the code in full detail, but I noticed this:

Code:

if Request.QueryString("Searchhotels")="Search" then

This seems to use a variable from the QueryString. However, a bit later in your code, you use a POST form with this code:

Code:

myScript=request.ServerVariables("SCRIPT_NAME")
send "<form name=form1  action=" & myScript & " method=post>"

The SCRIPT_NAME will not contain the QueryString, but just the page name. So, whenever the form form1 is submitted by clicking the Next button, you'll loose the value of the Searchhotels QueryString, causing your code to fail.

Either add the QueryString to the myScript variable (something like this:

Code:

myScript = Request.ServerVariables("SCRIPT_NAME") & "?" & Request.QueryString

or change the entire page so it uses just POST variables. In that case, you'll need to save the values of variables like Searchhotels in hidden form fields, so they are submitted with the form every time a user navigates forwards or backwards.

To make your page easier to understand, maintain and function, you could take a look at XHTML. Your code uses quite "old and messy" HTML, which can cause all kinds of problems (most notably, not quoting the values for attributes in form elements which can result in only partially submitted information).

Does this help?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: De Eerste X by Doe Maar (Track 9 from the album: Doris Day en andere Stukken) What's This?

buckster · May 7th, 2004, 06:36 AM

Thanks for your response Imar.

I am really new to ASP and stuggling somewhat. I have no idea how to use hidden fields as you suggested. I tried to change the value of myScript as you suggested but this time I get the first five database entries continually repeating upon the clicking of the next button. This time however I do see the previous button appear after clicking the next button as though the data shown is the second set of records.Do I need to modify the line of code that you wrote - Do I need to replace the ? with a value or is this the actual code. As you said I have used the get method because this was how I was taught. However all of the recordset paging methods that I have seen seem to use the post method thereby causing a conflict.

Sorry to appear clueless about this - I only started ASP about a month back.

Thanks Again.

Imar · May 7th, 2004, 06:58 AM

Hi there,

You might want to take a look here: http://www.asp101.com/articles/recor...ging/index.asp
Paging is explained in a few simple steps. The example uses GET for the page that pages the search results. If I were you, I'd create a basic paging page that pages through SELECT * FROM Hotels, without the additional stuff like filters.
Once you have the paging working, you can add features like filtering / searching. This way is easier than the other way around.

The ? should be taken literally. Suppose the QueryString is:

?Hello=Bla

Then my code would post the page to:

YourPage.asp??Hello=Bla

This way, you can post stuff using the form, and still retrieve information from the querystring.

If it is an option for you, I would change the form so it uses either GET or POST exclusively. This makes it easier to see what goes where. You can mix the two, but it may make things more difficult.
If you're using Dreamweaver, there are built-in features that enable you to add paging quite easily.....

It's not really the answer you may be hoping for, but this is quite difficult to explain and debug over a Web forum, without having the actual code and database here.

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Narayan by The Prodigy (Track 7 from the album: The Fat Of The Land) What's This?

buckster · May 7th, 2004, 07:57 AM

Thanks very much. I have had a quick glance at the link and it looks promising. I will try it out later- hopefully I will be able to get it to work.

Your help is much appreciated.