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

[Matthew Louden]

The following ASP code yields the following error, but actually the new
record is stored
in database. The same error happens when the application deletes a record,
such as
sqlStmt ="delete from test where username='2323'" Any ideas? Thanks!

<%
Dim objRS, sqlStmt
Set objRS = Server.CreateObject ("ADODB.Recordset")
sqlStmt = "insert into test VALUES ('2341', '2341');"
objRS.Open sqlStmt, strConnect
objRS.Close
Set objRS = Nothing
%>

ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.

 

[Ken Schaefer]

An action statement (INSERT, UPDATE, DELETE) does not return any records,
hence you can't close an non-open recordset. Instead, you cn do:

<%
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConnection

objConn.Execute(sqlStmt)

objConn.Close
Set objConn = Nothing
%>

Also, you should not use a connection string in the .Open method of a
recordset object, as this defeats connection pooling:

http://support.microsoft.com/?id=191572

Cheers
Ken

: The following ASP code yields the following error, but actually the new
: record is stored
: in database. The same error happens when the application deletes a record,
: such as
: sqlStmt ="delete from test where username='2323'" Any ideas? Thanks!
:
: <%
: Dim objRS, sqlStmt
: Set objRS = Server.CreateObject ("ADODB.Recordset")
: sqlStmt = "insert into test VALUES ('2341', '2341');"
: objRS.Open sqlStmt, strConnect
: objRS.Close
: Set objRS = Nothing
: %>
:
: ADODB.Recordset error '800a0e78'
: Operation is not allowed when the object is closed.
:
:
:
:
:

 

[Matthew Louden]

Thanks Ken,

I just tried it, but it yields another error on line
objConn.Execute(sqlStmt)
Microsoft OLE DB Provider for SQL Server error '80040e2f'

'runners.dbo.User' ???, 'user_level' ?? NULL ?? ??? ? ????. ??? null? ??? ?
????. INSERT?(?) ??????.

any ideas??

 

[Matthew Louden]

I got the run-time error: Microsoft OLE DB Provider for SQL Server error
'80040e2f'

I read this http://www.aspfaq.com/show.asp?id=2370, but I am adding a
record, not deleting a record. Since I migrate the whole web pages and
database from one machine to another machine, it worked everything fine in
the old machine. I already changed the connection string, and necessary
links, but the logic should be unchanged.

Please advise! Thanks!

 

[Bob Barrows]

Please show your revised code. we're not mind readers.

 

[Matthew Louden]

I got the run-time error: Microsoft OLE DB Provider for SQL Server error
'80040e2f' on line
conn.Execute(InsertUsersSql)

Here's the code fragment:
<%
Set conn = Server.CreateObject ("ADODB.Connection")
conn.Open strConnect
InsertUsersSql= "Insert Into Users VALUES ('myusername', 'mypassword');"
Response.Write InsertUsersSql
conn.Execute(InsertUsersSql)
conn.Close
Set conn = Nothing
%>

I read this http://www.aspfaq.com/show.asp?id=2370, but I am adding a
record, not deleting a record. Any ideas??

 

[Ken Schaefer]

What columns do you have in the table Users? If there are more than two,
then do:

InsertUsersSql = _
"INSERT INTO Users (field1, field2) & _
"VALUES ('myusername', 'mypassword');"

(changing field1, and field2 to be the names of the fields you want the
values inserted into)

Cheers
Ken


: I got the run-time error: Microsoft OLE DB Provider for SQL Server error
: '80040e2f' on line
: conn.Execute(InsertUsersSql)
:
: Here's the code fragment:
: <%
: Set conn = Server.CreateObject ("ADODB.Connection")
: conn.Open strConnect
: InsertUsersSql= "Insert Into Users VALUES ('myusername', 'mypassword');"
: Response.Write InsertUsersSql
: conn.Execute(InsertUsersSql)
: conn.Close
: Set conn = Nothing
: %>
:
: I read this http://www.aspfaq.com/show.asp?id=2370, but I am adding a
: record, not deleting a record. Any ideas??
:
: : > Please show your revised code. we're not mind readers.
: >
: > Matthew Louden wrote:
: > > I got the run-time error: Microsoft OLE DB Provider for SQL Server
: > > error '80040e2f'
: > >
: > > I read this http://www.aspfaq.com/show.asp?id=2370, but I am adding a
: > > record, not deleting a record. Since I migrate the whole web pages and
: > > database from one machine to another machine, it worked everything
: > > fine in the old machine. I already changed the connection string, and
: > > necessary links, but the logic should be unchanged.
: > >
: > > Please advise! Thanks!
: > >
: > >
: > > : > >> An action statement (INSERT, UPDATE, DELETE) does not return any
: > >> records, hence you can't close an non-open recordset. Instead, you
: > >> cn do:
: > >>
: > >> <%
: > >> Set objConn = Server.CreateObject("ADODB.Connection")
: > >> objConn.Open strConnection
: > >>
: > >> objConn.Execute(sqlStmt)
: > >>
: > >> objConn.Close
: > >> Set objConn = Nothing
: > >> %>
: > >>
: > >> Also, you should not use a connection string in the .Open method of a
: > >> recordset object, as this defeats connection pooling:
: > >>
: > >> http://support.microsoft.com/?id=191572
: > >>
: > >> Cheers
: > >> Ken
: > >>
: > >> : > >>> The following ASP code yields the following error, but actually the
: > >>> new record is stored
: > >>> in database. The same error happens when the application deletes a
: > >>> record, such as
: > >>> sqlStmt ="delete from test where username='2323'" Any ideas?
: > >>> Thanks!
: > >>>
: > >>> <%
: > >>> Dim objRS, sqlStmt
: > >>> Set objRS = Server.CreateObject ("ADODB.Recordset")
: > >>> sqlStmt = "insert into test VALUES ('2341', '2341');"
: > >>> objRS.Open sqlStmt, strConnect
: > >>> objRS.Close
: > >>> Set objRS = Nothing
: > >>> %>
: > >>>
: > >>> ADODB.Recordset error '800a0e78'
: > >>> Operation is not allowed when the object is closed.
: >
: >
: >
:
: