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, Aug 13 '15 at 08:40
@Lankymart I just did what you said, but still giving me the same error. — Amit Kaushal, Aug 13 '15 at 08:45
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, Aug 13 '15 at 09:13
Your not setting `@pris` to a value and it doesn't have a default like say `@pris nvarchar(50) out = NULL` so that's why you get the error. If you want `@pris` to contain the value returned by your `SELECT` you need to assign it like this `SELECT @pris = pris FROM sp_dummy_table where weight = @weight`. — user692942, Aug 13 '15 at 09:42
I've just realised that error is coming from `Response` object can you change `i=comm.execute` to just `comm.execute` as the only value returned by `.Execute` is a `ADODB.Recordset` which here you are not returning just setting an output parameter. Then change `Response.Write(i)` to `Response.Write value`. — user692942, Aug 13 '15 at 11:19
I have updated my code again, but this time the error did not come but value is still not printed. — Amit Kaushal, Aug 13 '15 at 11:56
Have you updated the stored procedure? The definition in your question is still wrong see [this comment](http://stackoverflow.com/questions/31982714/get-out-parameter-value-of-stored-procedure-in-classic-asp?#comment51874310_31982714). Your not getting the value because your not assigning it to `@pris` in your stored procedure. The original error is fixed though. — user692942, Aug 13 '15 at 12:07
I'm not sure you really need `= null` *(apologies wrote `out` in the wrong place but you corrected it so no harm)* I was just pointing out that it might have been complaining about that default property but as it turns out it was completely unrelated and the error was caused by the `Response` object. If you don't get a value returned now my guess is `@weight = "hello"` is not finding any results so `@pris` isn't populated. — user692942, Aug 13 '15 at 13:08

score 0 · Answer 1 · edited Aug 17 '15 at 11:41

0

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.

edited Aug 17 '15 at 11:41

user692942

16,398
7
76
175

answered Aug 13 '15 at 18:27

CLaFarge

1,277
11
16

Then what is the point in using the output parameter your just substituting one method for another. – user692942 Aug 14 '15 at 00:15
I offered the suggestion of my preferred, and known-good, method. It satisfies the goal of returning the value. I'm not sure I agree that offering an alternative is actually "down-vote" worthy. – CLaFarge Aug 14 '15 at 15:02
Its not "known good" in fact is less efficient then just returning an output variable. There is nothing wrong with the OP approach just needs to make sure it is executed properly. – user692942 Aug 15 '15 at 22:17
As a matter of fact, it IS "known good", as it is known to function. I can't argue efficiency, but I can argue that I don't get this issue using this method. I didn't say anything was wrong with the OP approach: I offered an alternative, which I think is appropriate. Not sure you'd feel as judgie if you had someone going through making down-votes on answers you provided that didn't meet their narrow vision of what works. Note that this remains the ONLY answer offered. – CLaFarge Aug 16 '15 at 22:57
It's easier to assume it's me because I commented that's up to you, fact is I get plenty of down-votes and while I don't feel they are justified someone else feels differently so I just get on with it. Alternatives are fine but the OP is asking about `out` parameters this completely changes the context. What you are returning there is a single row single column recordset which is not the same thing. What happens when you want to return some data as well as to set an output parameter? – user692942 Aug 17 '15 at 11:34
Do you return multiple recordsets then use `.NextRecordSet` to call the correct one that contains your make shift output or do you tack it on to the end of an existing recordset? Either approach isn't ideal imho. Although I will say using `.NextRecordset` is far better then making multiple database calls. – user692942 Aug 17 '15 at 11:37
Also just worth noting, there is a reason I haven't left an answer. That reason is it is still not clear enough where the OPs issue lies although through discussing it with the OP have a clearer picture of what is going on. I don't see the value in answering when the question is unclear. However saying that the OPs gone quiet so either through the comments or your answer they got their answer, maybe they will polite enough to come back and let us know. – user692942 Aug 17 '15 at 11:45

get out parameter value of stored procedure in classic asp

1 Answers1