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

Discussion in 'ASP General' started by Matthew Louden, Sep 27, 2003.

  1. 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, Sep 27, 2003
    #1
    1. Advertising

  2. Matthew Louden

    Ken Schaefer Guest

    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

    "Matthew Louden" <> wrote in message
    news:...
    : 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, Sep 27, 2003
    #2
    1. Advertising

  3. 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??



    "Ken Schaefer" <> wrote in message
    news:...
    > 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
    >
    > "Matthew Louden" <> wrote in message
    > news:...
    > : 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, Sep 27, 2003
    #3
  4. 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!


    "Ken Schaefer" <> wrote in message
    news:...
    > 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
    >
    > "Matthew Louden" <> wrote in message
    > news:...
    > : 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, Sep 27, 2003
    #4
  5. Matthew Louden

    Bob Barrows Guest

    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!
    >
    >
    > "Ken Schaefer" <> wrote in message
    > news:...
    >> 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
    >>
    >> "Matthew Louden" <> wrote in message
    >> news:...
    >>> 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.
     
    Bob Barrows, Sep 27, 2003
    #5
  6. 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??

    "Bob Barrows" <> wrote in message
    news:...
    > 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!
    > >
    > >
    > > "Ken Schaefer" <> wrote in message
    > > news:...
    > >> 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
    > >>
    > >> "Matthew Louden" <> wrote in message
    > >> news:...
    > >>> 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, Sep 27, 2003
    #6
  7. Matthew Louden

    Ken Schaefer Guest

    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


    "Matthew Louden" <> wrote in message
    news:e$...
    : 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??
    :
    : "Bob Barrows" <> wrote in message
    : news:...
    : > 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!
    : > >
    : > >
    : > > "Ken Schaefer" <> wrote in message
    : > > news:...
    : > >> 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
    : > >>
    : > >> "Matthew Louden" <> wrote in message
    : > >> news:...
    : > >>> 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, Sep 28, 2003
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. cliverama
    Replies:
    1
    Views:
    145
    Aaron Bertrand [MVP]
    Nov 5, 2003
  2. Guest
    Replies:
    1
    Views:
    246
    Ray Costanzo [MVP]
    Dec 20, 2004
  3. TC
    Replies:
    1
    Views:
    154
    madhouse
    Jan 12, 2005
  4. Arpan
    Replies:
    2
    Views:
    126
    Arpan
    Sep 5, 2005
  5. isaac2004
    Replies:
    1
    Views:
    124
    dNagel
    Feb 23, 2006
Loading...

Share This Page