ASP Paging Problem using SQL

dparsons · August 29th, 2006, 12:43 PM

this thread was abandoned 3 years ago

"The one language all programmers understand is profanity."

bettsy · January 21st, 2007, 04:21 PM

Hi I am having the same problem, with ASP paging and an MS Access Database when I use the SELECT FROM WHEN command.
If I use "SELECT * FROM query"
I get my whole list and my paging works fine, however I want to use 3 different choices from an HTML form and use the "SELECT FROM WHERE" statement, but when I do this, I get the first page fine with the list of pages to click on, but when I click onto page 2, I get my recordset EOF message, even though the URL shows:
http://localhost/accessory/prodsearch.asp?PageNo=2

The problem seems to be around the SQL statement, can anyone help?

rstelma · January 22nd, 2007, 03:07 PM

Hi,

I was able to duplicate you're original error in my paging script. I moved the SQL statement below the part of my script where you're doing this:

rsData.PageSize = 5
rsData.CursorLocation = adUseClient

You also may have some other problems with that script but for now I'll just send my entire script. You'll of course have to change the SQL statement and then the table tags. I've been using it for years.

Code:

<form name="form" action="pagescript.asp" method="post">
<input type="hidden" name="CurrentPage" />
<table border="0" width="100%" cellpadding="4" cellspacing="1" align="center" bgcolor="#ABABAB">
<%
    Set oConn = Server.CreateObject("ADODB.Connection")
    oConn.Open Application("conn")
    Set oRS = Server.CreateObject("ADODB.Recordset")



    oRS.CursorLocation = 3
    oRS.PageSize = 25
    oRS.open SQL,oConn, 3, 3
    PageCount = oRS.PageCount
    thecounter = oRS.recordcount
    CurrentPage = Request("CurrentPage") 

    SQL = "SELECT  TVAPrecision.TVAPrecID, TVAPrecision.TVAID, TVAPrecision.TVAPrecDate, TVAPrecision.EmpIDTester, TVA.TVAName, Employees.EmpFirst, Employees.EmpLast FROM TVAPrecision INNER JOIN TVA ON TVAPrecision.TVAID = TVA.TVAID INNER JOIN Employees ON TVAPrecision.EmpID = Employees.EmpID"
    If oRS.recordcount > 0 Then
        If CurrentPage = "" Then
            CurrentPage = 1
        End If
    oRS.absolutepage = CurrentPage
    End If
    If pagecount > 1 Then    
%>
    <tr bgcolor="#FFFFFF">
        <td colspan="5" class="aeiheadsm">
            <table border="0" width="100%" cellpadding="0" cellspacing="1" bgcolor="#ABABAB">
                <tr>
                    <td align="center" class="aeiheadsm" nowrap>
<%
        Response.Write "<a href='javascript:GoToPage(1)' class=aeilink>First</a>"
        If CurrentPage <> 1 Then
            Response.Write "&nbsp;&nbsp;<a href='javascript:GoToPage(" & currentpage - 1 & ")' class=aeilink><<<</a>"
        Else
            Response.Write "&nbsp;&nbsp;<<<"
        End If
        For pagenumber = 1 To pagecount
            If pagenumber = CInt(CurrentPage) Then 
                Response.Write "&nbsp;&nbsp;" & pagenumber & "&nbsp;&nbsp;"
            ElseIf  pagenumber = Cint(currentpage) - 2 or pagenumber = Cint(currentpage) - 1 or pagenumber = Cint(currentpage) + 1 or pagenumber = Cint(currentpage) + 2 or pagenumber = Cint(currentpage) + 3 or pagenumber = Cint(currentpage) + 4 Then
                Response.Write "<A HREF='javascript:GoToPage(" & pagenumber & ")' class='aeilink'>&nbsp;&nbsp;" & pagenumber & "&nbsp;&nbsp;</A>"
            End If 
        Next
        If Cint(CurrentPage) <> Cint(oRS.PageCount) Then
            Response.Write "<a href='javascript:GoToPage(" & CurrentPage + 1 & ")' class=aeilink>>>></a>&nbsp;&nbsp;"
        Else
            Response.Write ">>>&nbsp;&nbsp;"
        End If
    Response.Write "<a href='javascript:GoToPage(" & oRS.pagecount & ")' class=aeilink>Last</a>"
%>
                    </td>
                </tr>
            </table>
        </td>
    </tr>
<%
    End If
%>
    <tr>
        <td class="aeiheadsm">
            Sniffer<br />
        </td>
        <td class="aeiheadsm">
            Date<br />
        </td>
        <td class="aeiheadsm">
            Employee<br />
        </td>
        <td width="100%" class="aeiheadsm">
            Tester<br />
        </td>        
        <td align="center" class="aeiheadsm">
            Print<br />
        </td>
    </tr>
<%
        Count = 0
    If not oRS.EOF Then
        DO UNTIL oRS.EOF OR oRS.AbsolutePage <> CInt(CurrentPage)
        SQL2 = "Select EmpFirst AS TesterEmpFirst, EmpLast AS TesterEmpLast from Employees Where EmpID = "&oRS("EmpIDTester")&";"
        Set oRS2 = oConn.Execute(SQL2)

            If (Count MOD 2 = 0 ) Then
                RowColor = "#FFFFFF"
            Else
                RowColor = "#DDDDDD"
            End If
