ASP Paging Problem using SQL

socoolbrewster · August 5th, 2003, 08:56 AM

I am having problems using ASP paging to view data in an MS Access 2000 database. The criteria for data to be viewed is based on parameter based SQL statment whereby the value of one the attributes in the SQL statement is vale passed from a form.

On running the script I get the following error:

ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/socoolbrew/testd.asp, line 31

I have spent a number of days trying to figure it out and have tried a number of possible soultions as outlined in Professional Active Server Pages 3.0 and still no joy.

I have checked everyline of code and have concluded that the fault lies with the SQL statement. But I am still none the wiser in fidning out why. What I do know is that if I do not acceot a value from a form and make set the embedded variable strSearchtxt to a value like this:

strSearchtxt ="Termintor 3"

The script works fine. I would really welcome any solutions to this problem. The code is follows:

<%@LANGUAGE="VBSCRIPT"%>
<html><title></title>
<%
'Option Explicit
'Response.Expires = 0
%>

<%
Dim strSearchtxt
strSearchtxt = Request.Form("cool") ' Get text to define SQL filter search
'Should a user try and submit a blank field for a search
'If StrSearchtxt = "" Then
' Response.Redirect("index.html")
'End If

Dim rsData
Dim intPage
Dim intTotalPages
Dim fldF
Dim intRec
Dim strQuote
Dim strScriptName
Dim strQ
Dim strConn

strQuote = Chr(34) ' The double quote character

Set rsData = Server.CreateObject("ADODB.Recordset")

' Setting the page size

rsData.PageSize = 5
rsData.CursorLocation = adUseClient

'rsData.CursorLocation = adUseClient

' Define connection string to database

strConn = "DSN=brewster"

' Open the recordset and define SQL statement
strQ = "SELECT * FROM review WHERE title = '" & strSearchtxt & "'"
rsData.Open strQ, strConn, 3, 1
%>
<%
' Count how many records obtained from database if 0 then redirect script to prevent error
'Dim strRecordsCount
'strRecordsCount = rsData.RecordCount
'If strRecordsCount = 0 Then
' rsData.Close
' Set rsData = Nothing
' Response.Redirect("index.html")
'End If

%>

<body>

<%
If Request.QueryString("PAGE") = "" Then
    intPage = 1
Else
    ' Protect against out of range pages, in case of a user specified page number
    If intPage < 1 Then
        intPage = 1
    Else
        If intPage > rsData.PageCount Then
            intPage = rsData.PageCount
        Else
            intPage = CInt(Request.QueryString("PAGE"))
        End If
    End If
End If

' Set the absolute page number to the requested page

rsData.AbsolutePage = intPage

'If NOT (strRecordsCount = 0) Then ' record count
'rsData.PageSize = MyPageSize
'absolutePage is the page to be displayed - don't change it:
'it depends on the user's choice

'rsData.AbsolutePage = intPage
'End If

' Start building the table

Response.Write "<TABLE BORDER=1><THEAD<TR>"
For Each fldF In rsData.Fields
  Response.Write "<TD>" & fldF.Name & "</TD>"
Next
Response.Write "</TR></THEAD><TBODY>"

'Looping through

For intRec = 1 To rsData.PageSize
    If Not rsData.EOF Then
        Response.Write "<TR>"
        For Each fldF In rsData.Fields
            Response.Write "<TD>" & fldF.Value & "</TD>"
        Next
        Response.Write "</TR>"
        rsData.MoveNext
    End If
Next
Response.Write "</TBODY></THEAD></TABLE><P>"

'Now some paging controls

'strScriptName = Request.ServerVariables("SCRIPT_NAME")
strScriptName ="testcab.asp"
Response.Write " <A HREF= " & strQuote & strScriptName & "?PAGE=1" & strQuote & ">First Page</A>"

'Only give an active previous page if there are previous pages

'If intPage = 1 Then
' Response.Write " <SPAN>Previous Page</SPAN>"
'Else
    'Response.Write " <A HREF=" & strQuote & strScriptName & "?PAGE=" & intPage - 1 & strQuote & ">Previous Page</A>"
