CODEWITHSUNDEEP
c#sqlsql-serverstored-proceduresoutput-parameter
Jan Solo
Jan Solo

Reputation: 193

C# changes output parameter to invalid value and throws error

Using:

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!

Upvotes: 1

Views: 1214

Answers (3)

SSD
SSD

Reputation: 1391

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.

Upvotes: 0

GarethD
GarethD

Reputation: 69789

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.

Upvotes: 2

user6416335
user6416335

Reputation:

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

return (string)parameter.Value;

Upvotes: 0

Related Questions