HELP!- ADODB.Recordset error '800a0bb9'

[ordonyez]

Hi,

I am creating Recordset paging after a search page. However, I am now getting this error on this line in my code below:
oRecordSet.Open sSQLStatement, oConnection, adOpenStatic, adLockReadOnly

The first results page works fine until I choose another page (PAGE 2) and I get the error:
ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another

Can anyone help me?

<%


'Declare variables
Dim iCurrentPage
Dim iPageSize
Dim i
Dim oConnection
Dim oRecordSet
Dim oTableField
Dim sPageURL
dim term
dim clause
dim x

'Declare constants
Const adOpenStatic = 3 'Open a RecordSet using a static cursor
Const adLockReadOnly = 1 'Open a RecordSet in read-only mode


'Retrieve the name of the current ASP document
sPageURL = Request.ServerVariables("SCRIPT_NAME")

'Retrieve the current page number from the QueryString
iCurrentPage = CINT(Request.QueryString("Page"))
If iCurrentPage = "" Or iCurrentPage = 0 Then iCurrentPage = 1

'Set the number of records to be displayed on each page
iPageSize = 8

'An ADO connection string

set oConnection=Server.CreateObject("ADODB.Connection")
oConnection.Provider="Microsoft.Jet.OLEDB.4.0"
oConnection.Open(Server.Mappath("db1.mdb"))


'Create an ADO RecordSet object
Set oRecordSet = Server.CreateObject("ADODB.Recordset")

'An SQL statement

If Len(Request.Form) > 0 Then
term = Trim(Request.Form("txtSearch"))
term = Replace(term,"'","''")

If InStr(term," ") > 0 Then
term = Split(term," ")

For x=0 to Ubound(term)

If x > 0 Then
clause = clause & " OR"
End If

clause = clause & " description LIKE '%" & term(x) & "%'"
Next
Else

clause = " description LIKE '%" & term & "%'"
End If

sSQLStatement = " WHERE " & clause
sSQLStatement = "SELECT * FROM paper WHERE" & clause & "UNION SELECT * FROM stickers WHERE" & clause
'sSQLStatement = "SELECT * FROM paper WHERE description LIKE '%paper%' OR description LIKE '%sticker%'UNION SELECT * FROM stickers WHERE description LIKE '%paper%' OR description LIKE '%sticker%'"


End If

'Set the RecordSet PageSize property
oRecordSet.PageSize = iPageSize

'Set the RecordSet CacheSize property to the
'number of records that are returned on each page of results
oRecordSet.CacheSize = iPageSize

'Open the RecordSet
oRecordSet.Open sSQLStatement, oConnection, adOpenStatic, adLockReadOnly

'Move to the selected page in the record set
oRecordSet.AbsolutePage = iCurrentPage

'DISPLAY NUMBER OF RECORDS FOUND
Response.Write("Your search has returned ")
Response.Write(oRecordSet.RecordCount)
Response.Write(" records.")


'Display the opening HTML of a table
Response.Write "<table border=""0"" width=""50%"" cellpadding=""2"" cellspacing=""0"">"
Response.Write "<tr>"

'Loop through the fields in the RecordSet and
'display a column heading for each field
For Each oTableField In oRecordSet.Fields
Response.Write "<th width=""50%"" bgcolor=""#008080"" align=""left""><font color=""#FFFFFF""><b>" & oTableField.Name & "</b></font></th>"
Next

Response.Write "</tr>"
Response.Write "<tr><td width=""50%"" bgcolor=""#C0C0C0"">"


Response.write oRecordSet.GetString(2, iPageSize, "</td><td width=""50%"" bgcolor=""#C0C0C0"">", "</td></tr><tr><td width=""50%"" bgcolor=""#C0C0C0"">", " ")

Response.Write "</td></tr></table>"


'Display a list of links to all of the other pages of results
For i = 1 to oRecordSet.PageCount

If i = CInt(iCurrentPage) Then
Response.Write "[ Page " & i & " ] "
Else
Response.Write "[ <a href=""" & sPageURL & "?Page=" & i & Chr(34) & ">Page " & i & "</a> ] "
End If

Next


'Release database connectivity objects
oRecordSet.Close
set oRecordSet = nothing
set oConnection = nothing

%>

[prototyp3]

try pasting error code in google in would come up with some results
this are some of the problems they are conflicting

Const adOpenStatic = 3 'Open a RecordSet using a static cursor
Const adLockReadOnly = 1 'Open a RecordSet in read-only mode

[Spudhead]

Response.write() your SQL statement, and post it.

[ordonyez]

SELECT * FROM paper WHERE description LIKE '%paper%' OR description LIKE '%sticker%' UNION SELECT * FROM stickers WHERE description LIKE '%paper%' OR description LIKE '%sticker%'


The weirdest part is when I put the sql statement out side of the if statement it WORKS!. However, in this context I cant hardcode my sql statement ( See Code Below) THIS IS DRIVING ME NUTS!!

If Len(Request.Form) > 0 Then
term = Trim(Request.Form("txtSearch"))
term = Replace(term,"'","''")

If InStr(term," ") > 0 Then
term = Split(term," ")

For x=0 to Ubound(term)

If x > 0 Then
clause = clause & " OR"
End If

clause = clause & " description LIKE '%" & term(x) & "%'"
Next
Else

clause = " description LIKE '%" & term & "%'"
End If

sSQLStatement = " WHERE " & clause
sSQLStatement = "SELECT * FROM paper WHERE" & clause & "UNION SELECT * FROM stickers WHERE" & clause


End If


sSQLStatement = "SELECT * FROM paper WHERE description LIKE '%paper%' OR description LIKE '%sticker%' UNION SELECT * FROM stickers WHERE description LIKE '%paper%' OR description LIKE '%sticker%'"