CODEWITHSUNDEEP
c#sql-servervisual-studiosql-server-2005t-sql
kingrichard2005
kingrichard2005

Reputation: 7269

Return value of a stored procedure in C# returns error

I'm trying to retrieve the return value of a stored procedure in SQL Server 2005 as follows:

        OleDbCommand comm = new OleDbCommand(process_name, connection);
        comm.CommandType = CommandType.StoredProcedure;

        for (int i = 0; i < process_parameters.Length; i++)
            comm.Parameters.AddWithValue(process_parameters[i].ParameterName, process_parameters[i].Value);

        //Add output param
        comm.Parameters.Add("@TestID", OleDbType.Integer).Direction = ParameterDirection.ReturnValue;

        comm.ExecuteNonQuery();

When ExecuteNonQuery() is called, however, I get back a OleDbException saying: "Procedure or function myStoredProcedure has too many arguments specified." My stored procedure begins as follows:

ALTER PROCEDURE [dbo].[spTabtempTestsINSERT]
(
            @Param1     char (64),
            @Param2     char (128),
            @Param3     char (64),
)
AS
BEGIN
    Declare     @TestID         int

And ends with the following:

    RETURN @TestID
END

Does the fact that the return value is declared, rather than being passed as a parameter, have anything to do with it? If so, how can I get the return value of a param that's declared after the stored procedure begins? Thanks for your help.

UPDATE: I've tried the changes suggested so far, I added the following line:

            OleDbCommand comm = new OleDbCommand(process_name, connection);
            comm.CommandType = CommandType.StoredProcedure;

            for (int i = 0; i < process_parameters.Length; i++)
                comm.Parameters.AddWithValue(process_parameters[i].ParameterName, process_parameters[i].Value);

            var testID = (int)comm.ExecuteScalar();

Now when ExecuteScalar() gets executed, I get a NullReferenceException with Object reference not set to an instance of an object.

Note: I also tried setting it to an integer int testID = (int)comm.ExecuteScalar(); and I still get the same error.

Upvotes: 1

Views: 8482

Answers (6)

Theodore K.
Theodore K.

Reputation: 5176

I had the same issue, the problem is that you have to declare the return parameter first (before the input and output ones), this way it works:

OleDbCommand comm = new OleDbCommand(process_name, connection);
comm.CommandType = CommandType.StoredProcedure;

//Add the return param first
comm.Parameters.Add("@TestID", OleDbType.Integer).Direction = ParameterDirection.ReturnValue;

for (int i = 0; i < process_parameters.Length; i++)
    comm.Parameters.AddWithValue(process_parameters[i].ParameterName, process_parameters[i].Value);

comm.ExecuteNonQuery();
Object return_value = comm.Parameters["@TestID"].Value;

Upvotes: 0

chrnola
chrnola

Reputation: 332

In response to the accepted answer...

Because you are setting the parameter's Direction to ReturnValue here:

comm.Parameters.Add("@ReturnValue", OleDbType.Integer).Direction = ParameterDirection.ReturnValue;

You don't need an OUTPUT parameter in your stored procedure at all. You could change it back to:

ALTER PROCEDURE [dbo].[spTabtempTestsINSERT]
(
        @Param1     char (64),
        @Param2     char (128),
        @Param3     char (64),
)
AS
BEGIN
    Declare     @TestID         int

With:

    RETURN @TestID
END

at the end and it would still work exactly as it does now.

It is purely coincidental that your ReturnValue Parameter in C# bears the same name as your OUTPUT parameter. There is a separate parameter Direction for OUTPUT parameters.

Upvotes: 0

kingrichard2005
kingrichard2005

Reputation: 7269

Ok, I was able to get it to work properly. My problem was that, I neglected to declare any of the parameters as OUTPUT, I added an extra variable and set that as the output variable and had the stored procedure return that. After word I just checked that output parameter ,after calling the stored procedure, from my C# code, the following shows the changes:

ALTER PROCEDURE [dbo].[spTabtempTestsINSERT]
(
            @Param1     char (64),
            @Param2     char (128),
            @Param3     char (64),
            @ReturnValue int OUTPUT
)
AS
BEGIN
    Declare     @TestID         int

And at the end make a simple change:

    SET @ReturnValue = @TestID
    RETURN @ReturnValue
END

Finally, back in my code:

        OleDbCommand comm = new OleDbCommand(process_name, connection);
        comm.CommandType = CommandType.StoredProcedure;

    for (int i = 0; i < process_parameters.Length; i++)
        comm.Parameters.AddWithValue(process_parameters[i].ParameterName, process_parameters[i].Value);

       //Add output param
        comm.Parameters.Add("@ReturnValue", OleDbType.Integer).Direction = ParameterDirection.ReturnValue;

        comm.ExecuteNonQuery();
        Console.WriteLine("Stored Procedure returned a value of "+ comm.Parameters["@ReturnValue"].Value); //Success

Thank you all for your suggestions. Any alternatives or further insights are welcome.

Upvotes: 0

Ali Tarhini
Ali Tarhini

Reputation: 5358

remove these:

 //Add output param
        comm.Parameters.Add("@TestID", OleDbType.Integer).Direction = ParameterDirection.ReturnValue;

        comm.ExecuteNonQuery();

and use this instead: var testID = (int) comm.ExecuteScalar();

Upvotes: 0

John Batdorf
John Batdorf

Reputation: 2542

I thought ExecuteNonQuery() doesn't return anything from SQL..... am I missing something here?

Upvotes: 0

Aliostad
Aliostad

Reputation: 81660

Change 

 comm.ExecuteNonQuery();

to

 int returnValue =  (int) comm.ExecuteScalar();

And remove:

  comm.Parameters.Add("@TestID", OleDbType.Integer).Direction = ParameterDirection.ReturnValue;

Upvotes: 5

Related Questions