Forum Moderators: open
Error Type:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/socoolbrew/testca.asp, line 38
The script is as follows: I would really value help as I have been on a no brainer with this for 2 days! have pity on me!
<%@LANGUAGE="VBSCRIPT"%>
<%
Option Explicit
Response.Expires = 0
%>
<!--#include file="adovbs.inc" -->
<%
Dim StrSearchtxt
StrSearchtxt = Request.Form("cool")
Const intPageSize = 5
Dim intCurrentPage, objConn, objRSa, strQ
Dim intTotalPages, intI
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
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=brewster"
Set objRSa = Server.CreateObject("ADODB.RecordSet")
objRSa.CursorLocation = adUseClient
objRSa.CursorType = adOpenStatic
objRSa.CacheSize = intPageSize
strQ = "SELECT title, rating FROM review WHERE title = '" &StrSearchtxt & "'"
objRSa.Open strQ, objConn, , ,adCmdText <--ERROR POSS HERE?
objRSa.PageSize = intPageSize
If (Not objRSa.EOF) Then objRSa.AbsolutePage = intCurrentPage
intTotalPages = objRSa.PageCount
%>
And this IS embedded further into the script with some HTML
<%
For intI = 1 To objRSa.PageSize
Response.Write objRSa("title")
Response.Write objRSa("rating")
'Response.Write Server.HTMLEncode(objRS("title") & ":" & objRS("rating")) & "<BR>"
objRSa.MoveNext
If objRSa.EOF Then Exit For
Next
objRSa.Close : objConn.Close
Set objRSa = Nothing : Set objConn = Nothing
%>
<BR>
Page <%= intCurrentPage %> of <%= intTotalPages %><P><FORM ACTION="<%= Request.ServerVariables("SCRIPT_NAME") %>" METHOD="POST">
<INPUT TYPE="Hidden" Name="CurrentPage"
VALUE="<%= intCurrentPage%>">
<%If intCurrentPage > 1 Then %>
<INPUT TYPE="Submit" Name="Submit" VALUE="Previous">
<% End If
If intCurrentPage <> intTotalPages Then %>
<INPUT TYPE="Submit" NAME="Submit" VALUE="Next">
<% End If %>
</FORM>
Sometimes you get this error using the recordset method. Try using the connection method instead, If it works ok you can at least rule out problems with the SQL.
set objRSa = objCon.Execute (sql)
However, since then then I have rewritten the script and got the SQL statement to work and now have the follwing error for the following code.
On running the script all records are displayed for the first page, but then shows an error an BOF / EOF error should I attempt to load the next page.
To stop the end of file error I have now included an IF statement to stop such errors, but how can I get it to move to the next page.
The error message was:
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/testcab.asp, line 61
Line 61 being: rsData.AbsolutePage = intPage
Which has been replaced with:
MyPageSize = 10
on the recommendation of Smokin but still no joy :-(
<%@LANGUAGE="VBSCRIPT"%>
<html><title></title>
<%
'Option Explicit
'Response.Expires = 0
%>
<!--#include file="adovbs.inc" -->
<%
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
Dim MyPageSize
StrQuote = Chr(34) ' The double quote character
Set rsData = Server.CreateObject("ADODB.Recordset")
' Setting the page size
MyPageSize = 10
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
' 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 MyPageSize
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")
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>
Anyway will let the code speak for itself:
<%@ Language=VBScript %>
<HTML>
<HEAD>
<TITLE></TITLE>
</HEAD>
<BODY>
<%
Dim strSearchtxt
strSearchtxt = Request("cool") ' Get text to define SQL filter search
Dim Con
Dim rsPage
Dim Page
Dim RowCount
Dim PageCounter
Dim strQuote
'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 "brewster"
'Set the number of records in each page to 10
rsPage.PageSize = 5
'Open recordset
rsPage.Open lsSQL, Con,3,3
rsPage.AbsolutePage = cInt(Page)
if not rsPage.eof or rsPage.bof 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>"
For PageCounter = 1 to rsPage.PageCount
%>
<a href="fiveon.asp?Page=<%=PageCounter%>&cool=<%=request("cool")%>"><%=PageCounter%></a>
<%
Next
rsPage.Close
set rsPage = Nothing
End if
%>
</body>
</html>
