Why isn't my SCOPE_IDENTITY() working?

[news.microsoft.com]

I have a table called Activities with a primary key of ActivityId which is
an identity field. I can see the value after the insert is there. But my
return back from the ExecuteScalar method call is not working.

private const string SQL_INSERT_ACTIVITY = "INSERT INTO
APActivities(ActivityName,Description, " +
"Start,[End],CoordinatorId,ActivityTypeId,LocationId)
VALUES(@ActivityName,@Description, " +
"@Start,@End,@CoordinatorId,@ActivityTypeId,@LocationId); SELECT @ActivityId
= SCOPE_IDENTITY()";

here is my call for the insert.

Open(ConfigurationManager.AppSettings["SQL_CONN_STRING"]);
activity.ActivityId =
Convert.ToInt32(SQLHelper.ExecuteScalar(_internalConnection,
_internalADOTransaction, CommandType.Text, SQL_INSERT_ACTIVITY,
activityParams));

here is my ExecuteScalar

public static int ExecuteScalar(SqlConnection conn, SqlTransaction trans,
CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = cmdText;
cmd.Connection = conn;
if (trans != null)
cmd.Transaction = trans;
PrepareCommand(cmd, cmdParms);
int val = Convert.ToInt32(cmd.ExecuteScalar());
return val;
}

The variable val is holding 0 after the call cmd.ExecuteScalar() returns.
But if I look at the table the row is there with the identity column filled
in.

Bill

 

[Guest]

Bill,

I don't know if this makes any differences, but I use "Select
Scope_Identity()" as the select that I batch with an insert statement and
retrieve using ExecuteScalar.

Kerry Moorman

 

[JX]

try set nocount on

 

[Patrice]

SELECT @var= etc.. doesn't return anything client side. Use SELECT
SCOPE_IDENTITY() instead (or add SELECT @ActivityId if you really need to
keep this value).