ADODB.Field error '80020009' Cannot find record

[peashoe]

I get the following error: ADODB.Field error '80020009' Either BOF or
EOF is True, or the current record has been deleted. Requested
operation requires a current record.

This is my code:
' Get if module is hand-approved or not.
query = "select *, m.Title as ModuleTitle, d.IsRanged, c.IsHandApproved
as Approval, c.ApprovalDate as ApprovalDate, ci.InstructorID as UID,
i.Email, i.FirstName, i.LastName" _
& " from ModuleTop m" _
& " inner join ClassTop c on c.ModuleID=m.ID" _
& " inner join ClassInstructors ci ON c.ID=ci.ClassID" _
& " inner join Instructors i ON ci.InstructorID=i.ID" _
& " inner join LocationTop L on L.ID=c.LocationID" _
& " inner join Delivery d on c.DeliveryID=d.ID" _
& " where c.ID=" & sClassID

set rs = DBRecordset(query)

if rs("Approval")= 1 then (It stops here with the error)
sRegCode = "A"
else
sRegCode = "B"
end if

I looked in my database - and the record is set to True, so why doesn't
it see it? I also tried
if rs("Approval") = "True" and that didn't work.

Thanks in advance
Lisa

[gomer]

add

If Not rs.EOF

before you do anything.
"peashoe" <pe*****@yahoo.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...

>I get the following error: ADODB.Field error '80020009' Either BOF or
EOF is True, or the current record has been deleted. Requested
operation requires a current record.

This is my code:
' Get if module is hand-approved or not.
query = "select *, m.Title as ModuleTitle, d.IsRanged, c.IsHandApproved
as Approval, c.ApprovalDate as ApprovalDate, ci.InstructorID as UID,
i.Email, i.FirstName, i.LastName" _
& " from ModuleTop m" _
& " inner join ClassTop c on c.ModuleID=m.ID" _
& " inner join ClassInstructors ci ON c.ID=ci.ClassID" _
& " inner join Instructors i ON ci.InstructorID=i.ID" _
& " inner join LocationTop L on L.ID=c.LocationID" _
& " inner join Delivery d on c.DeliveryID=d.ID" _
& " where c.ID=" & sClassID

set rs = DBRecordset(query)

if rs("Approval")= 1 then (It stops here with the error)
sRegCode = "A"
else
sRegCode = "B"
end if

I looked in my database - and the record is set to True, so why doesn't
it see it? I also tried
if rs("Approval") = "True" and that didn't work.

Thanks in advance
Lisa

[Bob Barrows [MVP]]

peashoe wrote:

I get the following error: ADODB.Field error '80020009' Either BOF or
EOF is True, or the current record has been deleted. Requested
operation requires a current record.

This is my code:
' Get if module is hand-approved or not.
query = "select *, m.Title as ModuleTitle, d.IsRanged,
c.IsHandApproved as Approval, c.ApprovalDate as ApprovalDate,
ci.InstructorID as UID, i.Email, i.FirstName, i.LastName" _
& " from ModuleTop m" _
& " inner join ClassTop c on c.ModuleID=m.ID" _
& " inner join ClassInstructors ci ON c.ID=ci.ClassID" _
& " inner join Instructors i ON ci.InstructorID=i.ID" _
& " inner join LocationTop L on L.ID=c.LocationID" _
& " inner join Delivery d on c.DeliveryID=d.ID" _
& " where c.ID=" & sClassID

Response.Write query
Response.End

Run the page, copy the query from the browser window and run it in the
query execution tool for your database. Do you get the correct results?
If not, change the sql so that it gives you the correct result. Now you
know what it has to look like. Go back to your asp page and modify the
code so that it generates the sql that works.

In the future, raising exceptions (errors) is expensive - never try to
read data from a recordset without checking its EOF property:

If not rs.EOF then
'process the data
else
'handle the no-data situation
end if
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[peashoe]

I guess I should have explained a bit more - I have one record that is
True and one that is False. For some reason, when I do a
response.write(rs("Approval")) - the False record shows, but the True
record gives the error. What would cause this?
gomer wrote:

add

If Not rs.EOF

before you do anything.

[peashoe]

thanks everyone for your help - but I figured out the issue. It had
nothing to do with the approval - it was the fact that my query was
looking for instructors, and the one that wasn't working didn't have an
instructor - therefore rs.EOF was true.

Thanks guys!
~L~