'End If

'Only give an active next page if there are more pages
If intPage = rsData.PageCount Then
   Response.Write " <SPAN>Next Page</SPAN>"
Else
    Response.Write " <A HREF=" & strQuote & strScriptName & "?PAGE=" & intPage + 1 & strQuote & ">Next Page</A>"
End If

'Response.Write " <A HREF=" & strQuote & strScriptName & "?PAGE=" & rsData.PageCount & strQuote & ">Last Page</A>"

' Dispose of all objects & connections

rsData.Close
Set rsData = Nothing
%>
</html>

I have been testing the script using the following basic HTML form:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<form action="testd.asp" method="post">Enter film title: <input type="text" size="25" name="cool" maxlength="40"><input type="submit" value="search"></form>
</body>
</html>

Gaz

chintu · August 5th, 2003, 10:44 AM

I have 2 questions:

I see strConn is the connection string, but i dont see it being Set or declared, and what is strSearchtxt declared as in the db? let me know.

Chints

socoolbrewster · August 5th, 2003, 11:29 AM

Over the page few days I have tried a number of paging script variations and most suffer from the same common problem liek the one above: that of when loading the second page of records an EOF or BOF is displayed. I have now abandoned all other scripts(including the one above) as one stated below is was I know best.

However the stated problem is generated for this script also and the same HTML page with a form is used.

I shoudl point out that my database connection is that of an Access 2000 database which is refered to using a DSN link stated in code as follows:

DSN="brewster"

On reviewing my script what I didn't realise was that if my script has to keep calling itself doesn't that mean that the value passed from the form will become a NULL value therefore passing strSearchtxt = NULL?

Having read Professional Active Server Pages 3.0 from cover to cover I thought I would have it sorted and have since constructed a simple script with the problem stated above of:

