Recordset Paging - Not Showing records on next page


I

Infused

When I use the next button no records show up on the next page...
I've been going over this for half a day and can't figure it out.
Any
help would be greatly appreciated!

<%@ Language=VBScript%>
<%Option Explicit%>
<%Response.Expires=0%>
<!--#include file = "adovbs.inc"-->
<%
Const intPageSize = 10
Dim intCurrentPage, intTotalPages, intI
Dim sClass
Dim rs, sCN, sSql, sDbFilePath
Dim dLNG, dLAT, sZip
Dim bViewList: bViewList = false


Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
rs.CursorType = 3
rs.CacheSize = intPageSize
sDbFilePath = Server.MapPath("zip.mdb")
sCN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDbFilePath &
";Persist Security Info=False"


If len(trim(Request.Form("zip"))) = 5 and
isnumeric(Request.Form("zip")) Then
sZip = Request.Form("zip")
SetCoordinates sZip
Set rs = Getmains(Request.Form("distance"), dLNG, dLAT)
bViewList = (not rs.EOF)
End If


'===================================================================
Function Getmains(dRadius, dLNG, dLAT)


Dim rsTemp, sSql
Set rsTemp = Server.CreateObject("ADODB.Recordset")


' **SQL Server vesion - use ACOS()**
' sSql = "SELECT name, phone, addr1, city, state, zip " & _
' "FROM main " & _
' "WHERE zip in ( SELECT ZIP_CODE FROM zip "& _
' "WHERE @radius > 3959 * ACOS(SIN(@lat/
57.3) *
SIN(LAT/ 57.3) + " & _
' "COS(@lat/
57.3) *
COS(LAT/ 57.3) * " & _
' "COS((LNG/
57.3) -
(@lng/ 57.3))) ) " & _
' "ORDER BY name"


' **Access vesion**
sSql = "SELECT id, name, phone, addr1, city, state, zip " & _
"FROM main " & _
"WHERE zip in ( SELECT ZIP_CODE FROM zip "& _
"WHERE @radius > 3959 * ATN( SQR(1 -
(SIN(@lat/
57.3) * SIN(LAT/ 57.3) + " & _

"COS(@lat/
57.3) * COS(LAT/ 57.3) * " & _

"COS((LNG/
57.3) - (@lng/ 57.3))) ^2 ) /" & _

"(SIN(@lat/ 57.3) * SIN(LAT/ 57.3) + "
& _

"COS(@lat/
57.3) * COS(LAT/ 57.3) * " & _

"COS((LNG/
57.3) - (@lng/ 57.3))) ) ) " & _
"ORDER BY name"


sSql = Replace(sSql, "@radius",dRadius)
sSql = Replace(sSql, "@lng",dLNG)
sSql = Replace(sSql, "@lat",dLAT)
rsTemp.Open sSql, sCN, 3, 3
Set Getmains = rsTemp


End Function
'===================================================================
Sub SetCoordinates(sZip)


Dim rsTemp, sSql
Set rsTemp = Server.CreateObject("ADODB.Recordset")


sSql = "SELECT LAT, LNG FROM zip WHERE ZIP_CODE = ':1'"
sSql = Replace(sSql, ":1",sZip)
rsTemp.Open sSql, sCN
If not rsTemp.EOF Then
dLAT = rsTemp("LAT")
dLNG = rsTemp("LNG")
End If
Set rsTemp = Nothing


End Sub
'===================================================================
'Function CreateMapQuestURL(street, city, state, zip)
'
' Dim sStreet, sCity
'
' sStreet = Replace(Trim(street), " ", "+")
' sCity = Replace(Trim(city), " ", "+")
'
' CreateMapQuestURL = "http://www.mapquest.com/maps/map.adp?" &
_
' "country=US&" & _
' "address=" & sStreet & "&" & _
' "city=" & sCity & "&" & _
' "state=" & state & "&" & _
' "zipcode=" & zip
'End Function


