Paging Through Recordset Loses Form Results After 1st Page 2

dlcook · Nov 1, 2001

I have a form that lists countries. The user selects his country and the post action calls an asp page that displays the first 25 records of the recordset. When I click the hyperlink to take me to the next page the form data is gone and I get an EOF.

I've read the article on Microsoft Q175167 regarding Storing State the various ways ie. Session Variables, Cookies, Hidden Form Fields and QueryString. I can't get it to work. I could use some help. The form page is located at

http://www.ftmyers.com/scripts/rtrank_test.asp

and the page that displays the results is located at

http://www.ftmyers.com/Scripts/nations_cup_new.asp

- if you choose "Finland" from the list you will get more than 25 records and can see what is going wrong.

I'm a newbie at ASP and am doing this site for Rally Trophy hotlaps (stage times) a racing simulation. I would appreciate very much if someone could help me out

David Cook dlcook@pobox.xom

ToddWW · Nov 1, 2001

Well, I see your error. I think without re-writing an entire example of recordset paging here for you to pick through, why don't you show us a copy of the recordset creation. Heck, show us a copy of the whole page and point to line 55 (I think it is) that is throwing the EOF error.

Should be a simple fix

ToddWW

dlcook · Nov 2, 2001

Todd - I removed the code that attempted to pass the data beyond page one because I couldn't get it to work. Below is the page with the form dropdown for Countries (use Finland for this test). I may have the syntax wrong for Finland as well, but I'm not sure. When a user selects a Country that form data is passed to the asp page to do a select on the recordset and the first 25 records are listed. The form data gets lost on the next page or any other page for that matter.

Here is

http://www.ftmyers.com/scripts/rtrank_test.asp

<% Option Explicit

' ADO constants used in this page
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdTableDirect = &H0200
Const adUseClient = 3

%>
<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN">
<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style>
body { font-family : Arial; font-size : 10pt; }
a { font-family : Arial; font-size : 10pt; text-decoration : none; }
</style>
<title>Rally Trophy Stage Times</title>
</head>

<BODY BGCOLOR="#FFFFFF" vlink="#0000FF" alink="#0000FF" leftmargin="20">
<p><img border="0" src="../images/rtrank.gif"><img border="0" src="../images/cf.gif"></p>
<h1>Rally Trophy Stage Times</h1>
<table border="0" cellpadding="0" cellspacing="0" width="674">
<tr>
<td width="313" valign="top"><img border="0" src="../images/wheel.gif"> <a href="../rtrank_add.htm">Add
New User Data/Stage Times</a><br>
<img border="0" src="../images/wheel.gif"> <a href="../revise-data.htm">Revise
Existing User Data /Stage Times</a><br>
<img border="0" src="../images/wheel.gif"> <a href="

http://forum.jowood.com/forumdisplay.php?s=&forumid=11"

