SELECT @@IDENTITY returns 0 in Transaction

[Banski]

Hi,

Im trying to use SELECT @@IDENTITY in a transaction. But it always returns 0.
Im using an ms access database. And using the following code.
What am i doing wrong?

Best regards banski

public int SavePage(int id....)
{
int SomeId = 0;
string myDNS = ConfigurationSettings.AppSettings["ConnectionString"];
OleDbConnection myConn = new OleDbConnection(myDNS);
myConn.Open();
OleDbTransaction myTrans = myConn.BeginTransaction();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = myConn;
cmd.Transaction = myTrans;
try
{
cmd.CommandText = "InsertDataData";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@PageTypeId", OleDbType.Integer).Value = PageTypeId;
//adding more parameters
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();

cmd.CommandText = "SELECT @@IDENTITY";
SomeId = (int)cmd.ExecuteScalar();

//Making more inserts using identity
......
......

myTrans.Commit();

}
catch(OleDbException e)
{

Console.WriteLine(e.ToString());

myTrans.Rollback();
}
finally
{
myConn.Close();
}
return SomeId;

}

[Mark Fitzpatrick]

My guess is that you actually have two seperate operations going on. Even
though the transaction is the same it's no longer in the same scope. A scope
has a much shorter lifespan. Your best bet is to actually try to do your
insert operation, or whatever is being performed in the ExecuteNonQuery, and
select the @@Identity value at the same time, otherwise they won't be
considered as being in the same scope.

Hope this helps,
Mark Fitzpatrick
Microsoft MVP - FrontPage

"Banski" <th********@hotmail.com> wrote in message
news:a1**************************@posting.google.c om...

Hi,

Im trying to use SELECT @@IDENTITY in a transaction. But it always returns
0.
Im using an ms access database. And using the following code.
What am i doing wrong?

Best regards banski

public int SavePage(int id....)
{
int SomeId = 0;
string myDNS = ConfigurationSettings.AppSettings["ConnectionString"];
OleDbConnection myConn = new OleDbConnection(myDNS);
myConn.Open();
OleDbTransaction myTrans = myConn.BeginTransaction();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = myConn;
cmd.Transaction = myTrans;
try
{
cmd.CommandText = "InsertDataData";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@PageTypeId", OleDbType.Integer).Value = PageTypeId;
//adding more parameters
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();

cmd.CommandText = "SELECT @@IDENTITY";
SomeId = (int)cmd.ExecuteScalar();

//Making more inserts using identity
......
......

myTrans.Commit();

}
catch(OleDbException e)
{

Console.WriteLine(e.ToString());

myTrans.Rollback();
}
finally
{
myConn.Close();
}
return SomeId;

}

[jongalloway]

I recommend you change your insert procedure to return SCOPE_IDENTITY
since @@identity is a global variable and will return the identity of
the last inserted value within your transaction.

In my experience, the return of SCOPE_IDENTITY is not handled
automatically. I needed to cast it to a decimal and then parse back to
an Int32.

Sample SP change:
---
ALTER PROCEDURE InsertDataData
@PageTypeId int
AS
INSERT INTO SampleTable
(
PageTypeId
)
VALUES
(
@PageTypeId
)

--Do other processing...

--Return Inserted Row's ID
SELECT SCOPE_IDENTITY()
---

That would change your ADO code to this:
{
cmd.CommandText = "InsertDataData";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@PageTypeId", OleDbType.Integer).Value =
PageTypeId;
//adding more parameters
SomeId = decimal.ToInt32((decimal)cmd.ExecuteScalar());

//Making more inserts using identity
.......
.......
myTrans.Commit();
}

- Jon
http://weblogs.asp.net/jgalloway