'==================================================================
'Function CreateMapBlastURL(name, street, city, state, zip)
'
' Dim sStreet, sCity, sName
'
' sStreet = Replace(Trim(street), " ", "+")
' sCity = Replace(Trim(city), " ", "+")
' sName = Replace(Trim(name), " ", "+")
'
' CreateMapBlastURL = "http://www.mapblast.com/mblast/map.mb?" &
_
' "IC=" & sName & "&" & _
' "AD2=" & sStreet & "&" & _
' "AD3=" & sCity & "&" & _
' "AD3=" & state & "&" & _
' "AD3=" & zip & "&" & _
' "CMD=GEO"
'End Function
'===================================================================
%>


<%
Response.Write("Server Variables: ")
Response.Write(Request.ServerVariables("ALL_RAW"))


If Request.ServerVariables("CONTENT_LENGTH") = 0 then
intCurrentPage = 1
Else
intCurrentPage = CInt(Request.Form("CurrentPage"))
Select Case Request.Form("Submit")
Case "Previous"
intCurrentPage = intCurrentPage - 1
Case "Next"
intCurrentPage = intCurrentPage + 1
End Select
End If
%>


<HTML>
<HEAD>
</HEAD>
<body>


<h2>
<img border="0" src="images/iemortgageguide_logo.gif" width="626"
height="97"></h2>
<h2>Search by Zipcode</h2>