Error Type:
ADODB.Recordset (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/socoolbrew/fiveon.asp, line 45

Might creating a session variable be the answer? and if so how?
<%@ Language=VBScript %>
<HTML>
<HEAD>
<TITLE></TITLE>
</HEAD>
<BODY>

<%

Dim strSearchtxt
strSearchtxt = Request.Form("cool") ' Get text to define SQL filter search
Dim Con
Dim rsPage
Dim Page
Dim RowCount
Dim PageCounter

'Get the Current page
Page = Request.QueryString("Page")

'If there is no page set it to page 1
If Page = "" then Page = 1

RowCount = 0

set con = server.CreateObject("ADODB.Connection")
Set rsPage = Server.CreateObject("ADODB.Recordset")

lsSQL = "SELECT * FROM review WHERE title = '" & strSearchtxt & "'"

Con.Open "DSN=brewster"

'Need a rich cursor type to support paging
rsPage.CursorType = 3 'adOpenStatic

'Set the number of records in each page to 10
rsPage.PageSize = 5

'Open recordset
rsPage.Open lsSQL, Con

'Set the current page based on the QueryString value
'Must cast it as an integer or else it will have problems.
rsPage.AbsolutePage = cInt(Page)

If Response.IsClientConnected = true then
Response.Write "<TABLE>"

'Loop though each of the records and break out when we
'have reached the max for this page
Do while not rsPage.eof and RowCount < rsPage.PageSize

'Write out content
Response.Write "<tr><td>" & rsPage("title") & "</td></tr>"

rsPage.Movenext
RowCount = RowCount + 1
Loop

Response.Write "</TABLE><p>"

'Page to determine the AbsolutePage to display.
For PageCounter = 1 to rsPage.PageCount
Response.Write "<a href='fiveon.asp?Page=" & PageCounter & "'>" & _
PageCounter & "</a> "
Next

rsPage.Close
set rsPage = Nothing
End if

%>

</body>
</html>

Gaz

socoolbrewster · August 5th, 2003, 11:42 AM

Basically what I am trying to do is pass an input value from a form to the script. Then store the value obtained from the FORM which is stored in variable in the script. The stored variable "strSearchtxt" then becomes a parameter for the given SQL statement which is executed and then displays the records for the SQL statement.

As stated the only problem is it only works for the first page, on attempting to load the second page the state error occurs.

The script is for a personal movie website which I have been developing. All my other scripting pages work fine and its just this daft error that's stopping me from finishing the site. The purpose of this script will be to allow users to do a search on a given movie review stored in the database (which are submitted by users also). Due to the layout and design of the site I want to limit the number of records found for a given search to 5 per page. Which is easier said than done as this problem is becoming my scripting nightmare!!! I am clueless and have tried everything in the book to resolve the problem.

I know that the paging overall works as if I dont accept a vaule from a form and set the value of the parameter within the SQL statement manually the paging works.

For example:

If I do this:

strSearchtxt = "Terminator 3"

The paging and SQL statement work

Forgive me for confusing anybody reading this but this is my fifth day on and off on this problem and it's beginning to do my head in.

Gaz

chintu · August 5th, 2003, 12:14 PM

I dont know if i understand the problem correctly, but this is what i understood, after sending the value from the form to the script you are passing it into the sql statement to query on the database correct?

Based on what i stated above and with what you said that you the first page is working fine, check the link of the second page.

For example
In the main page(or the first page) i see you got the strSearchtext through Request.Form.

So when you are paging, the link should be in this way:

<a href="searchpage.asp?page=pageid&searchtype=<%=req uest.form("searchtype")%>">Next</a>

because when you go to the second page, basically the whole thing is done again, like the query is looking for a input in which case is the value requested from the previous form. Based on the error you are getting, this should solve it, let me know if you have any more questions...

Chintu

socoolbrewster · August 5th, 2003, 02:31 PM

What you have stated is correct. Basically the strSearchtxt is passed to the script from a FORM where a user defines what they wish to look for.

On executing the script the first time round it works fine but when it attempts to obtain further records and load the next page thats when the error occurs. Based on what you have suggested I think you might have it!!! I shall give it ago and see if I have any joy :-) Thanks for the help I really appreicate it

Gaz

socoolbrewster · August 6th, 2003, 02:36 AM

Based on your suggestion I am now attempting to implement the line of code you have suggested. The problem I have now is with the format of the code as at present half of the code is read in Dreamweaver MX as commented out code.

Can you see what part of the code might be causing this:

Response.Write " <A HREF=" & strQuote & "fiveon.asp" & "?PAGE=" & pageid & "&fiveon.asp=" & <%=request.form("fiveon.asp")%> & strQuote">Next Page</A>"

It is ->> & strQuote">Next Page</A>" of the code that seems to comment out.

Have I got the format right?

Gaz

chintu · August 6th, 2003, 07:37 AM

Actually what is strQuote?

It should be something like this
Response.Write "<a href="fiveon.asp&page="&pageid&"&strsearchtxt="&Re quest("strsearchtext")">Nextpage</a>"

try that..

Chintu

socoolbrewster · August 6th, 2003, 08:00 AM

strQuote is a variable that holds double quote character which in the past I have used for forming statements like I am attempting rightnow.

strQuote = Chr(34) ' The double quote character

On executing the modification you have suggested I get the following error:

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/socoolbrew/fiveon.asp, line 70, column 25
Response.Write "<a href="fiveon.asp&page="&pageid&"&strSearchtxt="&Re quest("strSearchtext")">Nextpage</a>"
------------------------^

Gaz

socoolbrewster · August 6th, 2003, 08:14 AM

I have just made a modification and the following format is correct:

Response.Write "<a href='fiveon.asp&page="&PageCounter&"&strSearchtxt ="&Request("strSearchtxt") & "'>" & PageCounter & "</a>"

However, on displaying the second page an error occurs and states that the page cannot be found.

This is how the HTTP looks like for second page:

http://localhost/socoolbrew/fiveon.a...&strSearchtxt=

Gaz