"Operation is not allowed when the object is closed" Error

When I try to execute the code below in asp I get the following error
message:

ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.


Rs.Open "SELECT COUNT(KartNo) From Personel WHERE
KartNo="&kayit3&"", Con, 3,3
While Not Rs.Eof
if RS(0) = 0 Then
Set Rs = Con.Execute ("INSERT INTO Personel (Adi, Soyadi, KartNo,
Departman, Unvan) VALUES ('"&kayit1&"', '"&kayit2&"', '"&kayit3&"',
'"&kayit7&"', '"&kayit8&"')")
Else
Rs.Movenext
End If
Wend
Rs.Close

I'll be glad if you help me to solve this problem.

Thanks...

Re: "Operation is not allowed when the object is closed" Error

You're overwriting your original recordset with the result of a non-query
execution of INSERT, since you're using "RS" as the variable name. Try
this:


Set Rs = Con.Execute("SELECT COUNT(KartNo) From Personel WHERE
KartNo="&kayit3&"")
While Not Rs.Eof
if Rs.Fields.Item(0).Value = "0" Then Con.Execute "INSERT INTO
Personel (Adi, Soyadi, KartNo, Departman, Unvan) VALUES ('"&kayit1&"',
'"&kayit2&"', '"&kayit3&"', '"&kayit7&"', "&kayit8&"')"),,129
Rs.Movenext
Wend
Rs.Close
Set Rs = Nothing


A coupld of things to make note of:
1. Rs.Open "query..." -- No reason to do that.
http://www.aspfaq.com/show.asp?id=2191
http://www.aspfaq.com/show.asp?id=2424


2. Your IF statement was going to potentially lead to an endless loop.
If rs(0) = 0 Then
'''do stuff
Else
rs.MoveNext
End If

Well, if rs(0) = 0, you'll never move on to the next record, so that would
be an infinitely true condition.

Ray at work



"Grayscale" wrote in message
news:1143639788.558005.212130@z34g2000cwc.googlegroups.com.. .
> When I try to execute the code below in asp I get the following error
> message:
>
> ADODB.Recordset (0x800A0E78)
> Operation is not allowed when the object is closed.
>
>
> Rs.Open "SELECT COUNT(KartNo) From Personel WHERE
> KartNo="&kayit3&"", Con, 3,3
> While Not Rs.Eof
> if RS(0) = 0 Then
> Set Rs = Con.Execute ("INSERT INTO Personel (Adi, Soyadi, KartNo,
> Departman, Unvan) VALUES ('"&kayit1&"', '"&kayit2&"', '"&kayit3&"',
> '"&kayit7&"', '"&kayit8&"')")
> Else
> Rs.Movenext
> End If
> Wend
> Rs.Close
>
> I'll be glad if you help me to solve this problem.
>
> Thanks...
>

Re: "Operation is not allowed when the object is closed" Error

Grayscale wrote:
> When I try to execute the code below in asp I get the following error
> message:
>
> ADODB.Recordset (0x800A0E78)
> Operation is not allowed when the object is closed.
>
>
> Rs.Open "SELECT COUNT(KartNo) From Personel WHERE
> KartNo="&kayit3&"", Con, 3,3
There is no reason to use such an expensive cursor. Use the default
server-side, forward-only cursor.

> While Not Rs.Eof

Why are you looping through a resultset that can only contain _one_ record?

> if RS(0) = 0 Then
> Set Rs = Con.Execute ("INSERT INTO Personel (Adi, Soyadi, KartNo,
> Departman, Unvan) VALUES ('"&kayit1&"', '"&kayit2&"', '"&kayit3&"',
> '"&kayit7&"', '"&kayit8&"')")

Why are you using a recordset to execute a query that _does not return
records_?

Your code should look like this:

sql = "SELECT COUNT(KartNo) ..."
Set rs=con.Execute(sql,,1)
if rs(0) = 0 then
sql="INSERT INTO Personel ... "
Con.Execute sql,,129
'the 129 tells ADO not to construct a recordset to receive results
end if

Points to remember:
A "Select count(..." query will always return a single record, unless GROUP
BY is used.
Don't use a recordset to execute a query that does not return records.
Use the least expensive cursor that will allow you to accomplish your task.

Further points to consider:
You use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

SQL Server:
http://tinyurl.com/jyy0

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.