ADODB.Recordset (0x800A0BB9) error?

Need help to resolve ASP script error

[socoolbrewster]

Any help is very much appreciated. I am developing a FREE fan based movie site called SoCoolBrewster. All the site features are ready apart from one page. Basically the following script accepts a form variable to enable a search using SQL. Would love to use SQL Server but am having to make do with MS Access 2000 database. Anyway, on running the script the following error occurs:

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>

[gangstah]

Would setting the page size BEFORE opening the recordset help?

[socoolbrewster]

I have already declared the page size as a CONSTANT at top of page. I seem to get the fault when using the SQL statement whereby one of the values of the SQL statement is a variable that is requested by the script into the form.

If for example I replace the SQL statement with one like this: SELECT * FROM review

The script works fine. But if I use the script with the above SQL statment it doesnt and gives me that error.

Any other thoughts?

[bmcgee]

Before you execute the statement, do a:
Response.Write "SELECT title, rating FROM review WHERE title = '" &StrSearchtxt & "'"

Then get back to us with what comes out. It may be that you have ticks in your StrSearchtxt variable, or that text could be too long. Many options here.

Let's take the first step and see exactly what SQL is executing(failing?).

[socoolbrewster]

I have excuted the statement as requested and this is the output I get:

SELECT title, rating FROM review WHERE title = 'terminator 3'

There is an entry in the database call for terminator 3 and the field for title in the Access database is a TEXT field with a maximum lenght of 50.

All I know up to know is if I remove this SQL statement and replace with one that doesnt accept a variable from a form the script works fine. It's real puzzle to me as I would of thought it would work.

[smokin]

socoolbrewster try:
objRSa.Open strQ, objConn, 3, 1

[Staffa]

strQ = "SELECT title, rating FROM review WHERE title = '" &StrSearchtxt & "'"

Try and replace with :

strQ = "SELECT title, rating FROM review WHERE title = " & StrSearchtxt

I find that usually the answer lies in simplicity ;o)

[socoolbrewster]

I have just tried Staffa's suggestion and now get the following message:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'title = terminator 3'.

[socoolbrewster]

Smokin when I try you're suggestion I get the following message:

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 36

Line 36 in my code is:

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

Any further ideas guys? I am sure I have done something really stupid but cant seem to put my finger on it.

[gangstah]

If your title is a string then enclose it in quotes: ie

strQ = "SELECT title, rating FROM review WHERE title = '" & StrSearchtxt & "'"

[socoolbrewster]

Hi Guys really value suggestions so keep them coming. Just attempted the last suggestion and still nothing. I still keep getting the darn error and am feeling such a dumb-ass for not knowing what's wrong.

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 37

[CodeSquad]

Change
StrSearchtxt = Request.Form("cool")
to
StrSearchtxt = CStr(Request.Form("cool"))

[smokin]

I don't think your sql is the problem. I think it may have something to do with this:

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

Try taking out all the paging stuff and see what happens.

[socoolbrewster]

Thanks Smokin you might be right. The thing is I can't understand why if I use a simply SQL statement like:

SELECT title FROM review

All the the paging code works fine, but as soon as I replace the simplay SQL statement with the one I am attempting to use it causes errors?

I'm sure I am doing something stupid but I can't workout what? LOL

How else might I attempt paging using Access?

[smokin]

I have a script you can use. I will sticky you it sometime today as I am off to a meeting.

smokin

[socoolbrewster]

Also guy's I am from an ASP.NET background and have been developing this site in classic ASP (ASP 3.0) due to hosting costs, so please have pitty on me! The last time I used ASP was in the final year at University and that was almost 4 years ago now.

