Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Operation is not allowed when the object is closed.

Posted on 2009-05-19
24
Medium Priority
?
376 Views
Last Modified: 2012-08-14
Dear eXperts
I have these three tables
a) list_emails
b) list_emails_not_existing
c) list_emails_not_wanted
and want to delete the emails in b) and c) from a)
When I run the attached code I get the error:
ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.

Could y pls have a look

Thanks & B Rgds
Manfred
conn=StrAdminProvider
SqlStmt22 = "SELECT * FROM lista_emails_not_existing"
Set rs22=Server.CreateObject("ADODB.Recordset")
'response.write SqlStmt
rs22.open SqlStmt22, conn
 
if rs22.eof then
else
	do until rs22.eof
	email=rs22("email")
	response.write "Email:" & EMAIL
		SqlStmt2 = "DELETE FROM lista_emails WHERE email='" & email & "'"
		Set rs2=Server.CreateObject("ADODB.Recordset")
		response.write SqlStmt2
		rs2.open SqlStmt2, conn 
		if rs2.eof then
		recfound=1
		else
			do until rs2.eof
			response.write "Encontrado y borrado: " & email & "<br>"
			rs2.movenext
			loop
		end if
 
	rs22.movenext
	loop
end if
 
 
'---------------------------------------------not wanted emails-------------------
 
conn=StrAdminProvider
SqlStmt33 = "SELECT * FROM lista_emails_not_wanted"
Set rs33=Server.CreateObject("ADODB.Recordset")
'response.write SqlStmt
rs33.open SqlStmt33, conn
 
if rs33.eof then
else
	do until rs33.eof
	email=rs33("email")
		SqlStmt3 = "DELETE FROM lista_emails WHERE email='" & email & "'"
		Set rs3=Server.CreateObject("ADODB.Recordset")
		response.write SqlStmt3
		rs3.open SqlStmt3, conn 
		if rs3.eof then
		recfound=1
		else
			do until rs3.eof
			response.write "Encontrado y borrado: " & email & "<br>"
			rs3.movenext
			loop
		end if
 
	rs33.movenext
	loop
end if

Open in new window

0
Comment
Question by:cosmopolita
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 12
24 Comments
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 24426776
were do you get the error at?
What line? Which one?
0
 

Author Comment

by:cosmopolita
ID: 24426840
Sorry,

at line:       if rs2.eof then
and
             if rs3.eof then
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 24426893
change them to
if rs22.eof then
And
if rs33.eof then

That should correct it.

Carrzkiss
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:cosmopolita
ID: 24426987
Dear CarrzKJiss
No, it didn't.
It than shows error in line: do until rs2.eof

r22 and rs33 are the 2 outer loops
and rs2 and rs3 the 2 inner loops
0
 

Author Comment

by:cosmopolita
ID: 24427021
BTW: Nice to see you again
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 24427152
ok.
I am sorry, I totally looked that code wrong.
You have 2 different RecordSets per page.

Let me look at the code a little more.
be back with you in a few
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 24427178
the RS2 and RS3
Do they have Records in them? This can happen if there is not records to display.
0
 

Author Comment

by:cosmopolita
ID: 24427186
I have put in 2 for each  as I thought this may happen
0
 

Author Comment

by:cosmopolita
ID: 24430854
When the tables are empty there is no error.
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 24431018
This seems to work.
Give it a shot. (I had to Refresh the page twice, and it would delete the record)
Not sure if this is what you have in mind, as I am flying blind on this one.

Let me know?
Carrzkiss
<%
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("Q_24422573.mdb") & ";"
conn.Open
 
SqlStmt22 = "SELECT * FROM lista_emails_not_existing"
Set rs22=Server.CreateObject("ADODB.Recordset")
'response.write SqlStmt
rs22.open SqlStmt22, conn
 
SqlStmt23 = "SELECT * FROM lista_emails"
Set rs23=Server.CreateObject("ADODB.Recordset")
'response.write SqlStmt
rs23.open SqlStmt23, conn
 
