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!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

problem with error ADODB.Field error '80020009' BOF or EOF

Status
Not open for further replies.
ceeleelewis

ceeleelewis

Programmer
Sep 26, 2002
45
US
My application has been fine up to this point but I have ran into an error that crashes my application everytime I try to submit a change to record. I receive the following message ...

ADODB.Field error '80020009'

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


Now I know that my dBase has the record that I'm trying to edit inside the correct table.

I just don't know if I'm using the EOF property correctly in this case?

While objRS.BOF= false
'objRS("reqFormId") = Trim(Request.Form("reqFormId"))
'objRS("reqDate") = Trim(Request.Form("reqDate"))
objRS("DNS") = Trim(Request.Form("DNS"))
objRS("pssWrd") = Trim(Request.Form("PASSWORD"))
objRS("chDksp") =Trim(Request.Form("chDksp"))
objRS("shMem_pre") = Trim(Request.Form("shMem_pre"))
objRS("dtAppsdb") = Trim(Request.Form("dtAppsdb"))
objRS("sndMrt") = Trim(Request.Form("sndMrt"))
objRS("preInstDate") = Trim(Request.Form("preInstDate"))
objRS("PreInstall_Comments") = Trim(Request.Form("PreInstall_Comments"))

objRS.Update

objRS.MoveNext

Wend

Any help would be greatly be apriciated.

 
Sort by date Sort by votes
try While NOT objRS.BOF AND objRS.EOF _________________________________________________________
for the best results to your questions: FAQ333-2924
01001111 01101110 01110000 01101110 01110100
onpnt2.gif
[/sub]
 
Upvote 0 Downvote
I tried both While NOT objRS.BOF AND objRS.EOF ... with no resolution to my issue. Should I use the while property like this...
While Not objRS.EOF

'objRS("reqFormId") = Trim(Request.Form("reqFormId"))
'objRS("reqDate") = Trim(Request.Form("reqDate"))
objRS("DNS") = Trim(Request.Form("DNS"))
objRS("pssWrd") = Trim(Request.Form("PASSWORD"))
objRS("chDksp") =Trim(Request.Form("chDksp"))
objRS("shMem_pre") = Trim(Request.Form("shMem_pre"))
objRS("dtAppsdb") = Trim(Request.Form("dtAppsdb"))
objRS("sndMrt") = Trim(Request.Form("sndMrt"))
objRS("preInstDate") = Trim(Request.Form("preInstDate"))
objRS("PreInstall_Comments") = Trim(Request.Form("PreInstall_Comments"))

objRS.Update

objRS.MoveNext

Wend
 
Upvote 0 Downvote
Out of curiosity, how will the recordset know which record to update if the id field isn't specified? (commented out)


-Tarwn 01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101
29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048
 
Upvote 0 Downvote
Good question, the reason why the objRS("reqFormId") = Trim(Request.Form("reqFormId")) is commented out is because I didn't want to pass the reqFormId along to the next table where the reqFormId also the primary key for that table. My thinking in this methof was that I didn't want to have a situation where I'll have duplicate records in a table.
 
Upvote 0 Downvote
Your problem may be because of the movenext.You check for
eof before monenext as follows:

if rsobj.eof=false then
rsobj.movenext
end if
See if it helps..
 
Upvote 0 Downvote
Forgive me if I'm wrong but should'nt it be

While not objRS.BOF and not objRS.EOF --------------------------------
If it ain't broke, don't fix it!
 
Upvote 0 Downvote
Hello,


I tried adding youR suggestion but with no positive result to my error....it seems like my cursor is lost.. I also modified your suggestion because I got the same error message on your orignal thought.. note:

your suggestion....

if rsobj.eof=false then
rsobj.movenext
end if

my change to your suggestion....

if rsobj.eof=true then
rsobj.movenext
end if

my code :

