get out parameter value of stored procedure in classic asp

Question

I am working with classic asp and using stored procedure. I have to get the value of stored procedure out parameter. This is my code

    <% @LANGUAGE="VBSCRIPT" CODEPAGE="65001" %>
 <!-- METADATA TYPE="TypeLib" NAME="Microsoft ADO Type Library" UUID="{00000205-0000-0010-8000-00AA006D2EA4}" -->
<% 

Dim value
    Dim i

set con = Server.CreateObject("ADODB.Connection")
con.Open  "Provider=SQLOLEDB;Server=aliba\SQLEXPRESS;Database=dummySP;Trusted_Connection=Yes;"      
Set Comm = Server.CreateObject("ADODB.Command")
comm.ActiveConnection = con
comm.CommandText = "sp_dummy"
'comm.NamedParameters=true
comm.CommandType = adCmdStoredProc
comm.Parameters.Append comm.CreateParameter("@weight" , adVarchar,adParamInput, 50, "hello")
'comm.Parameters.Append comm.CreateParameter("PRODUCT", adVarchar, adParamInput,50, producttype )
'comm.Parameters.Append comm.CreateParameter("ACCOUNT", adVarchar, adParamInput,100, "" )


    comm.Parameters.Append comm.CreateParameter("@pris", adVarchar, adParamOutput,50)  'output parameters
    'i=comm.Execute
    comm.Execute
    value=comm.Parameters("@pris").Value
     Response.Write("Value is")
    Response.Write(value)

The value of pris is not showing on output.I have no idea what is wrong with this.

I followed this link (Calling SQL Stored Procedure with Output Parameter in VBScript) but does not get success

It is giving me following error

Value is

Response object error 'ASP 0185 : 8002000e'

Missing Default Property

/StoreProcedure.asp, line 0

A default property was not found for the object.

Here is my stored procedure

 ALTER procedure [dbo].[sp_dummy]
  @weight nvarchar(50),
  @pris nvarchar(50)= null out
  as
  begin

  select @pris = pris from sp_dummy_table where weight= @weight
  end

You can't get the value of an adParamOutput parameter until the ADODB.Command has run the Execute() method. Execute*() has to run to retrieve the output from the Stored Procedure and populate the parameter. The error I think is because your value= line is incorrect, move it below comm.execute and try value = comm.Parameters("Pris").Value. Also remember to .Append all parameters in the order the stored procedure expects or you will have problems. — user692942
@Lankymart I just did what you said, but still giving me the same error. — Amit Kaushal
It would help if you posted the stored procedure definition not the whole thing just ALTER PROCEDURE [schema.name] @parameter1, @parameter2, ... AS will help me workout what you need defined parameter wise (assuming you're using SQL Server). — user692942

CLaFarge CLaFarge · Accepted Answer · 2015-08-13T18:27:03

I suggest that you close out your SP with a SELECT so you can get the value as from a Recordset.

SELECT OutPRIS=@pris

Then in your ASP code:

Set rsComm = comm.Execute
If Not rsComm.EOF Then
    myPRIS = rsComm.Fields("OutPRIS").Value
Else
    myPRIS = Null
End If
rsComm.Close
Set rsComm = Nothing

Hope this helps.

get out parameter value of stored procedure in classic asp

1 Answers