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

[Drew]

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

 

[Bob Barrows [MVP]]

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

 

[Drew]

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