2

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

8
  • Personally I use cmd.Parameters.Refresh to load the parameter definition from the proc. That should for example set the output attribute correctly (in your case you haven't done this) Commented Mar 22, 2018 at 3:33
  • 1
    @Nick.McDermaid: That causes a server round trip: I wouldn't recommend it's use. Commented Mar 22, 2018 at 4:35
  • Also: "If you use the Refresh method to obtain parameter information from the provider and it returns one or more variable-length data type Parameter objects, ADO may allocate memory for the parameters based on their maximum potential size, which will cause an error during execution." : learn.microsoft.com/en-us/sql/ado/reference/ado-api/… Commented Mar 22, 2018 at 4:36
  • You make good points. These points are balanced against the developer having to define the parameters seperately in both in the database and in the application (and know about parameter zero which is the return value, as well as the OUTPUT parameter trick). Anyway I believe you have addressed the root problem in your answer below. Refresh can certainly helpful even just for one-off metadata discovery - to know what the parameters should look like if you want to code them. Commented Mar 22, 2018 at 4:56
  • I wasn't aware of the cmd.parameters.Refresh method. Sounds very handy! Good caveats mentioned, too. Commented Mar 22, 2018 at 5:41

3 Answers 3

1

The create parameter parameters are to be set like this. Set objparameter=objcommand.CreateParameter (name,type,direction,size,value) in ADO.

Please set the parameters, its type, direction & size correctly.

  Response.write "[" & cmd.Parameters("@status").Value & "]"
Sign up to request clarification or add additional context in comments.

6 Comments

Isn't the line cmd.Parameters.Append cmd.CreateParameter("@status", 3, 2) already doing this?
the documentation says the first argument is name , then type and then direction & Value. In the above case first argument is correct, but the second argument should be the type.
Oh, I see what you mean now. I'm using the enum values rather than the labels. Value of 3 maps to label of adInteger, per MS doco. Similarly for the direction argument, value of 2 maps to a label of adParamOutput, per the MS docs.
you should also try putting the "return" at the end of procedure.
|
0

Please try

cmd.Parameters["@status"].Value

2 Comments

Thanks. Have tried this and still get a null value.
Again, this is either c# syntax in .Net or Classic ASP using JScript not Classic ASP using VBScript.
0

I found the problem: I was using the wrong dataType for the parameter @response_id. I had adSmallInt (2), whereas it should have been adInteger (3) to match the data type declared in the stored procedure. MSSQL was throwing an exception, but my ASP script had On Error Resume Next specified which was silently swallowing the error. So the script ran and the SP executed the INSERT statement but couldn't get far enough to set the @status parameter, but the script kept running anyway. Once I commented out the On Error Resume Next, I could see the exception and found the culprit.

Thanks for all your suggestions anyway. Much appreciated!

4 Comments

Very common and covered it multiple questions on Stack Overflow already.
A useful resource I have used for checking data types is Data Type Mapping, helped me out many times.
Thanks. I know similar q's have been asked and I think I researched all of them! But none pointed me in the right direction for this problem. Most talked about retrieving all record sets before having access to output params, while others talked about having the right data type for the param you're interested in. None suggested to check if On Error Resume Next is being used as there could be an exception being swallowed that is causing the output parameter to never be set.
@MattP that's because you used "the enum values rather than the labels". Such code is unreadable and often leads to errors. Also we assumed the SP was executed successfully as a developer can easiliy verify that by running SQL Profiler before asking for a help. Similar problem could be if SP is dropped, SQL Server is stopped, etc... such problems should be eliminated from the beginning. Anyway... we're glad you solved the issue.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.