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!

It seems to be some types converting issue. What is the type of "parameter.Value" at the moment of calling "Convert.ToString"? Try just casting like "return (string)parameter.Value;" — user6416335
– user6416335, Commented Nov 29, 2017 at 13:57
is it possible that you are casting from byte to string or something like that? — Ferus7
– Ferus7, Commented Nov 29, 2017 at 14:03
What is the SQL data type of the output parameter? Try executing the proc from SSMS to see if the * is returned. — Dan Guzman
– Dan Guzman, Commented Nov 29, 2017 at 14:04
On your stored procedure, is there another parameter of type Output or InputOutput that is returning a value before the parameter you intend to get the value from? — Grax32
– Grax32, Commented Nov 29, 2017 at 14:05

GarethD · Accepted Answer · 2017-11-29 14:08:20Z

2

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.

answered Nov 29, 2017 at 14:08

GarethD

70k11 gold badges90 silver badges130 bronze badges

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

1 Comment

Jan Solo Over a year ago

Thanks. With this answer I was able to figure out what the problem was. Turns out, I defined my output parameter as the wrong type (DBtype).

user6416335 · Accepted Answer · 2017-11-29 14:01:00Z

0

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

return (string)parameter.Value;

answered Nov 29, 2017 at 14:01

user6416335

Comments

SSD · Accepted Answer · 2017-11-29 14:24:51Z

0

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.

answered Nov 29, 2017 at 14:24

SSD

1,4012 gold badges14 silver badges20 bronze badges

Collectives™ on Stack Overflow

C# changes output parameter to invalid value and throws error

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related