Webmaster Forum / ASP / Database Access / June 2007

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>