Link to home
Start Free TrialLog in
Avatar of cosmopolita
cosmopolitaFlag for Spain

asked on

Operation is not allowed when the object is closed.

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

Avatar of Wayne Barron
Wayne Barron
Flag of United States of America image

were do you get the error at?
What line? Which one?
Avatar of cosmopolita

ASKER

Sorry,

at line:       if rs2.eof then
and
             if rs3.eof then
change them to
if rs22.eof then
And
if rs33.eof then

That should correct it.

Carrzkiss
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
BTW: Nice to see you again
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
the RS2 and RS3
Do they have Records in them? This can happen if there is not records to display.
I have put in 2 for each  as I thought this may happen
When the tables are empty there is no error.
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

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.
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

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
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.
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

I am making up a demo for you.
Give me a minute.
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
ASKER CERTIFIED SOLUTION
Avatar of Wayne Barron
Wayne Barron
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
I ment 2 lines
        if rs22("email")=rs23("email") then
        end if
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
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