Why the output parameter of my ADODB.Command does

问题:

I have a code in Classic ASP and SQL Server, the idea is simple, to have an stored procedure so you can insert a file but before that, the sp will check if the file already exists, after that will return an output parameter so I can check it on my asp page.

The problem is the returning value of the output parameter is nothing, I cann´t figure out where is the problem..

The SP is:

        ALTER PROCEDURE [dbo].[pi_usu_crear_cuenta]
         @msg_salida    char(1) OUTPUT  /* 0=Registro ya existe, 1=Insert satidfactorio, 2=Update Satidfactorio*/
        ,@usu_email     nvarchar(50)
        ,@usu_alias     nvarchar(50)
        ,@usu_password  nvarchar(50)
        ,@pai_cod       numeric(3,0)
        ,@usu_mayoriaedad char(1)
        ,@pk_pre        int
        ,@usu_respuesta nvarchar(50)

    AS
    BEGIN
        SET NOCOUNT ON;
       IF NOT EXISTS (SELECT * FROM tm_usu_usuarios
                       WHERE usu_email = @usu_email)
       BEGIN
        INSERT INTO tm_usu_usuarios
               (usu_email
               ,usu_alias
               ,usu_password
               ,pai_cod
               ,usu_mayoriaedad
               ,pk_pre
               ,usu_respuesta
               )
         VALUES
               (
                @usu_email
               ,@usu_alias
               ,@usu_password
               ,@pai_cod
               ,@usu_mayoriaedad
               ,@pk_pre
               ,@usu_respuesta
                );
        Select @msg_salida = '1'
       END
   ELSE
   BEGIN
        Select @msg_salida = '2'
   END
    END

The Classic ASP is:

        Dim cmd2
        Dim Rs_crearcuenta     
        Const adCmdStoredProc = &H0004
        '---- ParameterDirectionEnum Values ----
        Const adParamInput          = &H0001
        Const adParamOutput         = &H0002
        Const adParamReturnValue    = &H0004
        '---- DataTypeEnum Values ----
        Const adInteger     = 3
        Const adChar        = 129
        Const adVarChar     = 200
        Const adVarWChar    = 202
        Const adNumeric     = 131

        Set cmd2 = Server.CreateObject("ADODB.Command")
        Set cmd2.ActiveConnection = Session("Conexion")
        cmd2.CommandText = "pi_usu_crear_cuenta"
        cmd2.CommandType = adCmdStoredProc

        cmd2.Parameters.Append cmd2.CreateParameter("@msg_salida",      adChar,         adParamOutput,      1)      
        cmd2.Parameters.Append cmd2.CreateParameter("@usu_email",       adVarChar,      adParamInput,       50, vEmail)     
        cmd2.Parameters.Append cmd2.CreateParameter("@usu_alias",       adVarChar,      adParamInput,       50, vAlias)     
        cmd2.Parameters.Append cmd2.CreateParameter("@usu_password",    adVarChar,      adParamInput,       50, vPassword)      

        SET param           =  cmd2.CreateParameter("@pai_cod",         adNumeric,      adParamInput,       3, null)
        param.Precision     = 3
        param.NumericScale  = 0
        param.Value         = vPais
        cmd2.Parameters.Append param    'NUMERIC

        cmd2.Parameters.Append cmd2.CreateParameter("@usu_mayoriaedad", adChar,         adParamInput,       1,  vMayoria_edad)      
        cmd2.Parameters.Append cmd2.CreateParameter("@pk_pre",          adInteger,      adParamInput,        ,  vPregunta)      
        cmd2.Parameters.Append cmd2.CreateParameter("@usu_respuesta",   adVarWChar,     adParamInput,       50, vRespuesta)     'NVARCHAR
        cmd2.Prepared = true

        Set Rs_crearcuenta = cmd2.Execute

        vMsgSalida=cmd2.Parameters("@msg_salida").value

        response.write("-")     
        response.write(vMsgSalida)      
        response.write("-")     

        Set Rs_crearcuenta = Nothing
response.end

回答1:

As @diana has already pointed out you cannot access the output parameter because it isn't set until the query has executed.

This is a common problem and in this case you might think but I'm not executing a query that returns any results, I'm just inserting some data?

At first glance this may appear to be the case but when you take into consideration the use of

Select @msg_salida = '1'

you are in fact returning a single row single column ADODB.Recordset object.

In these instances using SET instead of SELECT is advisable because it does not create a ADODB.Recordset or block access to the OUTPUT parameters until all ADODB.Recordset objects are closed.

By changing the above line for example to

SET @msg_salida = '1'

and changing your ADODB.Command execute to

'Execute the command without returning any Recordsets
Call cmd2.Execute()

回答2:

As the answer on Why adParamOutput parameter doesn't contain a value after execute explains:

You have to iterate through all records before you can read the output parameter, like

do until rs.EOF
     rs.MoveNext
loop

回答3:

In my case the issue was nothing to do with record sets being retrieved. Check if On Error Resume Next is being used in the Classic ASP script as there could be an MSSQL exception being swallowed that is causing the output parameter to never be set.