target="_blank">JoWooD
Rally Trophy Forum</a></td>
<td width="357" valign="top">
<form method="POST" action="/Scripts/nations_cup_new.asp">
<p><img border="0" src="../images/wheel.gif"><font color="#0000FF">
<font size="2">Nations Cup - Rank by Country</font></font><br>
<select id="Country" name="Country"><option>[Select Country]</option><option>Albania </option><option>Algeria </option><option>Andorra </option><option>Angola </option><option>Antigua </option><option>Argentina </option><option>Armenia </option><option>Aruba </option><option>Australia </option><option>Austria </option><option>Azerbaijan </option><option>Bahama </option><option>Bahrain </option><option>Bangladesh </option><option>Barbados </option><option>Belarus </option><option>Belgium </option><option>Belize </option><option>Benin </option><option>Bhutan </option><option>Bolivia </option><option>Bosnia </option><option>Bosniancroat </option><option>Botswana </option><option>Brazil </option><option>Brunei </option><option>Brurundi </option><option>Bulgaria </option><option>Burkina </option><option>Burma </option><option>Cambodia </option><option>Cameroon </option><option>Canada </option><option>Cape_Verde </option><option>Centafrican </option><option>Chad </option><option>Chile </option><option>China </option><option>Columbia </option><option>Comoros </option><option>Congo </option><option>Congo_Drot </option><option>Costa </option><option>Cote </option><option>Croatia </option><option>Cuba </option><option>Cyprus </option><option>Czech </option><option>Denmark </option><option>Dijibouti </option><option>Djibouti </option><option>Dominica </option><option>Dominican </option><option>Ecuador </option><option>Egypt </option><option>Elsalvador </option><option>Equatorguinea </option><option>Eritrea </option><option>Estonia </option><option>Ethiopia </option><option>Fiji </option><option value='Finland'>Finland </option><option>France </option><option>Gabon </option><option>Gambia </option><option>Georgia </option><option>Germanstate </option><option>Germany </option><option>Ghana </option><option>Great_Brittain </option><option>Greece </option><option>Greenland </option><option>Grenada </option><option>Guatemala </option><option>Guinea </option><option>Guinea_Bissau </option><option>Guyana </option><option>Haiti </option><option>Honduras </option><option>Hong_Kong </option><option>Hungary </option><option>Iceland </option><option>India </option><option>Indonesia </option><option>Ireland </option><option>Israel </option><option>Italy </option><option>Jamaica </option><option>Japan </option><option>Jordan </option><option>Kazakhstan </option><option>Kenya </option><option>Kiribati </option><option>Korea_North </option><option>Korea_South </option><option>Krajina </option><option>Kuwait </option><option>Kyrgyzstan </option><option>Laos </option><option>Latvia </option><option>Lebanon </option><option>Lesotho </option><option>Liberia </option><option>Libya </option><option>Liechtenstein </option><option>Lithuania </option><option>Luxembourg </option><option>Macedonia </option><option>Madagascar </option><option>Malawi </option><option>Malaysia </option><option>Maldives </option><option>Mali </option><option>Malta </option><option>Marianas </option><option>Marshall_Islands </option><option>Mauritania </option><option>Mauritius </option><option>Mexico </option><option>Micronesia </option><option>Moldova </option><option>Monaco </option><option>Mongolia </option><option>Morocco </option><option>Mozambique </option><option>Myanmar </option><option>Namibia </option><option>Nauru </option><option>Nepal </option><option>Netherlands </option><option>New_Zealand </option><option>Nicaragua </option><option>Niger </option><option>Nigeria </option><option>North_Ireland </option><option>Norway </option><option>Oman </option><option>Pakistan </option><option>Palau </option><option>Palestine </option><option>Panama </option><option>Papua_New_Guinea </option><option>Paraguay </option><option>Peru </option><option>Philippines </option><option>Poland </option><option>Portugal </option><option>Puertorico </option><option>Qatar </option><option>Romania </option><option>Russia </option><option>Rwanda </option><option>Saint_Helena </option><option>Saint_Lucia </option><option>San_Marino </option><option>Sao_Tome </option><option>Saudi_Arabia </option><option>Scotland </option><option>Senegal </option><option>Serbia </option><option>Seychelles </option><option>Sierra_Leone </option><option>Singapore </option><option>Slovakia </option><option>Slovenia </option><option>Solomon_Islands </option><option>Somalia </option><option>South_Africa </option><option>Spain </option><option>Sri_Lanka </option><option>St_Kilts </option><option>St_Vincent </option><option>Sudan </option><option>Suriname </option><option>Swaziland </option><option>Sweden </option><option>Switzerland </option><option>Syria </option><option>Taiwan </option><option>Tajikistan </option><option>Tanzania </option><option>Thailand </option><option>Tibet </option><option>Tonga </option><option>Tongo </option><option>Trinidad </option><option>Tunisia </option><option>Turkey </option><option>Turkmenistan </option><option>Tuvalu </option><option>Ukraine </option><option>United_Kingdom </option><option>United_States </option><option>UnitedArabEmirates </option><option>Urganda </option><option>Uruguay </option><option>Uzbekistan </option><option>Vanuato </option><option>Venezuela </option><option>Vietnam </option><option>Wales </option><option>Western_Somoa </option><option>Yemen </option><option>Yugoslavia </option><option>Zambia </option><option>Zimbabwe </option></select> 
<input type="submit" value="Submit"> <input type="reset" value="Reset"></p>
</form>
</td>
</tr>
</table>
<P>
<%
Dim rs
Set rs = Server.CreateObject("ADODB.Recordset&quot

rs.PageSize = 25
rs.CacheSize = 25
rs.CursorLocation = adUseClient

Dim conn
Set conn = Server.CreateObject("ADODB.Connection&quot

conn.Open "DSN=listing"

Dim strSQL
strSQL = "SELECT Country, Name, Model, Track, Stime FROM hotlaps ORDER BY Stime"
rs.Open strSQL, Conn

If Len(Request("pagenum&quot

) = 0 Then
rs.AbsolutePage = 1
Else
If CInt(Request("pagenum&quot

) <= rs.PageCount Then
rs.AbsolutePage = Request("pagenum&quot

Else
rs.AbsolutePage = 1
End If
End If

Dim abspage, pagecnt
abspage = rs.AbsolutePage
pagecnt = rs.PageCount

Dim Rank
Rank = (abspage*25)-24

If Not rs.EOF Then

Dim intRec

For intRec=1 To rs.PageSize
If Not rs.EOF Then
%>

<table border="0" width="674">
<tr>
<td valign="TOP" width="97">
<img src="/flags/<%= RS("Country&quot

%>.gif" border="0" align="LEFT" height="50" width="68"></a></td>
<td valign="TOP" width="282">
<nobr><b>Name:</b> <%= RS("Name&quot

%></nobr><br>
<nobr><b>Time:</b> <%= RS("Stime&quot

%></nobr><br>
<nobr><b>Rank:</b> <%= Rank %></nobr>
</td>
<td valign="TOP" width="281">
<nobr><b>Country:</b> <%= RS("Country&quot

%></nobr><br>
<nobr><b>Model:</b> <%= RS("Model&quot

%></nobr><br>
<nobr><b>Track/Stage:</b> <%= RS("Track&quot

%></nobr>
</td>
</tr>
</table>
<HR SIZE="3">

<% Rank = Rank + 1
rs.MoveNext
End If
Next
Response.Write "<p>"
' Now showing first, next, back, last buttons.

Response.Write "<div align=""left"">" & vbcrlf
Response.Write "<a href="""
Response.Write Request.ServerVariables("SCRIPT_NAME&quot

Response.Write "?pagenum=1"">First Page</a>"
Response.Write " | "

If abspage = 1 Then
Response.Write "<span style=""color:silver;"">Previous Page</span>"
Else
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME&quot

Response.Write "?pagenum=" & abspage - 1 & """>Previous Page</a>"
End If

Response.Write " | "
If abspage < pagecnt Then
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME&quot

Response.Write "?pagenum=" & abspage + 1 & """>Next Page</a>"
Else
Response.Write "<span style=""color:silver;"">Next Page</span>"
End If

Response.Write " | "
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME&quot

Response.Write "?pagenum=" & pagecnt & """>Last Page</a>"
Response.Write "</div>" & vbcrlf

Else
Response.Write "No records found!"
End If

rs.Close
Set rs = Nothing

%>
<table border="0" width="640" height="90">
<tr>
<td width="100%" height="86"><br>
<img border="0" src="../images/rt_logo2.gif" align="left"></td>
</tr>
</table>
<p>
Comments or Suggestions: <a href="mailto:rtrank@ftmyers.com">rtrank@ftmyers.com</a>
</p>
</body>
</html>

Here is

http://www.ftmyers.com/Scripts/nations_cup_new.asp<%

Option Explicit

' ADO constants used in this page
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdTableDirect = &H0200
Const adUseClient = 3
%>

<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN">
<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style>
body { font-family : Arial; font-size : 10pt; }
a { font-family : Arial; font-size : 10pt; text-decoration : none; }
</style>
<title>Rally Trophy Nations Cup</title>
</head>
<BODY BGCOLOR="#FFFFFF" vlink="#0000FF" alink="#0000FF" leftmargin="20">

<p><img border="0" src="../images/rtrank.gif"><img border="0" src="../images/cf.gif"></p>
<h1>Rally Trophy Nations Cup</h1>
<P>
<%
Dim rs
Set rs = Server.CreateObject("ADODB.Recordset&quot

rs.PageSize = 25
rs.CacheSize = 25
rs.CursorLocation = adUseClient

Dim Country
Country = Request.Form("Country&quot

Country = Replace(Country,"'","''&quot

Dim conn
Set conn = Server.CreateObject("ADODB.Connection&quot

conn.Open "DSN=listing"

Dim strSQL
strSQL = "SELECT * FROM hotlaps WHERE Country = '" & Country & "' "
strSQL = strSQL & "ORDER BY Stime ASC "
rs.Open strSQL, Conn

If Len(Request("pagenum&quot

) = 0 Then
rs.AbsolutePage = 1
Else
If CInt(Request("pagenum&quot

) <= rs.PageCount Then
rs.AbsolutePage = Request("pagenum&quot

Else
rs.AbsolutePage = 1
End If
End If

Dim abspage, pagecnt
abspage = rs.AbsolutePage
pagecnt = rs.PageCount

Dim Rank
Rank = (abspage*25)-24

If Not rs.EOF Then

Dim intRec

For intRec=1 To rs.PageSize
If Not rs.EOF Then

%>

<table border="0" width="674">
<tr>
<td valign="TOP" width="97">
<img src="/flags/<%= RS("Country&quot

%>.gif" border="0" align="LEFT" height="50" width="68"></a></td>
<td valign="TOP" width="282">
<nobr><b>Name:</b> <%= RS("Name&quot

%></nobr><br>
<nobr><b>Time:</b> <%= RS("Stime&quot

%></nobr><br>
<nobr><b>Rank:</b> <%= Rank %></nobr>
</td>
<td valign="TOP" width="281">
<nobr><b>Country:</b> <%= RS("Country&quot

%></nobr><br>
<nobr><b>Model:</b> <%= RS("Model&quot

%></nobr><br>
<nobr><b>Track/Stage:</b> <%= RS("Track&quot

%></nobr>
</td>
</tr>
</table>
<HR SIZE="3">

<% Rank = Rank + 1
rs.MoveNext
End If
Next
Response.Write "<p>"
' Now showing first, next, back, last buttons.

Response.Write "<div align=""left"">" & vbcrlf
Response.Write "<a href="""
Response.Write Request.ServerVariables("SCRIPT_NAME&quot

Response.Write "?pagenum=1"">First Page</a>"
Response.Write " | "

If abspage = 1 Then
Response.Write "<span style=""color:silver;"">Previous Page</span>"
Else
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME&quot

Response.Write "?pagenum=" & abspage - 1 & """>Previous Page</a>"
End If

Response.Write " | "
If abspage < pagecnt Then
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME&quot

Response.Write "?pagenum=" & abspage + 1 & """>Next Page</a>"
Else
Response.Write "<span style=""color:silver;"">Next Page</span>"
End If

Response.Write " | "
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME&quot

Response.Write "?pagenum=" & pagecnt & """>Last Page</a>"
Response.Write "</div>" & vbcrlf

Else
Response.Write "No records found!"
End If

rs.Close
Set rs = Nothing
%>

<table border="0" width="640" height="90">
<tr>
<td width="100%" height="86"><br>
<img border="0" src="../images/rt_logo2.gif" align="left"></td>
</tr>
</table>
<p>
Comments or Suggestions: <a href="mailto:rtrank@ftmyers.com">rtrank@ftmyers.com</a>
</p>
</body>
</html>

dlcook · Nov 2, 2001

...Just to let you know - Paging works - Just not when I use the data from the form. If you look at

http://www.ftmyers.com/scripts/rtrank.asp

you can see that the code works fine if I'm not trying to pull results from a POST.

Thanks for your help. I look forward to getting this working!!!

David Cook

ToddWW · Nov 2, 2001

Well, here's your current problem.

Dim Country
Country = Request.Form("Country&quot
Country = Replace(Country,"'","''&quot

Dim conn
Set conn = Server.CreateObject("ADODB.Connection&quot
conn.Open "DSN=listing"

Dim strSQL
strSQL = "SELECT * FROM hotlaps WHERE Country = '" & Country & "' "
strSQL = strSQL & "ORDER BY Stime ASC "
rs.Open strSQL, Conn

While that's valid when you reach this page from the country selection page, it's not a valid paramater while you're paging through from there because you're not passing the country form element while paging.

The quickest fix to this would be this.

Dim Country
IF Request.Form("Country&quot <> "" THEN
Session("Country&quot = Request.Form("Country&quot
END IF
Country = Replace(Session("Country&quot,"'","''&quot

Dim conn
Set conn = Server.CreateObject("ADODB.Connection&quot
conn.Open "DSN=listing"

Dim strSQL
strSQL = "SELECT * FROM hotlaps WHERE Country = '" & Country & "' "
strSQL = strSQL & "ORDER BY Stime ASC "
rs.Open strSQL, Conn

That will store and change the Country to a Session Variable as needed. That variable sticks with the user and each time they change the country, so does the variable. But now, the SQL is getting data from the Session Variable instead of directly from the form contents.

Now, you may have other issues, but I looked over your code pretty carefully and it looks like that might just do it.

ToddWW

dlcook · Nov 2, 2001

It WORKS!!! Thank you SO much

Send me an address where you hang and I'll order you a pizza from your local Pizza Hut! Serious - I did this for Andrew Tridgell and the Canberra Linux User Group in Australia for the work they did on Samba. Thanks again!

-David Cook

dlcook · Nov 2, 2001

Ooh, just noticed one thing. Do you know how I can avoid the dreaded EOF if someone chooses a country that doesn't have any records? Head over to

http://www.ftmyers.com/scripts/rtrank_test.asp

and choose Albania and you'll see.

-David Cook

ToddWW · Nov 2, 2001

The lines that are throwing the EOF errors are the ones where you are trying to set the page. You can't do that to a recordset that doesn't have any records in it.

Therefore, you need to move the first IF condition..

Code:

If Not rs.EOF Then

and place it right after the rs.Open command.

ToddWW

dlcook · Nov 2, 2001

Perfect! Thanks - I will put a link to your site on the bottom of mine as a token of my appreciation.

-David Cook

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Paging Through Recordset Loses Form Results After 1st Page 2

dlcook

MIS

ToddWW

Programmer

dlcook

MIS

dlcook

MIS

ToddWW

Programmer

dlcook

MIS

dlcook

MIS

ToddWW

Programmer

dlcook

MIS

Similar threads

Part and Inventory Search

Sponsor