Solved

Operation is not allowed when the object is closed.

Posted on 2009-05-19
24
363 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
  • 12
  • 12
24 Comments
 
LVL 30

Expert Comment

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

Author Comment

by:cosmopolita
Comment Utility
Sorry,

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

Expert Comment

by:Wayne Barron
Comment Utility
change them to
if rs22.eof then
And
if rs33.eof then

That should correct it.

Carrzkiss
0
 

Author Comment

by:cosmopolita
Comment Utility
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
Comment Utility
BTW: Nice to see you again
0
 
LVL 30

Expert Comment

by:Wayne Barron
Comment Utility
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 30

Expert Comment

by:Wayne Barron
Comment Utility
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
Comment Utility
I have put in 2 for each  as I thought this may happen
0
 
LVL 30

Expert Comment

by:Wayne Barron
Comment Utility
0
 

Author Comment

by:cosmopolita
Comment Utility
When the tables are empty there is no error.
0
 
LVL 30

Expert Comment

by:Wayne Barron
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 30

Expert Comment

by:Wayne Barron
Comment Utility
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 30

Expert Comment

by:Wayne Barron
Comment Utility
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
Comment Utility
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
Comment Utility
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 30

Expert Comment

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

Author Comment

by:cosmopolita
Comment Utility
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 30

Accepted Solution

by:
Wayne Barron earned 125 total points
Comment Utility
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 30

Expert Comment

by:Wayne Barron
Comment Utility
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
Comment Utility
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
Comment Utility
I ment 2 lines
        if rs22("email")=rs23("email") then
        end if
0
 
LVL 30

Expert Comment

by:Wayne Barron
Comment Utility
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
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now