No EOF or BOF, Exception error, but records are showing 1

kkinnea · Mar 12, 2003

I've researched through the forum but have not found my situation or answer.

I am trying to work with an Access 2000 database through ASP. I am running into a situation where I get records, but my recordset is showing false for both BOF and EOF. In addition, I recieve the following error:

error '80020009'
Exception occurred.

/kkinnear/mktplc.asp, line 35

Line 35 is:
<div align=center style="background-color: #000000; color: #FFFFFF;"><b><%=strCurrCategory%></b></div><br>

If someone could take a look at the following code (a "working" example is at

http://www13.brinkster.com/kkinnear/mktplc.asp):

<html>
<head>
<title>UMFA Marketplace</title>
</head>

<body>

<% Dim strConnection, rsData, strQuery, intRecords, strCurrCategory, x

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("\kkinnear\db\umfa.mdb&quot

& ";" & _
"Persist Security Info=False"

Set rsData = Server.CreateObject("ADODB.Recordset&quot

strQuery = ("SELECT Count(ID) As CatNbr FROM marketplace WHERE Active_IND = 'Y'&quot

rsData.Open strQuery, strConnection, adOpenForwardOnly

intRecords=rsData("CatNbr&quot

rsData.Close

strQuery = ("SELECT Category_Type_ID, Date_Entered, Category, Title, Text FROM marketplace WHERE Active_IND = 'Y' ORDER BY Category_Type_ID, Date_Entered DESC&quot

rsData.Open strQuery, strConnection, adOpenStatic
%>

<table border=0 width=85% align=center cellspacing=10>
<tr>
<% do until rsData.EOF %>
<% strCurrCategory=rsData("Category&quot

%>
<td valign=top width=25%>
<div align=center style="background-color: #000000; color: #FFFFFF;"><b><%=strCurrCategory%></b></div><br>
<% do until strCurrCategory <> rsData("Category&quot

%>
<div style="font-size: 8pt; text-align: justify;">
<b><%=rsData("Title&quot

%>,</b> <%=rsData("Text&quot

%></div><hr size=1>
<% rsData.MoveNext
loop %>
</td>
<% loop %>
</tr></table>

<% rsData.Close %>
<% set rsData=Nothing %>

</body>

</html>

Thanks,
Kristen

shaddow · Mar 12, 2003

in the second loop you have an uncontroled rsData.MoveNext
try to replace this line

Code:

[red]<% rsData.MoveNext[/red]
with
<% if not rsData.EOF then rsData.MoveNext

this was the error. you didnt handle the EOF on the second loop before movenext ________
George, M

kkinnea · Mar 12, 2003

Hi shaddow,

Thanks for your advice. I tried adding the EOF check to the second loop, but it did not make a difference.

One of the issues I encountered when testing this was that no matter how I changed the code, if I added <%=rsData.BOF%>,<%=rsData.EOF%> as a check into my page, it always returned False,False. This occurred even when I eliminated the second loop and only had the main piece of code looping.

Kristen

cathyw · Mar 13, 2003

Hi Kristen,
I think this line

do until strCurrCategory <> rsData("Category&quot

will generate an error if rsData is already EOF. You need to change this so that you're not doing this comparison when you get to EOF.

Hope this helps,
Cathy

kkinnea · Mar 13, 2003

Hi Cathy,

Do you have a suggestion of what I could change it to? I wanted the second loop so I could easily sort the different categories into different columns. I put that line in so I could output a new column when I reached a new category.

Thanks,
Kristen

cathyw · Mar 13, 2003

The problem is referencing rsData("Category&quot

when you could be at EOF. One way you could deal with this is to read rsData("Category&quot

into another variable (let's say strCategory) and check the variable in your do until instead of the value in the recordset (i.e. Do Until strCurrCategory <> strCategory). You would need to set this variable before going into the second loop (at this point, you could just have strCategory = strCurrCategory), and then reset it after rsData.MoveNext in the loop, making sure you check for EOF there, i.e.

rsData.MoveNext
If rsData.EOF Then
strCategory = ""
Else
strCategory = rsData("Category&quot

End If

When at EOF, I've set it to "", but you should set it to something that will never match strCurrCategory so that you drop out of the loop here.

Hope this helps,
Cathy

kkinnea · Mar 13, 2003

I tried your code as demonstrated below:

<% do until rsData.EOF %>
<% strCurrCategory=rsData("Category&quot

%>
<td valign=top width=25%>
<div align=center style="background-color: #000000; color: #FFFFFF;"><b><%=strCurrCategory%></b></div><br>
<% strCategory=rsData("Category&quot

%>
<% do until strCurrCategory <> strCategory %>
<div style="font-size: 8pt; text-align: justify;">
<b><%=rsData("Title&quot

%>,</b> <%=rsData("Text&quot

%></div><hr size=1>
<% if not rsData.EOF then rsData.MoveNext
strCategory=rsData("Category&quot

loop %>
</td>
<% loop %>

and received a new error!

ADODB.Field error '800a0bcd'

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

/kkinnear/mktplc.asp, line 39

I marked line 39 above. Any theories?

onpnt · Mar 13, 2003

try this rework
<%
do while not rsData.EOF

strCurrCategory = rsData("Category&quot

response.write"<td valign=""top"" width=""25%"""

response.write"<div align=center style=""background-color: #000000; color: #FFFFFF;"">"
response.write"<b>" & strCurrCategory & "</b></div><br>"

strCategory = rsData("Category&quot

if strCurrCategory <> strCategory then

response.write"<div style=""font-size: 8pt; text-align: justify;"">"

response.write"<b>" & rsData("Title&quot

& ",</b>" & rsData("Text&quot

& "</div><hr size=1>"

end if

rsData.MoveNext

strCategory=rsData("Category&quot

[/b]
loop
response.write"</td>"
loop
%>

I'm a bit confused on the nested loop? so I substituted it with a simple if then
is this the output you are looking for? _________________________________________________________
for the best results to your questions: FAQ333-2924
[sub]01001111 01101110 01110000 01101110 01110100[/sub]

[sup]

http://www.onpntwebdesigns.com

[/sub]

onpnt · Mar 13, 2003

get rid of that last loop, forgot to take it out _________________________________________________________
for the best results to your questions: FAQ333-2924
[sub]01001111 01101110 01110000 01101110 01110100[/sub]

[sup]

http://www.onpntwebdesigns.com

[/sub]

kkinnea · Mar 13, 2003

I found the last loop and removed it, but now my detail is gone and I got the same second error as before. You can see the results at

http://www13.brinkster.com/kkinnear/mktplc.asp

cathyw · Mar 13, 2003

Hi Kristen,

In the sample I gave you earlier, you need to move next BEFORE you check for EOF. If you check for EOF and then move next, you could be on EOF again

The entire 2nd loop would look like this:

<% do until strCurrCategory <> strCategory %>
<div style="font-size: 8pt; text-align: justify;">
<b><%=rsData("Title&quot

%>,</b> <%=rsData("Text&quot

%></div><hr size=1>
<% rsData.MoveNext
If rsData.EOF Then
strCategory = ""
Else
strCategory = rsData("Category&quot

End If
loop %>

Again, on the EOF side of the IF statement, I have set strCategory to "" assuming that this would make it not the same as strCurrCategory so you drop out of the loop. If "" is a valid value for category, you will need to change this to something that will make the condition fail.

Hope this helps,
Cathy

kkinnea · Mar 13, 2003

Cathy,

That did it!! I'm assuming what I had didn't work before because a) at some point I was trying to compare to a field that wasn't there (that is at some point rsData("Category&quot

didn't have anything to compare to and caused the exception) and b) I forgot to MoveNext before checking for EOF!

Thanks so much - I thought I had this piece of code covered because I did something similar before, but I hadn't tried the double-looping before so it was easy to miss some of the key pieces...

Kristen

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

No EOF or BOF, Exception error, but records are showing 1

kkinnea

Programmer

shaddow

Programmer

kkinnea

Programmer

cathyw

Programmer

kkinnea

Programmer

cathyw

Programmer

kkinnea

Programmer

onpnt

Programmer

onpnt

Programmer

kkinnea

Programmer

cathyw

Programmer

kkinnea

Programmer

Similar threads

Part and Inventory Search

Sponsor