if objRS.EOF= True then
objRS.MoveFirst
While objRS.EOF = False
'objRS("reqFormId") = Trim(Request.Form("reqFormId"))
'objRS("reqDate") = Trim(Request.Form("reqDate"))
objRS("DNS") = Trim(Request.Form("DNS"))
objRS("pssWrd") = Trim(Request.Form("PASSWORD"))
objRS("chDksp") =Trim(Request.Form("chDksp"))
objRS("shMem_pre") = Trim(Request.Form("shMem_pre"))
objRS("dtAppsdb") = Trim(Request.Form("dtAppsdb"))
objRS("sndMrt") = Trim(Request.Form("sndMrt"))
objRS("preInstDate") = Trim(Request.Form("preInstDate"))
objRS("PreInstall_Comments") = Trim(Request.Form("PreInstall_Comments"))

response.write"****"

objRS.Update
Wend
'objRS.MoveNext
end if

I'm starting to wonder of this error is caused from something else other than the EOF or BOF property. I'm starting To test if data is being passed through, by writing a few "response.write" statements to show when a box is checked from the form ... a check should equal "yes"... The other wierd thing is that when I look inside the dBase (Access) I don't see the "yes" corresponding to the record/field in the table that I checked off/submitted on the web form .. That can be caused by this cursor error.. but this is getting a lot stickier than anticipated and I've read over my resources time and time again..Am I missing something here?
 
Upvote 0 Downvote
a few things.
first what cursor lock type are you setting for this connection?
second I see no addnew when you are using the update method?

try this adjusted script
if objRS.EOF then
response.write "no records"
else
objRS.AddNew
While NOT objRS.EOF
'objRS("reqFormId") = Trim(Request.Form("reqFormId"))
'objRS("reqDate") = Trim(Request.Form("reqDate"))
objRS("DNS") = Trim(Request.Form("DNS"))
objRS("pssWrd") = Trim(Request.Form("PASSWORD"))
objRS("chDksp") =Trim(Request.Form("chDksp"))
objRS("shMem_pre") = Trim(Request.Form("shMem_pre"))
objRS("dtAppsdb") = Trim(Request.Form("dtAppsdb"))
objRS("sndMrt") = Trim(Request.Form("sndMrt"))
objRS("preInstDate") = Trim(Request.Form("preInstDate"))
objRS("PreInstall_Comments") = Trim(Request.Form("PreInstall_Comments"))
response.write"****"
objRS.Update
Wend
objRS.MoveNext
end if _________________________________________________________
for the best results to your questions: FAQ333-2924
01001111 01101110 01110000 01101110 01110100
onpnt2.gif
[/sub]
 
Upvote 0 Downvote
also this doesn't really make much sense. you have one instance of form values but you are perfomring a loop?

why? _________________________________________________________
for the best results to your questions: FAQ333-2924
01001111 01101110 01110000 01101110 01110100
onpnt2.gif
[/sub]
 
Upvote 0 Downvote
alright now that I said that I'm real confused. you're inserting data into the DB. why are you worried about .EOF at all?

the last code is crap that I posted now that I realise this is odd. [lol]

you're trying to do?
if not at EOF move to first position of table
insert values of form

right? _________________________________________________________
for the best results to your questions: FAQ333-2924
01001111 01101110 01110000 01101110 01110100
onpnt2.gif
[/sub]
 
Upvote 0 Downvote
Opnt,

Yes,I'm trying to insert values from a form into a table of my dBase. The reason why I am not inserting/updating the objRS("reqFormID") is because that id is already established in the dBase table and inserting another reqFormId will cause a duplicated error being that the id(reqFormId) is a primary key. The strSQL statement's job is to match the reqFormId (existing in the dBase table) with the "Request.Querystring" object (In this case strSQL = "SELECT * FROM [cm_chcklstitem] WHERE [reqFormId]='" & Request.Querystring("PreInstall") & "'" ). In theory and in practice, this process has worked fine without any cursor properties added. This problem just came out of the blue and all of my resourses have told me different things from a server issue to an error with the cursor property. Talking about the weirdiest things... am I just missing the point here..thanx...below is my code...

Dim RScm_checklstitem


'Define SQL Query& Request.Querystring("Edit")cmdCntrgrp, Status, reqFormId
strSQL = "SELECT * FROM [cm_chcklstitem] WHERE [reqFormId]='" & Request.Querystring("PreInstall") & "'"
response.write"****"
response.write(strSQL)

'Set local recordset variable equals to RScm_checklstitem
Set objRS = Server.CreateObject("ADODB.Recordset")

