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

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]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OabYpUt$HHA.1188@TK2MSFTNGP04.phx.gbl...
> 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