Why isn't my SCOPE_IDENTITY() working?

17 Nov 2007 9:26 PM

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

18 Nov 2007 12:40 AM

Kerry Moorman

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

Show quote

"news.microsoft.com" wrote:

> 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
>
>
>

18 Nov 2007 4:39 AM

JX

try set nocount on

Show quote

"news.microsoft.com" <billgo***@charter.net> wrote in message
news:O1llgCWKIHA.4752@TK2MSFTNGP05.phx.gbl...
> 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
>
>

19 Nov 2007 10:28 AM

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).

--
Patrice

"news.microsoft.com" <billgo***@charter.net> a écrit dans le message de

news: O1llgCWKIHA.4***@TK2MSFTNGP05.phx.gbl...
Show quote

>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
>