'Open recordset passing the SQL to the connection object.
'Open as Static to be able to execute more Move commands in the recordset.
objRS.open strSQL, objConn, adOpenDynamic, adLockOptimistic
'response.write"-3"

'Check for errors in objConn
'subErrorCheck
'response.write"4"

'response.write"5"
If Request.Form("Update") = 1 Then
'response.write"6"

'If Request.Form("DNS") = "" Then
%>
<!--<p align=&quot;Center&quot;>
<font color=&quot;#FF0000&quot;>Error.</font><br><br> Send Monitoring Requirements Template Field Cannot Be Empty. <br/><br />
Please Check Field Then Repost Form.
<br>
<a href='#1' onClick='history.back()'>Back</a>
</p>-->
<%

'Else



'Update the database
'do while not (objRS.EOF)

'objRS.MovePrevious
'if objRS.EOF= True then
'objRS.MoveFirst
While not objRS.BOF and not objRS.EOF
objRS(&quot;reqFormId&quot;) = Trim(Request.Form(&quot;reqFormId&quot;))
'objRS(&quot;reqDate&quot;) = Trim(Request.Form(&quot;reqDate&quot;))
objRS(&quot;DNS&quot;) = Trim(Request.Form(&quot;DNS&quot;))
objRS(&quot;pssWrd&quot;) = Trim(Request.Form(&quot;PASSWORD&quot;))
objRS(&quot;chDksp&quot;) =Trim(Request.Form(&quot;chDksp&quot;))
objRS(&quot;shMem_pre&quot;) = Trim(Request.Form(&quot;shMem_pre&quot;))
objRS(&quot;dtAppsdb&quot;) = Trim(Request.Form(&quot;dtAppsdb&quot;))
objRS(&quot;sndMrt&quot;) = Trim(Request.Form(&quot;sndMrt&quot;))
objRS(&quot;preInstDate&quot;) = Trim(Request.Form(&quot;preInstDate&quot;))
objRS(&quot;PreInstall_Comments&quot;) = Trim(Request.Form(&quot;PreInstall_Comments&quot;))

response.write&quot;****&quot;

objRS.Update
Wend
'objRS.MoveNext
'end if
'
'loop
'end if
'end if

response.write&quot;****&quot;
response.write(Request.Form(&quot;DNS&quot;))
response.write&quot;****&quot;
response.write(objRS(&quot;DNS&quot;))

'Check for errors in objConn
'subErrorCheck

%>
<p align=&quot;Center&quot;>
Your update has been processed succesfully.<br><br>
<a href=&quot;CheckListInstall.asp?Install=<%=(Request.Querystring(&quot;PreInstall&quot;))%>&quot;>Proceed to Installation</a>
<br />
<br />
<a href=&quot;javascript:history.go(-2)()&quot;>Return to Open Requests</a><br />
<br />
<br />
<center>
<b>
<font face=&quot;Arial&quot; color=&quot;#ff0000&quot; size=&quot;2&quot;>
<center><a href=&quot;cmdEntryform.asp&quot;>Back To Command Center Maintaince Page</a></center>
</font>
</b>
</center>

</p>
<%

'End if

End if
%>



 
Upvote 0 Downvote
One more question, Isn't there a way for me to have the cursor move up and down the rs if the cursor reaches the eof? Seems to me if you can make a cursor type scrollable, you can create a objRS.move(scrollable)?
 
Upvote 0 Downvote
When dealing with boolean variants, you can access like this.

If NOT objRS.EOF

***OR*****

If objRS.EOF = False

Now... If the if you want to check if the condition is true, you can do this two ways:

IF objRS.EOF

***OR*****

If objRS.EOF = True
 
Upvote 0 Downvote
Hrmm here is how i do my updates, i mean my code style and i hope will help.
I'm suposing that you already opened your conexion to permit you making dinamically updates of your recordset.
Code: 
mySql=&quot;select * from mytable where myid=&quot;&myupdateid
'execute the querry and you suposed to have a recordset that updates a specified record
if objRS.RecordCount>0 then
 objRS(&quot;myfield&quot;) = Trim(Request.Form(&quot;myformfield&quot;))
 objRS.Update
 Response.Write &quot;Update Success!&quot;