<STYLE>
.shade {BACKGROUND: #ededed}
</STYLE>


<form method=post>
<TABLE BORDER=0 CELLSPACING=1 CELLPADDING=1>
<TR>
<TD>ZIP</TD>
<TD>Distance</TD>
<TD rowspan=2 valign=bottom><INPUT type="submit"
value="Go"
name=submit1></TD>
</TR>
<TR>
<TD><INPUT type="text" name=zip size=5 maxlength=5
value="<%=sZip
%>">
</TD>


<TD><SELECT name=distance>
<OPTION value=1>1</OPTION>
<OPTION value=5>5</OPTION>
<OPTION value=10>10</OPTION>
<OPTION value=20>20</OPTION>
<OPTION value=50>50</OPTION>
</SELECT></TD>


</TR>


</TABLE>
</form>


&nbsp;<%If bViewList Then%>


<TABLE BORDER=0 CELLSPACING=1 CELLPADDING=1>
<TR>
<TD><b>NAME</b></TD>
<TD><b>PHONE</b></TD>
<TD><b>ADDRESS</b></TD>
<TD><b>CITY</b></TD>
<TD><b>STATE</b></TD>
<TD><b>ZIP</b></TD>
</TR>


<%
rs.PageSize = intPageSize
'Response.Write(intPageSize)


If Not(rs.EOF) Then
intCurrentPage = rs.AbsolutePage


'Response.Write("AbsolutePage = ")
'Response.Write(rs.AbsolutePage)


intTotalPages = rs.PageCount
%>
<%
If sClass <> "shade" Then
sClass = "shade"
Else
sClass = ""
End If
%>
<% For intI = 1 to rs.PageSize %>
<TR class="<%=sClass%>">
<TD><a href="details.asp?pid=<%=rs("id")%>"><
%=rs("name") & ""%></
a></TD>
<TD><%=rs("phone") & ""%></TD>
<TD><%=rs("addr1") & ""%></TD>
<TD><%=rs("city") & ""%></TD>
<TD><%=rs("state") & ""%></TD>
<TD><%=rs("zip") & ""%></TD>
</TR>


<%rs.MoveNext
If rs.EOF Then Exit For
Next


rs.close
set rs = nothing%>
<% End If %>


</TABLE>
<br> <%= intCurrentPage %> of <%=intTotalPages %><p>
<form action="<%= Request.ServerVariables("SCRIPT_NAME") %>"
method="POST">
Script Name:
<% Response.Write(Request.ServerVariables("SCRIPT_NAME")) %>
<input type="hidden" name="CurrentPage" value="<%= intCurrentPage%>">
<%
If intCurrentPage > 1 Then %>
<input type="submit" value="Previous">
<% End If
If intCurrentPage <> intTotalPages Then %>
<input type="submit" name=submit value="Next">
<% End if %><% End IF%>
</form>
</body>
</html>
 
Ad

Advertisements

B

Bob Barrows [MVP]

Infused said:
When I use the next button no records show up on the next page...
I've been going over this for half a day and can't figure it out.

Please do not multipost. This is definitely a database-related
question so .asp.db was the perfect group in which to post it. Posting it
here as well did not increase your chances of getting an answer (most of us
subscribe to both groups). On the contrary, if somebody had taken his time
to answer it here, only to find that it was already resolved in the other
group, that person may have been annoyed enough to ignore any future posts
from you, thereby decreasing your chances of getting help in the future.

There are times when you will not be sure which group is most appropriate
(again, this was not one of them), and you will want to post a question to
both groups. In that situation, you should use the cross-posting technique,
rather than posting the same message multiple times. To crosspost, put
a semicolon-delimited* list of the newsgroups to which you wish to post in
the To: header of your post and post it once. It, and any replies to it,
will appear in all the newsgroups in your list. So, if I reply in .asp.db,
my reply will also appear here in .asp.general.

* ... or whatever delimiter is recognized by your news client
 
I

Infused

Please do not multipost. This is definitely a database-related
question so .asp.db was the perfect group in which to post it. Posting it
here as well did not increase your chances of getting an answer (most of us
subscribe to both groups). On the contrary, if somebody had taken his time
to answer it here, only to find that it was already resolved in the other
group, that person may have been annoyed enough to ignore any future posts
from you, thereby decreasing your chances of getting help in the future.

There are times when you will not be sure which group is most appropriate
(again, this was not one of them), and you will want to post a question to
both groups. In that situation, you should use the cross-posting technique,
rather than posting the same message multiple times. To crosspost, put
a semicolon-delimited* list of the newsgroups to which you wish to post in
the To: header of your post and post it once. It, and any replies to it,
will appear in all the newsgroups in your list. So, if I reply in .asp.db,
my reply will also appear here in .asp.general.

* ... or whatever delimiter is recognized by your news client

Sorry I was unaware of the cross posting technique... I will be sure
to use it in the future. I'm fairly diligent updating posts with a
resolution if I have gotten one as well to wherever I have posted. So
if I get a resolution to this I will definitely post it to the other
group or vice versa. Once again I apologize for double posting.
 
Ad

Advertisements

I

Infused

AnxietyAnswers wrote on Tue, 26 Jun 2007 01:39:18 -0700:






In the above form you don't have any of the fields required to fill the
query with values for the ZIP being looked for, so your query is running
with different data than the initial request, and likely bringing back an
empty recordset which is why you get no results. You'll need to add the ZIP
code as a hidden field in the form so it gets resubmitted with the
Previous/Next button.

Absolutely right thanks Dan I added the hidden fields in the form and
now I'm getting the intial recordset in next page. Thanks!
Also I can't see anywhere in the code I trimmed out that sets the page to
view - you're reading the AbsolutePage value into the intCurrentPage
variable, rather than setting AbsolutePage to the page you want to view, so
even if you'd been passing the ZIP in the second form you'll just be showing
page 1 of the results again no matter which button is pressed.

Dan- Hide quoted text -

- Show quoted text -

I kept getting the error

ADODB.Recordset error '800a0cb3'
Current Recordset does not support bookmarks. This may be a limitation
of the provider or of the selected cursortype.

when I had the abolsultepage = intcurrentpage the right way around

and thought I had the cursortype set the right way but when I checked
it was coming up with cursortype = 0

I've got everything fixed and it's working perfect now thank you so
very much for your help I really appreciate it!!!

Jon
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top