SQL output parameters in C#

Question

I'm trying to get a value from Microsoft SQL Server stored procedure. Totally I need to return two values. As you see below one of them is accessible by return and the other is accessible by a output parameter. I run this code in WPF(C#):

    public int InsertCustomerEntranceLogWithOptions(String cardNumber,int buy, int score, out int logID)
    {
        SqlCommand command = new SqlCommand(
            @"
            --declare @id int, @logID int                    

            exec @res = Insert_CustomerEntranceLog_WithOptions 
                @cardNumber, @buy, @score, @logID

            --set @logID = @id",
            myConnection);

        command.CommandType = CommandType.Text;
        command.Parameters.Add("@cardNumber", SqlDbType.NVarChar).Value = cardNumber;
        command.Parameters.Add("@buy", SqlDbType.Int).Value = buy;
        command.Parameters.Add("@score", SqlDbType.Int).Value = score;

        command.Parameters.Add("@logID", SqlDbType.Int).Value = 0;
        command.Parameters["@logID"].Direction = ParameterDirection.Output;      

        command.Parameters.Add("@res", SqlDbType.Int).Value = 0;
        command.Parameters["@res"].Direction = ParameterDirection.Output;

        int res = (int)RunNonQuery(command);
        logID = (int)command.Parameters["logID"].Value;

        return res;
    }

and RunNonQuery Method is:

    public Object RunNonQuery(SqlCommand command, String returnVariableName = "@res")
    {
        Object result = null;
        if (openConnection())
        {
            try
            {
                command.ExecuteNonQuery();

                if (returnVariableName.Length != 0)
                {
                    result = command.Parameters[returnVariableName].Value;
                    //command.Dispose();
                }
                else
                {
                    //command.Dispose();
                    return 1;
                }
            }
            catch (TimeoutException ex)
            {
                //Log
            }
            catch (Exception ex)
            {
                Utility.LogExceptionError(ex, 1, 1, 1);
                //throw ex;
            }

            closeConnection();
        }


        return result;
    }

I'm sure that my RunNonQuery works properly. I test it a lot. But When I InsertCustomerEntranceLogWithOptions method, I Get this error:

An unhandled exception of type 'System.IndexOutOfRangeException' 
occurred in System.Data.dll

Additional information: An SqlParameter with ParameterName 'logID' 
is not contained by this SqlParameterCollection.

It seems that I do not add SqlParameter but as you see LogID is inserted. what is wrong? I also remove comments in SQL command and then run it but steel I see the error.

Thank you every body. All of you help me, But I have to choose one of your answers. But +1 for all of you. — Babak.Abad
– Babak.Abad, Commented Jun 25, 2014 at 13:26

NicoD · Accepted Answer · 2014-06-25 12:35:07Z

2

Seems like you forget the @ prefix for your sql parameter :

logID = (int)command.Parameters["@logID"].Value;

answered Jun 25, 2014 at 12:35

NicoD

1,1998 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:10:59Z

1

May be that your logID = (int)command.Parameters["logID"].Value; does not access the logId because it should be named @logID, as you've added it like:

command.Parameters.Add("@logID", SqlDbType.Int).Value = 0;
command.Parameters["@logID"].Direction = ParameterDirection.Output;

and '@' must be part of the parameter name - Is it necessary to add a @ in front of an SqlParameter name?

edited May 23, 2017 at 12:10

CommunityBot

11 silver badge

answered Jun 25, 2014 at 12:34

Eugene Podskal

10.4k5 gold badges34 silver badges56 bronze badges

Comments

Jesuraja · Accepted Answer · 2014-06-25 13:03:37Z

0

@ is missing, update like the following

logID = (int)command.Parameters["@logID"].Value;

answered Jun 25, 2014 at 13:03

Jesuraja

3,8524 gold badges26 silver badges49 bronze badges

Collectives™ on Stack Overflow

SQL output parameters in C#

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related