else
  Response.Write &quot;Could not find the record!&quot;
end if

As for the while...wend cycle
Code: 
mySql=&quot;select * from mytable&quot;
while not rs.Eof
 'put code here
 rs.MoveNext
wend
This should work no matter the querry will return records or not.
If querry returns 0 records then both EOF and BOF are true so the while cycle wont be executed.

If querry returns 1 or then BOF is true and EOF false right after executing querry. EOF false means that we enter the cicle and if the recordset has only 1 record the next MoveNext will make EOF true

One way to see if you have or not records is to check the RecordCount but this is not available with the ForwardOnly cursor.
The general way is like:
if objRs.EOF then
Response.Write &quot;No records!&quot;
Response.End
end if
________
George, M
 
Upvote 0 Downvote
George, M

Hello,

I tried this technique and for some reason, I still get the same error:

****SELECT * FROM [cm_chcklstitem] WHERE [reqFormId]='3394548' Could Not Find The Record!****yes****
ADODB.Field error '80020009'

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

?


Now I know that my strSQL statement is working okay bacause of my &quot;response.write&quot;. My cursor is set to adLockOptimistic so it should be scrollable. I was assuming that the &quot;if then&quot; condition would help to determine if the record existed or not, but I'm still stuck at this point. Would a loop inside the &quot;if then&quot; condition help to move the cursor thats refusing to budge?

Thanks and below is code for your review....



'===============================================================================
'Edit Record
'================================================================================
Dim RScm_checklstitem


'Define SQL Query& Request.Querystring(&quot;Edit&quot;)cmdCntrgrp, Status, reqFormId
strSQL = &quot;SELECT * FROM [cm_chcklstitem] WHERE [reqFormId]='&quot; & Request.Querystring(&quot;PreInstall&quot;) & &quot;'&quot;
response.write&quot;****&quot;
response.write(strSQL)

'Set local recordset variable equals to RScm_checklstitem
Set objRS = Server.CreateObject(&quot;ADODB.Recordset&quot;)

'Open recordset passing the SQL to the connection object.
'Open as Static to be able to execute more Move commands in the recordset.
objRS.open strSQL, objConn, adOpenDynamic, adLockOptimistic
'response.write&quot;-3&quot;

'Check for errors in objConn
'subErrorCheck
'response.write&quot;4&quot;

'response.write&quot;5&quot;
If Request.Form(&quot;Update&quot;) = 1 Then
'response.write&quot;6&quot;

'If Request.Form(&quot;DNS&quot;) = &quot;&quot; Then
%>
<!--<p align=&quot;Center&quot;>
<font color=&quot;#FF0000&quot;>Error.</font><br><br> Send Monitoring Requirements Template Field Cannot Be Empty. <br/><br />
Please Check Field Then Repost Form.
<br>
<a href='#1' onClick='history.back()'>Back</a>
</p>-->
<%

'Else



'Update the database


'objRS.MovePrevious
if objRS.RecordCount>0 then

While not objRS.EOF
'objRS(&quot;reqFormId&quot;) = Trim(Request.Form(&quot;reqFormId&quot;))
'objRS(&quot;reqDate&quot;) = Trim(Request.Form(&quot;reqDate&quot;))
objRS(&quot;DNS&quot;) = Trim(Request.Form(&quot;DNS&quot;))
objRS(&quot;pssWrd&quot;) = Trim(Request.Form(&quot;PASSWORD&quot;))
objRS(&quot;chDksp&quot;) =Trim(Request.Form(&quot;chDksp&quot;))
objRS(&quot;shMem_pre&quot;) = Trim(Request.Form(&quot;shMem_pre&quot;))
objRS(&quot;dtAppsdb&quot;) = Trim(Request.Form(&quot;dtAppsdb&quot;))
objRS(&quot;sndMrt&quot;) = Trim(Request.Form(&quot;sndMrt&quot;))
objRS(&quot;preInstDate&quot;) = Trim(Request.Form(&quot;preInstDate&quot;))
objRS(&quot;PreInstall_Comments&quot;) = Trim(Request.Form(&quot;PreInstall_Comments&quot;))
objRS.Update
Wend
response.write&quot;Update Success!&quot;
else
response.write&quot;Could Not Find The Record!&quot;
end if




