Returning scope identity using sqlCommand.ExecuteNonQuery()

Question

I have a stored procedure that looks like so.

ALTER PROCEDURE dbo.addPackage(

    @PackageStatus                      VARCHAR(50) = null,
    @OrgCode                            VARCHAR(50) = null,
    @SystemID                           VARCHAR(50) = null,
    @ID                               int OUTPUT
)
AS
BEGIN
    insert into package_table
    (
            PackageStatus,
            OrgCode,
            SystemID
            )
            values
            (@PackageStatus, 
            @OrgCode, 
            @SystemID
            )
            SET @ID =  SCOPE_IDENTITY()
END

And my function adds the following parameters to the sql command and calls sqlCommand.ExecuteNonQuery();

List<SqlParameter> parameters = new List<SqlParameter>();

parameters.Add(new SqlParameter("@PackageStatus", package.PackageStatus));
parameters.Add(new SqlParameter("@OrgCode", package.OrgCode));
 parameters.Add(new SqlParameter("@SystemID", package.SystemID));

SqlParameter outParameter = new SqlParameter("@ID", SqlDbType.Int);
outParameter.Direction = ParameterDirection.Output;
parameters.Add(outParameter);

but I keep getting a 0 back from the stored porcuedure evne though I have an out parameter declared and set it before the stored procedure exits. The record is successfully added with the auto incrementing value for the ID but the ID is not returned when I get the output parameter back. It is set to 0.

Any suggestions?

Can you simply return the value as a scalar instead of an output parameter? Easier to handle, IMHO. — sfuqua, Dec 13 '11 at 17:06
Your code seems incorrect. Your parameter name is defined as iD (lower case I), The SET statement uses ID (upper case I) and your code to get the value of the output parameter uses APID which is completely different. Can you please post the actual code you are using? — Chris Dunaway, Dec 13 '11 at 17:17
I had altered my code in notepad ++. The actual values are correct. I just wanted to make it as generic as possible. That wasn't the reason for the error. I have since found what the problem was. — gsirianni, Dec 13 '11 at 21:44

score 4 · Accepted Answer · answered Dec 13 '11 at 17:06

4

You've defined the output parameter - but do you also read it out after your call to .ExecuteNonQuery() ?? Unfortunately, you're not showing that code...

Basically, you need

SqlCommand cmd = new SqlCommand(..., connection); .....

// set up your parameter as above

connection.Open();
cmd.ExecuteNonQuery();

var outputValue = cmd.Parameters["@APID"].Value;  // you need to read it out!

connection.Close();

Are you doing this?? And that's where you're getting 0 back all the time??

The output parameter is NOT the return value from the .ExecuteNonQuery() call - you need to explicitly read out the parameter value after the call.

answered Dec 13 '11 at 17:06

marc_s

732,580
175
1,330
1,459

Yes I am performing this call some where in my code. APID = Convert.ToInt32(sqlCommand.Parameters["@APID"].Value); is NULL when I set the break point and retrieve this value. So the Convert.ToInt32 sets it to 0; – gsirianni Dec 13 '11 at 18:38
1

Though I had everything you suggested already you're answer helped me deiscover the real problem so I upvoted it. There were 51 parameters and one of them wasn't set to NULL by default causing the statement to fail and thus returning the 0. – gsirianni Dec 13 '11 at 18:53

Returning scope identity using sqlCommand.ExecuteNonQuery()

1 Answers1