I have an MSSQL stored procedure which explicitly sets an output parameter to a value. However, when I execute that stored procedure from an Classic ASP page using an ADODB command, the output parameter is null.
Stored procedure:
ALTER PROCEDURE [dbo].[recordResponse]
@survey_id smallint OUTPUT,
@member_id varchar(10) OUTPUT,
@response varchar(1000) OUTPUT,
@comment varchar(1000) OUTPUT,
@response_id int OUTPUT,
@timestamp datetime OUTPUT,
@status int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET @timestamp = getdate();
DECLARE @surveyExists as binary
Select @surveyExists = 1 from surveys where survey_id = @survey_id;
if (@surveyExists = 1)
BEGIN
insert into
responses(member, [timestamp], response, comments, survey_id)
values(@member_id, @timestamp, @response, @comment, @survey_id);
set @response_id = SCOPE_IDENTITY();
set @status = 200;
END
else
set @status = 400;
END
Classic ASP:
Set cmd = Server.CreateObject("ADODB.Command") 'Initiate the command object
cmd.CommandType = 4 'Stored Procedure
cmd.CommandText = "recordResponse" 'Name of the stored procedure
cmd.ActiveConnection = connString 'Using which connection?
'Add the parameters
cmd.Parameters.Append cmd.CreateParameter("@survey_id", 2, 3, 0, 1)
cmd.Parameters.Append cmd.CreateParameter("@member_id", 200, 3, 10, memberNo)
cmd.Parameters.Append cmd.CreateParameter("@response", 200, 3, 1000, answer)
cmd.Parameters.Append cmd.CreateParameter("@comment", 200, 3, 1000, comment)
cmd.Parameters.Append cmd.CreateParameter("@response_id", 2, 2)
cmd.Parameters.Append cmd.CreateParameter("@timestamp", 135, 2)
cmd.Parameters.Append cmd.CreateParameter("@status", 3, 2)
'Execute stored procedure
Call cmd.Execute()
Response.write "[" & cmd("@status") & "]"
This results in an output of [] whereas I am expecting an output of [200] or [400].
I have looked at various other similar threads and taken on board suggestions and solutions including iterating over the resulting recordset, but none have solved my problem.
Can anyone see where I am going wrong???
cmd.Parameters.Refreshto load the parameter definition from the proc. That should for example set the output attribute correctly (in your case you haven't done this)Refreshcan certainly helpful even just for one-off metadata discovery - to know what the parameters should look like if you want to code them.cmd.parameters.Refreshmethod. Sounds very handy! Good caveats mentioned, too.