• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

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

0
cosmopolita
Asked:
cosmopolita
  • 12
  • 12
1 Solution
 
Wayne BarronAuthor, Web DeveloperCommented:
were do you get the error at?
What line? Which one?
0
 
cosmopolitaWebmasterAuthor Commented:
Sorry,

at line:       if rs2.eof then
and
             if rs3.eof then
0
 
Wayne BarronAuthor, Web DeveloperCommented:
change them to
if rs22.eof then
And
if rs33.eof then

That should correct it.

Carrzkiss
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
cosmopolitaWebmasterAuthor Commented:
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
 
cosmopolitaWebmasterAuthor Commented:
BTW: Nice to see you again
0
 
Wayne BarronAuthor, Web DeveloperCommented:
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
 
Wayne BarronAuthor, Web DeveloperCommented:
the RS2 and RS3
Do they have Records in them? This can happen if there is not records to display.
0
 
cosmopolitaWebmasterAuthor Commented:
I have put in 2 for each  as I thought this may happen
0
 
Wayne BarronAuthor, Web DeveloperCommented:
0
 
cosmopolitaWebmasterAuthor Commented:
When the tables are empty there is no error.
0
 
Wayne BarronAuthor, Web DeveloperCommented:
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
 
cosmopolitaWebmasterAuthor Commented:
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
 
Wayne BarronAuthor, Web DeveloperCommented:
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
 
Wayne BarronAuthor, Web DeveloperCommented:
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
 
cosmopolitaWebmasterAuthor Commented:
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
 
cosmopolitaWebmasterAuthor Commented:
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
 
Wayne BarronAuthor, Web DeveloperCommented:
I am making up a demo for you.
Give me a minute.
0
 
cosmopolitaWebmasterAuthor Commented:
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
 
Wayne BarronAuthor, Web DeveloperCommented:
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
 
Wayne BarronAuthor, Web DeveloperCommented:
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
 
cosmopolitaWebmasterAuthor Commented:
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
 
cosmopolitaWebmasterAuthor Commented:
I ment 2 lines
        if rs22("email")=rs23("email") then
        end if
0
 
Wayne BarronAuthor, Web DeveloperCommented:
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
 
cosmopolitaWebmasterAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 12
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now