ASP EOF Error

[taurang]

I experienced an error when doing recordset paging of search results. When the search term is blank or in the database, it work's fine. The problem occurs when it's not in the database and search term is not blank. The error message is as follows:

ADODB.Recordset error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/anandatravel/candystore/MPSearch.asp, line 101

I'm using adOpenStatic cursor type because only this supports paging. I suspect that when search term is not blank and cannot be found, EOF is true. Is this the cause of the error? How do I solve this problem? Thanks in advance.



<%
cat = TRIM( Request( "cat" ) )
IF cat = "" THEN cat = "Home"
searchFor = TRIM( Request( "searchFor" ) )
Set Con = Server.CreateObject( "ADODB.Connection" )
Con.Open(conStr)
%>
<%
' Get the Current Page
pg = TRIM( Request( "pg" ) )
IF pg = "" THEN pg = 1

sqlString = "SELECT product_id, product_name, product_briefDesc " &_
"FROM Products " &_
"WHERE product_status = 1 " &_
"AND ( product_name LIKE '%" & searchFor & "%' " &_
"OR product_briefDesc LIKE '%" & searchFor & "%') " &_
"ORDER BY product_name "

Set RS = Server.CreateObject( "ADODB.Recordset" )
RS.CursorType = adOpenStatic
RS.ActiveConnection = Con
RS.PageSize = 8

RS.Open sqlString
RS.AbsolutePage = pg
%>

<% IF NOT RS.EOF AND searchFor <> "" THEN %>
<table width="400" border="0" cellpadding="4" cellspacing="0">
<tr>
<td>
<h3>Search Results:<h3>
</td>
</tr>
<% WHILE NOT RS.EOF AND rowCount < RS.PageSize
rowCount = rowCount + 1
%>
<tr>
<td>
<a href="product.asp?pid=<%=RS( "product_id" )%>">
<%=RS( "product_name" )%></a>

<%=RS( "product_briefDesc" )%>

<a href="product.asp?pid=<%=RS( "product_id" )%>">
get more information</a>
</td>
</tr>
<tr>
<td></td>
</tr>
<%
RS.MoveNext
WEND
%>

<% IF RS.PageCount > 1 THEN %>
<tr>
<td>
Go to page:
<%
FOR i = 1 to RS.PageCount
IF i <> cINT( pg ) THEN
%>
<a href="default.asp?cat=<%=cat%>&pg=<%=i%>">
<%=i%></a>
<% ELSE %>
<%=i%>
<% END IF %>
<% NEXT %>
</font>
</td>
</tr>
<% END IF %>
</table>

<% ELSE %>
<table width="400" border="0" cellpadding="4" cellspacing="0">
<tr>
<td>
<h3>Search Results:h3>
</td>
</tr>
<tr>
<td>No products matched your search
terms.</td>
</tr>
</table>
<% END IF %>

[Stevo]

Hi there,

Best i could come up with is possible prob. with the lines:


<% WHILE NOT RS.EOF AND rowCount < RS.PageSize

and


<% IF NOT RS.EOF AND searchFor <> "" THEN %>

Not sure - but maybe try using some brackets (I always do in those situations!) eg.

<% IF (NOT RS.EOF) AND (searchFor <> "") THEN %>

That may be how an EOF RS is slipping through the checking. Otherwise, if doing that for both lines above doesn't work - perhaps post which line is generating the error?

Hope this helps,
Steve

[swstyles]

Its hard to see where your error is if we don't know which line is 101.

[taurang]

Hi. I've tried to put them in brackets, but the same error occurred. Line 101 is the following line:
RS.AbsolutePage = pg
HTH.

[Stevo]

Hmmm... Yeah, the error is before the EOF logic so i guess its no surprise bracket dont fix it! ;) I guess another option is to move the error line below the first EOF check so that it wont call that error if the recordset is empty (which is logical - there is no point counting pages if there are no records).

Hope this helps...
Cheers,
Steve

[ravensloft]

I presume that you have already been here: http://support.microsoft.com/default.aspx?scid=kb;en-us;230101

I had that problem before and this micsoft 'solution' solved it.

[deltatrend]

taurang,

Yep, sounds like you have an empty recordset, which means RS.EOF AND RS.BOF, so try this:



IF NOT RS.EOF AND RS.BOF THEN

[taurang]

Yes, I've read that article in the MS Knoweledge Base. I doubt it's due to memory leaks.

I've moved the error line to just above the While loop and hey presto! It works. Thanks Stevo. :D

[tertius]

taurang,

In practice I find it helps to parse for EOF after running the SQL statement before doing the rest of the IF code (make your error check code the IF and leave your asp processing code for the ELSE), such as:

<initial page HTML, Connection code, etc...>
set rs = Server.CreateObject("ADODB.Recordset")
Conn.Execute returns a Recordset Object.
set rs = Conn.Execute(sqlstmt)
' Note:I use the IF..THEN loop to display the 'no records found' message first, code is simpler.

IF rs.EOF then%>
<p align="center"><font face="Arial"><your displayed no data message here></font></p>


</p>
<%ELSE%>
<rest of code/HTML follows>

Another thing I noticed that would slightly help server parsing efficiency is to fix the places where you close your asp then open it, with no HTML in between.... Each %> then <% requires a server call to process end of asp code section and beginning of asp code section, so if you eliminate the %> and <% where you have nothing in between it will cumulatively help a wee bit in server processing time and page display time--especially noticeable when the server load nears capacity!

For example, your code:

Con.Open(conStr)
%>
<%
' Get the Current Page

could be coded as:

Con.Open(conStr)

' Get the Current Page

same with this:

<%=i%>
<% ELSE %>
<%=i%>
<% END IF %>
<% NEXT %>

could be:

<%=i%>
<% ELSE %>
<%=i%>
<% END IF
NEXT %>

(I think I saw 4 occurrences of the %> followed by a <% without HTML in between...)

HTH!

-Tertius

[Stevo]

Glad I could help be of help!

Steve

[taurang]

Hi tertius. I put the EOF in the IF statement and something is wrong. For example, when I type "choco" as the search term, there are 2 pages. The 1st page is ok. When I click on page 2, it displays the homepage not the search results. The code is as follows:

<% IF RS.EOF OR searchFor = "" THEN %>
.
.
<% ELSE %>
.
.
<% END IF %>

Everything else remains the same. Thanks.