'Wend


response.write&quot;****&quot;
response.write(Request.Form(&quot;DNS&quot;))
response.write&quot;****&quot;
response.write(objRS(&quot;DNS&quot;))

'Check for errors in objConn
'subErrorCheck

%>
<p align=&quot;Center&quot;>
Your update has been processed succesfully.<br><br>
<a href=&quot;CheckListInstall.asp?Install=<%=(Request.Querystring(&quot;PreInstall&quot;))%>&quot;>Proceed to Installation</a>
<br />
<br />
<a href=&quot;javascript:history.go(-2)()&quot;>Return to Open Requests</a><br />
<br />
<br />
<center>
<b>
<font face=&quot;Arial&quot; color=&quot;#ff0000&quot; size=&quot;2&quot;>
<center><a href=&quot;cmdEntryform.asp&quot;>Back To Command Center Maintaince Page</a></center>
</font>
</b>
</center>

</p>
<%

'End if

End if
%>


=================================================================================================================
=======================




 
Upvote 0 Downvote
And the reqFormId field is definately a text field?

Also, and more importantly, try this and tell me what happens.

Before you do your update, close the recordset
Code: 
objRS.Close

and then open the table your trying to update
Code: 
objRS.Open &quot;tablename&quot;,connection, etc

If memoery serves me correctly, you can't do an update a Recordset that returns a partial portion of the database because the results of a query are basically a new temporary table in the db, therefore you have to either Sleect * from a table with no wheer clause, or .Open the table by table name.

-Tarwn 01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101
29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048
 
Upvote 0 Downvote
And the reqFormId field is definately a text field?

Correct,the field 'reqFormId' is a text field but it does not get updated because it is already inserted into the table 'cm_chcklstitem' and is represented as a primary key in that table with no duplicates allowed.

Also, and more importantly, try this and tell me what happens.


Before you do your update, close the recordset
objRS.Close


I tried your suggestion but I got the error message:

ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.

My question to you is there a way for me to update a table partially or should I just create new tables with the field 'reformId' being the primary key for each table?


and then open the table your trying to update
objRS.Open &quot;tablename&quot;,connection, etc
If memoery serves me correctly, you can't do an update a Recordset that returns a partial portion of the database because the results of a query are basically a new temporary table in the db, therefore you have to either Sleect * from a table with no wheer clause, or .Open the table by table name.
 
Upvote 0 Downvote
And the reqFormId field is definately a text field?

Correct,the field 'reqFormId' is a text field but it does not get updated because it is already inserted into the table 'cm_chcklstitem' and is represented as a primary key in that table with no duplicates allowed.

Also, and more importantly, try this and tell me what happens.


Before you do your update, close the recordset
objRS.Close


I tried your suggestion but I got the error message:

ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.

My question to you is there a way for me to update a table partially or should I just create new tables with the field 'reformId' being the primary key for each table?

But even if I'm forced to create new tables, I will still have to match the 'reqFormId' in order to keep my changes in line with my records I want to edit.
 
Upvote 0 Downvote
Maybe I'm missing something but from looking thru all your posts you're doing a select statement based on one prerequisite, if this is found then do some processing. I'm assuming that your select statement should only return one record (since reqFormId is a primary key), if that is so you can't move backwards or forwards thru the recordset without an error, you will get an EOF or BOF error since there is only one recordset, hence your error. So I can't see any reason for a While loop...

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
775
leifoet
leifoet
JScannell
  • Locked
  • Question
Border issue with Google Chrome on an Android phone
Replies
0
Views
554
JScannell
JScannell
Krus1972
  • Locked
  • Question
msxml3.dll error '80072f7d' - An error occurred in the secure Channel support
Replies
7
Views
1K
Krus1972
Krus1972
leifoet
  • Locked
  • Question
Using date picker gives an error message, only at application start
Replies
1
Views
584
MarkSweetland
MarkSweetland
leifoet
  • Locked
  • Question
First execute an input command and only then proceed with displaying the next record of the table
Replies
0
Views
465
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top