Re: Identiy retrieval within SqlCeTransaction

Hi João, thanks for your response!

I've read the two articles, and one question still bothers me.

What if I am creating multiple items in a table with an identity field, all
within the same transaction?

Example with "pseudocode":

Table t1 and t2 has identity fields, table t3 doesn't.

BeginTransaction
foreach row while( no more rows in file )
if row is for t1
insert into t1
save identity value of t1
if row is for t2
insert into t2 (using identity value from t1)
save identity value of t2
if row is for t3
insert into t3 (using identity value from t2)
endwhile
EndTransaction

Do you understand what I am concerned about?

Thanks,

Peter


"João Paulo Figueira" <joao.figueira@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:3d207d0322cfa8c8d0c99e552e90@xxxxxxxxxxxxxxxxxxxxx
Hello Peter,

You can find information on MSDN here:
http://msdn2.microsoft.com/en-us/library/ms174156.aspx

I wtote a short article on this subject:

http://www.pocketpcdn.com/articles/articles.php?&atb.set(c_id)=74&atb.set(a_id)=6918&atb.perform(details)=&

Regards,
João Paulo Figueira
Device Application Development MVP

Thanks for replying!

I'm not sure how this works in my scenario, is this documented in
sqlbooks, or is there an article about it?

Thanks,

Peter

"João Paulo Figueira" <joao.figueira@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote
in message news:3d207d0322c438c8d09be3ea57a0@xxxxxxxxxxxxxxxxxxxxx

Hello Peter,

If you are using SQL Mobile (now Compact Edition) you can prefetch
the next IDENTITY value by querying the INFORMATION_SCHEMA.COLUMNS
schema table.

Regards,
João Paulo Figueira
Device Application Development MVP
Hi!

Is it possible to retrieve the identity value of a newly inserted
row using SELECT @@IDENTITY when you are executing within a
transaction?

Sample code overview:
sqlCmd1.CommandText = "INSERT INTO T1 VALUES..."
sqlCmd2.CommandText = "INSERT INTO T2 VALUES..."
sqlCmd3.CommandText = "SELECT @@IDENTITY FROM T1"
SqlCeTransaction tx = sqlConn.BeginTransaction();
sqlCmd1.Transaction = tx;
sqlCmd2.Transaction = tx;
sqlCmd3.Transaction = tx;
try
{
sqlCmd1.ExectueNonQuery();
string id = sqlCmd3.ExecuteScalar().ToString(); <--
NullReferenceException
sqlCmd.Parameters[0] = id;
sqlCmd2.ExectueNonQuery();
tx.Commit();
}
catch( Exception )
{
tx.RollBack();
}
finally
{
sqlCmd1.Transaction = null;
sqlCmd2.Transaction = null;
sqlCmd3.Transaction = null;
}
Is the reason for the NullRefExc that the identity value isn't set
until the transaction is commited? How do you workaround this
problem
when using transactions and identity fields?
Thanks in advance,

Peter





.

Relevant Pages

  • Re: Identiy retrieval within SqlCeTransaction
    ... It also has the advantage of allowing you to specify the table, where the @@IDENTITY macro will retrieve the last one. ... Device Application Development MVP ... all within the same transaction? ... Table t1 and t2 has identity fields, ...
    (microsoft.public.sqlserver.ce)
  • DataSet Transactions Help
    ... and as most of the table keys are Identity fields I had to write stored ... using Identity fields I now get an exception when I dont attach the ... transaction to the other Dataadapter commands such as its Read, ... Update commands. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Identiy retrieval within SqlCeTransaction
    ... João Paulo Figueira ... Device Application Development MVP ... NullReferenceException ... until the transaction is commited? ...
    (microsoft.public.sqlserver.ce)