%>
    <tr bgcolor="<%=RowColor%>">
        <td class="aeiresults" nowrap>
            <%=oRS("TVAName")%><br />
        </td>
        <td class="aeiresults">
            <%=FormatDateTime(oRS("TVAPrecDate"), 2)%><br />
        </td>
        <td class="aeiresults" nowrap>
            <%=oRS("EmpFirst") & " " & oRS("EmpLast")%><br />
        </td>
        <td class="aeiresults" nowrap>
            <%=oRS2("TesterEmpFirst") & " " & oRS2("TesterEmpLast")%><br />
        </td>        
        <td align="center" class="aeiresults">
            <a href="cal_report_print.asp?id=<%=oRS("TVAPrecID")%>" target="cal_report"><img border="0" src="../images/icons/print.gif" width="16" height="16" /></a><br />
        </td>
    </tr>
<%
            Count = Count + 1
        oRS.MoveNext
        Loop
    End If
%>
</form>
<script>
function GoToPage(n)
{
    document.form.CurrentPage.value = n;
    document.form.submit();
}
</script>

rstelma · January 22nd, 2007, 03:32 PM

Hi,

I was able to duplicate your error by moving the SQL below your rsData declarations:

rsData.PageSize = 5
rsData.CursorLocation = adUseClient
strConn = "DSN=brewster"

I have the script below that has been working for me for years. Give it a shot.

<form name="form" action="pagescript.asp" method="post">
<input type="hidden" name="CurrentPage" />
<table border="0" width="100%" cellpadding="4" cellspacing="1" align="center" bgcolor="#ABABAB">
<%
    Set oConn = Server.CreateObject("ADODB.Connection")
    oConn.Open Application("conn")
    Set oRS = Server.CreateObject("ADODB.Recordset")
    'Put in your own SQL statement
    'Make sure to change the the recordset column values when writing to the page
    SQL = ""

    oRS.CursorLocation = 3
    oRS.PageSize = 25
    oRS.open SQL,oConn, 3, 3
    PageCount = oRS.PageCount
    thecounter = oRS.recordcount
    CurrentPage = Request("CurrentPage")

    If oRS.recordcount > 0 Then
        If CurrentPage = "" Then
            CurrentPage = 1
        End If
    oRS.absolutepage = CurrentPage
    End If
    If pagecount > 1 Then
%>
    <tr bgcolor="#FFFFFF">
        <td colspan="5" class="aeiheadsm">
            <table border="0" width="100%" cellpadding="0" cellspacing="1" bgcolor="#ABABAB">
                <tr>
                    <td align="center" class="aeiheadsm" nowrap>
<%
        Response.Write "<a href='javascript:GoToPage(1)' class=aeilink>First</a>"
        If CurrentPage <> 1 Then
            Response.Write "  <a href='javascript:GoToPage(" & currentpage - 1 & ")' class=aeilink><<<</a>"
        Else
            Response.Write "  <<<"
        End If
        For pagenumber = 1 To pagecount
            If pagenumber = CInt(CurrentPage) Then
                Response.Write "  " & pagenumber & "  "
            ElseIf pagenumber = Cint(currentpage) - 2 or pagenumber = Cint(currentpage) - 1 or pagenumber = Cint(currentpage) + 1 or pagenumber = Cint(currentpage) + 2 or pagenumber = Cint(currentpage) + 3 or pagenumber = Cint(currentpage) + 4 Then
                Response.Write "<A HREF='javascript:GoToPage(" & pagenumber & ")' class='aeilink'>  " & pagenumber & "  </A>"
            End If
        Next
        If Cint(CurrentPage) <> Cint(oRS.PageCount) Then
            Response.Write "<a href='javascript:GoToPage(" & CurrentPage + 1 & ")' class=aeilink>>>></a>  "
        Else
            Response.Write ">>>  "
        End If
    Response.Write "<a href='javascript:GoToPage(" & oRS.pagecount & ")' class=aeilink>Last</a>"
%>
                    </td>
                </tr>
            </table>
        </td>
    </tr>
<%
    End If
%>
    <tr>
        <td class="aeiheadsm">
            Sniffer<br />
        </td>
        <td class="aeiheadsm">
            Date<br />
        </td>
        <td class="aeiheadsm">
            Employee<br />
        </td>
        <td width="100%" class="aeiheadsm">
            Tester<br />
        </td>
        <td align="center" class="aeiheadsm">
            Print<br />
        </td>
    </tr>
<%
        Count = 0
    If not oRS.EOF Then
        DO UNTIL oRS.EOF OR oRS.AbsolutePage <> CInt(CurrentPage)

            If (Count MOD 2 = 0 ) Then
                RowColor = "#FFFFFF"
            Else
                RowColor = "#DDDDDD"
            End If
%>
    <tr bgcolor="<%=RowColor%>">
        <td class="aeiresults" nowrap>
            <%=oRS("TVAName")%><br />
        </td>
        <td class="aeiresults">
            <%=FormatDateTime(oRS("TVAPrecDate"), 2)%><br />
        </td>
        <td class="aeiresults" nowrap>
            <%=oRS("EmpFirst") & " " & oRS("EmpLast")%><br />
        </td>
        <td class="aeiresults" nowrap><br />
        </td>
        <td align="center" class="aeiresults">
            <a href="cal_report_print.asp?id=<%=oRS("TVAPrecID")% >" target="cal_report"><img border="0" src="../images/icons/print.gif" width="16" height="16" /></a><br />
        </td>
    </tr>
<%
            Count = Count + 1
        oRS.MoveNext
        Loop
    End If
%>
</form>
<script>
function GoToPage(n)
{
    document.form.CurrentPage.value = n;
    document.form.submit();
}
</script>

rstelma · January 22nd, 2007, 03:37 PM

Hi,

I think I can help you with this. I've put two posts here and I don't see them. Maybe they were too long. This is just a test.

Richard