cosmopolita
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
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
ASKER
Sorry,
at line: if rs2.eof then
and
if rs3.eof then
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
if rs22.eof then
And
if rs33.eof then
That should correct it.
Carrzkiss
ASKER
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
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
ASKER
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
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.
Do they have Records in them? This can happen if there is not records to display.
ASKER
I have put in 2 for each as I thought this may happen
ASKER
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
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
%>
ASKER
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.
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
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
%>
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
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
ASKER
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.
BUT, no it gives error at: if rs2.eof then
ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.
ASKER
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
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
I am making up a demo for you.
Give me a minute.
Give me a minute.
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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.Reco rdset")
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
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("
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"
end if
All works fine, anyhow.
I thank you very much for the patiente you have/had with me.
Manfred
ASKER
I ment 2 lines
if rs22("email")=rs23("email" ) then
end if
if rs22("email")=rs23("email"
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
if not rs23.EOF then
if rs22("email")=rs23("email"
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"
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
ASKER
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
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
What line? Which one?