Stored Procedure with OUTPUT parameter. Help. 2

[mymou]

Hi All

My first shot at ASP so go easy on me here. This is a bit over my head but here goes.

First, a stored procedure that should indicate whether something is true or false.

CREATE PROCEDURE XAA_in_PROF
@XAA nvarchar(6),
@PEL_PROF nvarchar(4),
@Result INT OUTPUT
AS
IF (Some SQL) >0
BEGIN
SELECT @Result = 1
END
ELSE
SELECT @Result = 0
GO

I think this works.

In an ASP I need to run the sp and get the result.
Here's my attempt (in VB at present).

Set cmdAsset = CreateObject("ADODB.Command&quot

cmdAsset.Parameters.Append cmdAsset.CreateParameter("@Xaa", adVarChar, adParamInput, 50, " 2266&quot

cmdAsset.Parameters.Append cmdAsset.CreateParameter("@Pel_Prof", adVarChar, adParamInput, 50, "gt&quot

cmdAsset.Parameters.Append cmdAsset.CreateParameter("@Result", adInteger, adParamInput, 1, 0)

cmdAsset.ActiveConnection = adoConn
cmdAsset.CommandText = "XAA_in_PROF"
cmdAsset.CommandType = adCmdStoredProc

cmdAsset.Execute
X = cmdAsset.Parameters(2).Value

But I never get the value I expect in X (return value of OUTPUT parameter?). Where am I going wrong??

Thanks in advance.


Stew

 

[newtoASP]

cmdAsset.Parameters.Append cmdAsset.CreateParameter("@Result", adInteger, adParamInput, 1, 0)

try changing to:
cmdAsset.Parameters.Append cmdAsset.CreateParameter("@Result", adInteger, adParamOutput, 1, 0)

and I think you have to make it your first parameter and address it as:
(cmdAsset.Parameters(0).Value

 

[mymou]

Hi newtoASP

Thought I had everything working. Not so sure now. From VB my code worked as expected - so changed it a little so that it should work as an ASP.

Saved this as an .asp on appropiate machine

<%
Set adoConn = Server.CreateObject(&quot;ADODB.Connection&quot
adoConn.Open &quot;Blah&quot;
Set cmdAsset = Server.CreateObject(&quot;ADODB.Command&quot

cmdAsset.Parameters.Append cmdAsset.CreateParameter(&quot;@Xaa&quot;, adVarChar, adParamInput, 50, &quot; 2266&quot

cmdAsset.Parameters.Append cmdAsset.CreateParameter(&quot;@Pel_Prof&quot;, adVarChar, adParamInput, 50, &quot;ÐÓ&quot

cmdAsset.Parameters.Append cmdAsset.CreateParameter(&quot;@Result&quot;, adInteger, adParamOutput, 1, 0)

cmdAsset.ActiveConnection = adoConn
cmdAsset.CommandText = &quot;XAA_in_PROF&quot;
cmdAsset.CommandType = adCmdStoredProc

cmdAsset.Execute
Response.Write cmdAsset.Parameters(2).Value

%>

it returns an error for
cmdAsset.Parameters.Append cmdAsset.CreateParameter(&quot;@Xaa&quot;, adVarChar, adParamInput, 50, &quot; 2266&quot
'Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.' Any ideas on how to debug this?

Thanks

Stew

 

[GabeC]

I'm not exactly sure how to debug this but I have received this problem in the past when working with passing data, especially into Stored Procedures.

It seems to me this has to do with the fact that VB is strong typed and VBScript is not.

In one case I received this type of error because the variable I was passing in was an empty string. I had to pass in data.

In the second case I had to explicitly convert the data to the type I was trying to pass in.
Ex. cstr(), cint(), clng(), etc.

I hope this helps. At any rate, it is a place to start.

Gabe

 

[mymou]

Hi GabeC

This was the kind of welcome I expected to the world of ASP. I am trying really hard to spot where these type of problems might occur - but not doing too well so far!!

Thanks, will look into it - although am tempted to create a COM object to take care of the task. First COM+ object coming up.


Stew

 

[mymou]

Hi All


Think I know why this doesn't work - but don't know the solution.

With the code, VB has the same error message if there is no reference to the ADO library.

Where can I find the 'references' in ADO???

Stew

PS Sounds like a dumb question to me!!

 

[mymou]

Hi GabeC

You pointed me in the right direction. For whatever reason, I couldn't use adParamOutput etc and had to use the number instead.

Can anyone explain why - I think it will help me understand how ASP is working.

Thanks all.


Stew

 

[GabeC]

Your thoughts are correct. In VB you set a reference to ADO but you don’t do this in ASP and VBScript thus the variable adParamOutput does not contain the value you are expecting.

To use ADO variables in your ASP pages you need to add one of the following to the ASP page.

<!-- #INCLUDE VIRTUAL=”include/adovbs.inc” -->

You will of course need to place adovbs.inc in the subdirectory include

OR

<!-- METADATA TYPE=”typelib” FILE=”C:\Program Files\Common Files\system\ado\msado15.dll” -->

Make sure the path to msado15.dll is correct.


Once you have done either of these, you will then be able to use ADO variables like adParamOutput.


The second option is more efficient but the first option is easier to work with especially if you have no control over the machine you are placing the ASP page on or if you have to move the web site to another server.

Thanks,

Gabe