I'm trying to access the return value of this SP. However, the return value seems to always be zero. Unforuntately I'm not allowed to change the SP. Any help would be appreciated. Thanks!

-V

Here is the code...

            
AseParameter[] parameters = new AseParameter[2];
parameters[0] = RetrievalProcedures.CreateAseParameter("@Entity", AseDbType.NVarChar, 90, ParameterDirection.Input, true, 90, 0, "", DataRowVersion.Current, entity);
parameters[1] = new AseParameter("RETURNVALUE", AseDbType.Integer, 0, ParameterDirection.ReturnValue, true, 10, 0, "", DataRowVersion.Current, returnValue);
int toReturn = adapter.CallActionStoredProcedure("resource.dbo.PCC_GenerateId;1", parameters);
returnValue = (int)parameters[1].Value;
return toReturn;

Here is the SP...

CREATE PROCEDURE MyProc @Entity  nvarchar(30)
AS
BEGIN
    DECLARE @Id int,
                               @ReservedID int

    [Some logic here]

    SELECT @Id AS id

ENDRETURN:
   RETURN 0
END

Are you sure the SP works nice at DB level passing the same parameters? LLBLGen runtime library version? ASE ADO.NET client version?

Yes, the SP works fine at the DB level.

We are using LLBLGen 2.6, ADO.NET 1.15.152.0.

The SP doesn't have output parameters, right? So, did you try converting the SP (mapped at LLBLGen Designer) as Rerieval SP? (look at this: http://llblgen.com/tinyforum/Messages.aspx?ThreadID=14888).

Thanks Dave! Changing to a retrieval procedure worked.

By the way, I found a bug in both RetrievalProceduresAdapter and ActionProceduresAdapter templates. For all the input parameters it's using the ParameterDirection.OUTPUT enumerated value.

Vinh

By the way, I found a bug in both RetrievalProceduresAdapter and ActionProceduresAdapter templates. For all the input parameters it's using the ParameterDirection.OUTPUT enumerated value.

Please help us identify the template. 1- Which Database is this? 2- .NET 1.x or .NET 2.x? 3- C# or VB? 4- LLBLGen Pro Designer->Help->About...... which release date is it?

Sybase ASE .NET 2.x C# 2.6 Final, October 6, 2008

You are right, thanks.

The return value isn't returned from the method but in the call.

So to call ActionProcedures.MyProc, you've to specify a ref parameter returnvalue.

that's the value returned from the stored procedure itself. The value returned from the method is the value received from the ado.net provider (which for example can be the # of rows affected).

Does 'returnvalue' return as 0 ?

Btw, your proc returns 0 (See at the bottom: RETURN 0) so of course it returns 0

vhtas wrote:

Thanks Dave! Changing to a retrieval procedure worked.

What did it return now? Retrieval procs return sets, so changing it for this particular proc doesn't make sense or I'm lost at what you're referring to

By the way, I found a bug in both RetrievalProceduresAdapter and ActionProceduresAdapter templates. For all the input parameters it's using the ParameterDirection.OUTPUT enumerated value. Vinh

Strange as your startpost quotes a piece of generated code which shows Input

Btw, I checked the templates again and you are right, the input parameters are generated as output parameters. I've fixed the templates.

I've attached them as a .zip to this post.

Got a similar problem. W\ a breaking change in v.3.0 as "Stored procedure call code no longer has overloads which accept/return ReturnValue values." I can no longer access value returned from SP's via ExecuteScalar style. With a restriction of no changing SP's it makes the usage of LLBLGen 3.0 mapper hardly feasible or am I missing something? Any ideas? Maybe some custom code-generation templates exist for this case? Quick answer much appreciated.

An action stored procedure can have a return value returned by the ado.net thet might represent affected rows, this is something else than input oroutput parameters.

The generated method calls that call th SP, will return the ReturnValue, and will accept parameters for the Input and output parameters of the SP. In previous versions we had an overload of these methods that would take the a parameter for the ReturnValue, this has been removed. So you are left with the methods that return the ReturnValue.

Next time please start a new thread.

This indeed affects SQL Server only and only procs which returned the value with the return value, not with an OUTPUT variable. In general it's good practice to use output variables, as the return value of a proc is always an int.

We cut this feature because it would make things much simpler: we didn't have any custom code per db anymore, everything is generic now and re-usable.

that said, if you could give us an example of what you want to do, it would help us with giving you hints how to add this. In v2.x, proc calls happened completely in templated code, so there's nothing stopping you from doing that now as well . As we didn't execute procs using executescalar anyway, I think you already had custom code for these kind of procs?

In any way, it is possible in v3, just not directly out of the box with the generated code, but in the edge case that you need this, you can. Please give us an example of such a proc and what you're doing in v2.x with it currently.

I see you posted another thread. I'll continue there. http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=18949