How to get rowid of uncommitted record

I have two tables - header, detail. HeaderID appears in both, is an
auto-incrementing rowID in the header table, and required also in the detail
table. It's a one-to-many relationship from the header table to the detail
table.
The code I have writing to these tables really needs to write the detail
first, and then summariize and write the header infomation, however I can't
write the detail until I get the HeaderID by writing a header record.
So...If I write a record to the header table as a placeholder, how can I
get the HeaderID of that record while I am writing it, to use it in the
detail table (or for that matter reserve a HeaderID to be written later). I
t
is the only unique ID in the header table, and I don't have anything else
that I can uniquely use to go back and get the record just written to get th
e
header table. Here's the further challenge - all of these are uncomitted
transactions until both detail and header records for the transaction have
been written, at which point I will commit them.
Tell me there is some magic way to get the id of a record I am about to
write, am writing, or have written. Help!
Thanks!Matt wrote on Fri, 2 Dec 2005 07:58:03 -0800:

> I have two tables - header, detail. HeaderID appears in both, is an
> auto-incrementing rowID in the header table, and required also in the
> detail table. It's a one-to-many relationship from the header table to
> the detail table.
> The code I have writing to these tables really needs to write the detail
> first, and then summariize and write the header infomation, however I
> can't write the detail until I get the HeaderID by writing a header
> record.
> So...If I write a record to the header table as a placeholder, how can I
> get the HeaderID of that record while I am writing it, to use it in the
> detail table (or for that matter reserve a HeaderID to be written later).
> It is the only unique ID in the header table, and I don't have anything
> else that I can uniquely use to go back and get the record just written to
> get the header table. Here's the further challenge - all of these are
> uncomitted transactions until both detail and header records for the
> transaction have been written, at which point I will commit them.
> Tell me there is some magic way to get the id of a record I am about to
> write, am writing, or have written. Help!
> Thanks!
What are you using to create the records? You should have no trouble getting
the rowid of the inserted header record. For instance, I myself use ADO to
create a header record in a table inside a transaction, and pull back the
identity value exactly the same way as if it wasn't in a transaction - I
just read it from the column. I then create the detail lines to go with it,
and at the end commit the transaction. If I have to rollback, both the
header and the detail lines get rolled back. There should be no need to be
doing anything special - this is exactly the same code I was using before I
added a transaction (which was simply of case of adding a BeginTrans ADO
Connection method call before the header record was created, and a
CommitTrans method call at the end).
Dan|||Look at scope_identity() in Books Online.
It doesn't matter if the transaction is not committed.
So, after you do your first insert:
declare @.headerID INT
insert...
set @.headerID = scope_identity()
Don't forget to handle errors.
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:911A2464-EE82-4A4D-999E-402C2DD6AAC4@.microsoft.com...
>I have two tables - header, detail. HeaderID appears in both, is an
> auto-incrementing rowID in the header table, and required also in the
> detail
> table. It's a one-to-many relationship from the header table to the
> detail
> table.
> The code I have writing to these tables really needs to write the detail
> first, and then summariize and write the header infomation, however I
> can't
> write the detail until I get the HeaderID by writing a header record.
> So...If I write a record to the header table as a placeholder, how can I
> get the HeaderID of that record while I am writing it, to use it in the
> detail table (or for that matter reserve a HeaderID to be written later).
> It
> is the only unique ID in the header table, and I don't have anything else
> that I can uniquely use to go back and get the record just written to get
> the
> header table. Here's the further challenge - all of these are uncomitted
> transactions until both detail and header records for the transaction have
> been written, at which point I will commit them.
> Tell me there is some magic way to get the id of a record I am about to
> write, am writing, or have written. Help!
> Thanks!|||Im using .NET SQLConnection and updating with a SQLcommand.executenonquery
command. the HeaderID is an identity colum increments by the DB. I've just
never had to do this beofre so this is new terrirotry. I guess my question
is how either when I am executing the command or after I execute it i can ge
t
the assigned headerID. Understand that this DB will process millions of
trx/day, so I can't just write a record and go back and get the max id as
something/someone else could have easly incremented it in the meantime. How
are you going back to get the id regardless of it being within a transaction
?
Thanks!
"Daniel Crichton" wrote:

> Matt wrote on Fri, 2 Dec 2005 07:58:03 -0800:
>
>
> What are you using to create the records? You should have no trouble getti
ng
> the rowid of the inserted header record. For instance, I myself use ADO to
> create a header record in a table inside a transaction, and pull back the
> identity value exactly the same way as if it wasn't in a transaction - I
> just read it from the column. I then create the detail lines to go with it
,
> and at the end commit the transaction. If I have to rollback, both the
> header and the detail lines get rolled back. There should be no need to be
> doing anything special - this is exactly the same code I was using before
I
> added a transaction (which was simply of case of adding a BeginTrans ADO
> Connection method call before the header record was created, and a
> CommitTrans method call at the end).
> Dan
>
>|||Thanks!
"Raymond D'Anjou" wrote:

> Look at scope_identity() in Books Online.
> It doesn't matter if the transaction is not committed.
> So, after you do your first insert:
> declare @.headerID INT
> insert...
> set @.headerID = scope_identity()
> Don't forget to handle errors.
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:911A2464-EE82-4A4D-999E-402C2DD6AAC4@.microsoft.com...
>
>|||Matt wrote on Fri, 2 Dec 2005 08:31:07 -0800:

> Im using .NET SQLConnection and updating with a SQLcommand.executenonquery
> command. the HeaderID is an identity colum increments by the DB. I've
> just never had to do this beofre so this is new terrirotry. I guess my
> question is how either when I am executing the command or after I execute
> it i can get the assigned headerID. Understand that this DB will process
> millions of trx/day, so I can't just write a record and go back and get
> the max id as something/someone else could have easly incremented it in
> the meantime. How are you going back to get the id regardless of it being
> within a transaction? Thanks!
In ADO it's pretty simple - you just use a recordset Keyset cursor type with
a server-side cursor, write the values in to the new row, and after running
the .Update method of the recordset read the value of the identity field.
That's it. No messing around with making other calls back to SQL Server, or
trying to determine the row id in some other arcane way.
Dan