I have got all the other pages for this site working using classic ASP and this is the only page I cant get to work :-(

[socoolbrewster]

Cheers smokin you are a star!

[socoolbrewster]

Although the offer a script is most welcomed. I would still welcome any other suggestions to cure the fault with my code otherwise it will continue to bug me! LOL

Any ideas guys?

[Staffa]

Hi again,

I think the problem lies with the text from your request.form consisting of 'text-space-number'
it's therefore neither a string or an integral.

May I suggest to try my previous posted "Select.... " again
and change at the top of your page :

StrSearchtxt = Request.Form("cool")
to
StrSearchtxt = Server.HTMLEncode(Request.Form("cool"))

[socoolbrewster]

Hi Staffa thanks for advise sadly tried it and still no joy. I think overall I havent helped with the problem by having a poor program structure.

[defanjos]

Try this:
strQ = "SELECT title, rating FROM review WHERE title = " & StrSearchtxt & "

Instead of this:
strQ = "SELECT title, rating FROM review WHERE title = '" & StrSearchtxt & " '

Notice, I removed the -->'<--

[WebDawg]

The quotes are not matched.

replace
WHERE title = '" &StrSearchtxt & "'"
by
WHERE title = "'" & StrSearchtxt & "'"

Pace, dawg!

[socoolbrewster]

On trying WebDawg suggestion I get this error message:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'title ='.
/socoolbrew/testd.asp, line 33

This script is turning into my worst nightmare! LOL

[socoolbrewster]

On trying Dafanjos suggestion I get this error message:

Error Type:
Microsoft VBScript compilation (0x800A0409)
Unterminated string constant
/socoolbrew/testd.asp, line 32, column 75
strQ = "SELECT title, rating FROM review WHERE title = " & StrSearchtxt & "
--------------------------------------------------------------------------^

I think I might have to give up guys and maybe start a new script from the start and see if I get lucky.

[socoolbrewster]

I have decided to have another attempt at a script and have coded the following this afternoon.

I can now get the script to execute statment based on an variable requested from a form. But on loading it as soon as I click the next page link to view further records I get the following message:

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

Having spent a few hours on this script I really thought I had it this time! LOL

The new code is as follows:

<%@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
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 objConn = Server.CreateObject("ADODB.Connection")
'objConn.Open "DSN=brewster"
Set rsData = Server.CreateObject("ADODB.Recordset")

' Setting the page size

rsData.PageSize = 5
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, adOpenForwardOnly, adLockReadOnly, adCmdText
%>

<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 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")
Response.Write "&nbsp;<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 "&nbsp;<SPAN>Previous Page</SPAN>"
Else
Response.Write "&nbsp;<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 "&nbsp;<SPAN>Next Page</SPAN>"
Else
Response.Write "&nbsp;<A HREF=" & strQuote & strScriptName & "?PAGE=" & intPage + 1 & strQuote & ">Next Page</A>"
End If

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

' Dispose of all objects & connections

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

[smokin]

Hey socoolbrewster, Ive sent you some code that will work

[socoolbrewster]

Cheers smokin will check it out. Although I have to say that I was hoping that my new code (as above) would work, as I was just trying to see if I could use my own code to work as I love a challenge and am not a quiter (well ok sometimes I might be LOL)

Basicly my problem is that I am in ASP.NET mode at the moment and am in the process of developing a website at work. In my own time have been developing a free movie website called socoolbrewster.com which the above code is from using classic ASP.

While I think about any you guys know the best place to get a site SUBMITTED free of charge to many of the leading search engines like Google, MSN etc... And have them submitted ASAP?

Also another problem I have had is when a user submits a review using a form if they include a ' or " characters it causes my INSERT SQL statment into an error Anyway I can work round this?

Cheers for all the help guys really appreciate it

[socoolbrewster]

Ok guys have decided to use the new script I developed and with the suggestions of Smokin have embedded some additional IF statements to stop the script from crashing. My current script works with the SQL statment but will only display records for the first page. Should there be more records the script would state that it has come to end of file and when attempting to load the next page. I have now included an IF statement to stop the error, but what am I doing wrong, Any ideas?

<%@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

StrQuote = Chr(34) ' The double quote character

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

' Setting the page size

rsData.PageSize = 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 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")
Response.Write "&nbsp;<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 "&nbsp;<SPAN>Previous Page</SPAN>"
Else
Response.Write "&nbsp;<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 "&nbsp;<SPAN>Next Page</SPAN>"
Else
Response.Write "&nbsp;<A HREF=" & strQuote & strScriptName & "?PAGE=" & intPage + 1 & strQuote & ">Next Page</A>"
End If

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

' Dispose of all objects & connections

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

[smokin]

You need to replace ' with '' two apostrophes, same goes for quotes.
Replace(string, "'", "''")

[socoolbrewster]

Thanks for the tip smokin as the days progress my VBScript has been flooding back to me.