if rs22.eof then
else
	do until rs22.eof
	
	email=rs22("email")
	if not rs23.EOF then
	if rs22("email")=rs23("email") then
	end if
	response.write "Email:" & EMAIL
		SqlStmt2 = "DELETE FROM lista_emails WHERE email='" & email & "'"
		Set rs2=Server.CreateObject("ADODB.Recordset")
		response.write SqlStmt2
		rs2.open SqlStmt2, conn 
		if rs2.eof then
		recfound=1
		else
			do until rs2.eof
			response.write "Encontrado y borrado: " & email & "<br>"
			rs2.movenext
			loop
		end if
 end if
	rs22.movenext
	loop
end if
%>

Open in new window

0
 

Author Comment

by:cosmopolita
ID: 24431158
When I replace DELETE with SELECT there
is no error either.
So, the problem seems to be the originated by
the word DELETE

I don't understand the instruction  SET NOCOUNT ON
which was the solution in the forum you've sent.
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 24431165
got rid of the error.
Runs like a charm

I made up some dummy email address
And copied them over both tables.
And added in some different ones into the other table.
And it only deleted out those that were identical in the other table.

Pretty Cool.

Have a good one.
Carrzkiss
<%
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("Q_24422573.mdb") & ";"
conn.Open
 
SqlStmt22 = "SELECT * FROM lista_emails_not_existing"
Set rs22=Server.CreateObject("ADODB.Recordset")
'response.write SqlStmt
rs22.open SqlStmt22, conn
 
SqlStmt23 = "SELECT * FROM lista_emails"
Set rs23=Server.CreateObject("ADODB.Recordset")
'response.write SqlStmt
rs23.open SqlStmt23, conn
 
if rs22.eof then
else
	do until rs22.eof
	
	email=rs22("email")
	if not rs23.EOF then
	if rs22("email")=rs23("email") then
	end if
	response.write "Email:" & EMAIL
		SqlStmt2 = "DELETE FROM lista_emails WHERE email='" & email & "'"
		Set rs2=Server.CreateObject("ADODB.Recordset")
		'response.write SqlStmt2
		rs2.open SqlStmt2, conn 
		else
		if not rs2.eof then
		recfound=1
		else
			do until rs2.eof
			response.write "Encontrado y borrado: " & email & "<br>"
			rs2.movenext
			loop
		end if
 
 end if
	rs22.movenext
	loop
end if
%>

Open in new window

0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 24431223
try my last code here and see if it will do what you need.
If not, then we will look at the Forum Link and see if that will help you.
This code that I just supplied seems to be working pretty good, and should solve your issue.

Carrzkiss
0
 

Author Comment

by:cosmopolita
ID: 24431227
Sorry, I didn't see the code above.
BUT, no it gives error at: if rs2.eof then

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

Author Comment

by:cosmopolita
ID: 24431366
Now, I have stripped of all and only left: see attached
and get the same error
ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.
at line: if rs2.eof then
                SqlStmt2 = "DELETE FROM lista_emails WHERE email='" & email & "'"
                Set rs2=Server.CreateObject("ADODB.Recordset")
                response.write SqlStmt2
                rs2.open SqlStmt2, conn 
                if rs2.eof then
                recfound=1
                else
                        do until rs2.eof
                        response.write "Encontrado y borrado: " & email & "<br>"
                        rs2.movenext
                        loop
                end if

Open in new window

0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 24431466
I am making up a demo for you.
Give me a minute.
0
 

Author Comment

by:cosmopolita
ID: 24431498
Dear Carrzkiss
Just coming back from Lunch and all seems to work.
Let me check before you do anyhting else.

Will keep you informed.

Rgds - Manfred
0
 
LVL 31

Accepted Solution

