Operation is not allowed when the object is closed with Insert stored procedure

Discussion in 'ASP General' started by Drew, Sep 24, 2007.

  1. Drew

    Drew Guest

    I have some insert stored procedures that I am having problems with. The
    ASP looks like this,

    'Execute SP to insert into Task table
    sql = "DECLARE @Count int " &_
    "DECLARE @TaskID varchar(20) " &_
    "DECLARE @Description varchar(255) " &_
    "DECLARE @AssignedTo varchar(25) " &_
    "EXEC InsertTask " &_
    "@Count = ''," &_
    "@TaskID = '" & varTaskID & "'," &_
    "@Description = '" & varDescription & "'," &_
    "@AssignedTo = '" & varAssignedTo & "'"

    set conn = CreateObject("ADODB.Connection")
    conn.open MM_WorkOrderTracking_STRING
    set rs = conn.execute(sql)

    'Close recordset and connection
    rs.close: set rs = nothing
    conn.close: set conn = nothing

    On the rs.close: line, it keeps giving me the Operation is not allowed when
    the object is closed error, which I am assuming is caused by the SP
    returning "The command(s) completed successfully.". I have SET NO COUNT in
    my SPs... Is there any way to supress the "The command(s) completed
    successfully." message?

    Thanks,
    Drew
    Drew, Sep 24, 2007
    #1
    1. Advertising

  2. Drew wrote:
    > I have some insert stored procedures that I am having problems with.
    > The ASP looks like this,
    >
    > 'Execute SP to insert into Task table
    > sql = "DECLARE @Count int " &_
    > "DECLARE @TaskID varchar(20) " &_
    > "DECLARE @Description varchar(255) " &_
    > "DECLARE @AssignedTo varchar(25) " &_
    > "EXEC InsertTask " &_
    > "@Count = ''," &_
    > "@TaskID = '" & varTaskID & "'," &_
    > "@Description = '" & varDescription & "'," &_
    > "@AssignedTo = '" & varAssignedTo & "'"
    >
    > set conn = CreateObject("ADODB.Connection")
    > conn.open MM_WorkOrderTracking_STRING
    > set rs = conn.execute(sql)
    >
    > 'Close recordset and connection
    > rs.close: set rs = nothing
    > conn.close: set conn = nothing
    >
    > On the rs.close: line, it keeps giving me the Operation is not
    > allowed when the object is closed error, which I am assuming is
    > caused by the SP returning "The command(s) completed successfully.".
    > I have SET NO COUNT in my SPs... Is there any way to supress the "The
    > command(s) completed successfully." message?
    >

    Does this stored procedure return a resultset? If not, don't use a
    recordset to run it! Do this instead:

    conn.execute sql,,129
    ' 129 is the combination of two values: 1 (adCmdText) which indicates
    ' that you are passing a string to be executed,
    ' and 128 (adExecuteNoRecords) which tells it not to open a
    ' recordset because you aren't retrieving any records

    Actually, this technique is leaving you vulnerable to hackers using sql
    injection. Part of the reason for using stored procedures with
    parameters is so you don't have to resort to dynamic sql to execute
    them. Here is how I would run this procedure:

    conn.InsertTask "",varTaskID, varDescription,varAssignedTo

    If the procedure actually does return records, then I would do this:
    set rs = createobject("adodb.recordset")
    conn.InsertTask "",varTaskID, varDescription,varAssignedTo, rs

    --
    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.
    Bob Barrows [MVP], Sep 24, 2007
    #2
    1. Advertising

  3. Drew

    Drew Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:OabYpUt$...
    > Drew wrote:
    >> I have some insert stored procedures that I am having problems with.
    >> The ASP looks like this,
    >>
    >> 'Execute SP to insert into Task table
    >> sql = "DECLARE @Count int " &_
    >> "DECLARE @TaskID varchar(20) " &_
    >> "DECLARE @Description varchar(255) " &_
    >> "DECLARE @AssignedTo varchar(25) " &_
    >> "EXEC InsertTask " &_
    >> "@Count = ''," &_
    >> "@TaskID = '" & varTaskID & "'," &_
    >> "@Description = '" & varDescription & "'," &_
    >> "@AssignedTo = '" & varAssignedTo & "'"
    >>
    >> set conn = CreateObject("ADODB.Connection")
    >> conn.open MM_WorkOrderTracking_STRING
    >> set rs = conn.execute(sql)
    >>
    >> 'Close recordset and connection
    >> rs.close: set rs = nothing
    >> conn.close: set conn = nothing
    >>
    >> On the rs.close: line, it keeps giving me the Operation is not
    >> allowed when the object is closed error, which I am assuming is
    >> caused by the SP returning "The command(s) completed successfully.".
    >> I have SET NO COUNT in my SPs... Is there any way to supress the "The
    >> command(s) completed successfully." message?
    >>

    > Does this stored procedure return a resultset? If not, don't use a
    > recordset to run it! Do this instead:
    >
    > conn.execute sql,,129
    > ' 129 is the combination of two values: 1 (adCmdText) which indicates
    > ' that you are passing a string to be executed,
    > ' and 128 (adExecuteNoRecords) which tells it not to open a
    > ' recordset because you aren't retrieving any records
    >
    > Actually, this technique is leaving you vulnerable to hackers using sql
    > injection. Part of the reason for using stored procedures with
    > parameters is so you don't have to resort to dynamic sql to execute
    > them. Here is how I would run this procedure:
    >
    > conn.InsertTask "",varTaskID, varDescription,varAssignedTo


    NICE! I like that! I just got the other method in my head and that is what
    I use all over... thanks for clearing this up for me!

    Drew
    Drew, Sep 24, 2007
    #3
    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. Replies:
    1
    Views:
    2,099
    Kevin Spencer
    Aug 10, 2005
  2. Matthew Louden
    Replies:
    6
    Views:
    238
    Ken Schaefer
    Sep 28, 2003
  3. cliverama
    Replies:
    1
    Views:
    125
    Aaron Bertrand [MVP]
    Nov 5, 2003
  4. Guest
    Replies:
    1
    Views:
    225
    Ray Costanzo [MVP]
    Dec 20, 2004
  5. TC
    Replies:
    1
    Views:
    133
    madhouse
    Jan 12, 2005
Loading...

Share This Page