C# changes output parameter to invalid value and throws error

Question

Using:

• Visual Studio 2010
• SQL Server 2012

I have a stored procedure, which inserts a new record in the DB and returns the new record ID as an output parameter. I ran the sp manually and it works.

If I run it from my C# application, and I read the output parameter afterwards, C# reads a *, instead of the number.

I changed the sp to write the output parameter to a table before outputting it. I always get the correct record ID in this table.

The C# code that reads the output parameter is used (without any changes) in several other applications and works. It is used for other sp's in this application and it works. I've added the code nevertheless:

public string SpOutputParameter(string sSpName, SpParameter[] oParam, bool 
    bKeepConnectionOpen = false)
{
        // Set return value to -1
        int iReturnValue = -1;

        // SP Command
        SqlCommand Cmd = new SqlCommand(sSpName, this.Db); // Command (exec sp)
        Cmd.CommandType = CommandType.StoredProcedure; // Type of command

        try // Try to get results
        {
            // Add the parameters
            this.AddParameters(oParam, Cmd);
            this.AddReturnValue(Cmd);

            // Get the results
            this.OpenDatabase();
            Cmd.ExecuteNonQuery();

            if (!bKeepConnectionOpen)
                this.Db.Close();

            // Get the return value
            iReturnValue = GetReturnValue(Cmd);

            // If the sp fails, throw an exception (to be caught)
            if (iReturnValue != 0)
                throw new Exception("The database returned a return value of " + Convert.ToString(iReturnValue != 0));

            // Get the output parameter to return
            foreach (SqlParameter parameter in Cmd.Parameters)
            {
                if (parameter.Direction == ParameterDirection.Output ||
                    parameter.Direction == ParameterDirection.InputOutput)
                    return Convert.ToString(parameter.Value);
            }
        }
        catch (Exception Ex)
        {
            // Edit the message, rethrow exception
            throw new Exception(
                "Failed to run sp '" + sSpName + "'",
                Ex);
        }
        finally // Dispose of used objects
        {
            // Dispose the command
            Cmd.Dispose();
            Cmd = null;
        }

        // If the code gets here, there was no output parameter.
        // return null...
        return null;
}

When I debug, I see the value of the parameter as a * on the parameter.Value property. ('return Convert.ToString(parameter.Value);' line)

At the moment my app doesn't work, I need to get the value of the output parameter. Can someone please help me figure out why I get a * (in C#) instead of the actual output parameter value?

Thank you!

Answer 1

Based on your explanation that your stored procedure is inserting a new record and returning that value, and the fact that your return type is string, I am going to guess that your output parameter is a char or varchar, and you do something like this:

SET @VarCharParameter = SCOPE_IDENTITY();

In which case, if your char/varchar is not large enough to store the int, it will become *, e.g.

SELECT CONVERT(CHAR(2), 10000);

The solution to this is to use the correct types. If you are looking to return an integer, then use an INT parameter.

Answer 2

It seems to be some types converting issue. Try just casting like:

return (string)parameter.Value;

Answer 3

Since stored procedure being used is not shown here, kindly make sure that OUTPUT keyword is used in StoredProcedure with the parameters required to be sent back for C# e.g. > @outputParameter Varchar(100) OUTPUT

Also while adding SQL parameters to cmd object in your C# code, check that the direction is set to output e.g.SqlParameter OutputParam = new SqlParameter("@OutputParam", SqlDbType.VarChar); OutputParam.Direction = ParameterDirection.Output; cmd.Parameters.Add(OutputParam);

At Last, try to close database connection (this.Db.Close()) after you have got everything you need from the cmd object.