by:
Wayne Barron earned 500 total points
ID: 24431608
Please read the instructions on how to use this demo (Located on the page below)
http://ee.cffcs.com/Q_24422573/Q_24422573.asp
code
http://ee.cffcs.com/Q_24422573/Q_24422573.zip

Please only test 1 address on this live demo, so that I do not have to upload another DB.
With new address's

Please download and have a look at the code.

Carrzkiss
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 24434411
cosmopolita:
Can you let me know how the code worked out?
Instead of just Awarding and leaving?
(I always like to find out how people like my work, and if it helps them
Make this EE thing worth it, you know?)

Carrzkiss
0
 

Author Comment

by:cosmopolita
ID: 24434762
Dear CarrzKiss
Sorry, I had to take a neighbour to hospital and didn't want to leave you without any reply.

Yes, it works fine.
So you had to add
        SqlStmt23 = "SELECT * FROM lista_emails"
        Set rs23=Server.CreateObject("ADODB.Recordset")
        rs23.open SqlStmt23, conn
to make the DELETE WORK.
This time I am completely confused. Well, I am a home-made ASPer.
Either did I understand the job of these 3 lines:
        if not rs23.EOF then
        if rs22("email")=rs23("email") then
        end if
All works fine, anyhow.
I thank you very much for the patiente you have/had with me.
Manfred
0
 

Author Comment

by:cosmopolita
ID: 24434768
I ment 2 lines
        if rs22("email")=rs23("email") then
        end if
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 24435025
Why I had to add this
 if not rs23.EOF then
        if rs22("email")=rs23("email") then
        end if


OK.
The
 if not rs23.eof then
 looks to see if the Record is there, if not, then conitue on to the next records.
(Could not do this with the rs22, as it is another Table, and the rs2
It is another table AFTER, so I need a way to check against the rs22, so created another RecordSet
And there you go.

if rs22("email")=rs23("email") then

If the record exist in rs22, and the same record is in rs23
Then delete it from rs22.

So.
Hope that explains a little better for you.
It was a fun trip, I enjoyed it a lot.

I am off to help someone with an Email component issue now.
Have a good one.
Carrzkiss
0
 

Author Comment

by:cosmopolita
ID: 24436322
Dear CarrzKiss
Ref.:
If the record exist in rs22, and the same record is in rs23
Then delete it from rs22.
No, the 'not wanted @-addresses' should be always kept.
If you get new emails from somewhere a 'not wanted' email address
could be again in this new list and should be deleted with a 'clean_list' programm.

BTW: I found that lot of the code was not necessary
Down here is the stripped version.
the NOT NECESSARY PART is marked '--->
The record are deleted anyhow.
Sorry, I didn't want to be pestering but thought it may interest you.
Manfred
conn=StrAdminProvider
SqlStmt22 = "SELECT * FROM lista_emails_not_existing"
Set rs22=Server.CreateObject("ADODB.Recordset")
'response.write SqlStmt
rs22.open SqlStmt22, conn
 
SqlStmt23 = "SELECT * FROM lista_emails"
Set rs23=Server.CreateObject("ADODB.Recordset")
'response.write SqlStmt
rs23.open SqlStmt23, conn
 
if rs22.eof then
else
        do until rs22.eof
        
        email=rs22("email")
        if not rs23.EOF then
 '--->        if rs22("email")=rs23("email") then
 '--->        end if
        response.write "Email:" & EMAIL & "<br>"
                SqlStmt2 = "DELETE FROM lista_emails WHERE email='" & email & "'"
                Set rs2=Server.CreateObject("ADODB.Recordset")
                'response.write SqlStmt2
                rs2.open SqlStmt2, conn 
 '--->                else
 '--->                if not rs2.eof then
 '--->                recfound=1
 '--->                else
 '--->                       do until rs2.eof
 '--->                       response.write "Encontrado y borrado: " & email & "<br>"
 '--->                        rs2.movenext
 '--->                        loop
 '--->                end if
 
 end if
        rs22.movenext
        loop
end if

Open in new window

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question