Getting Identity of row inserted id MS SQL database

I am trying to get the identity of a record added with an ado dataset the sql in the dataset is :-

Insert into Timetran
(casecode, personno, date .........)
Values
(:ccode , :pno, :tdate, ................)
select @@IDENTITY as 'Ident'

when I then open the adoDataset and try to get the value of the Identity with
adoDataset.fieldbyname('ident').asinteger returns 'Field not found'

Any help would be appreciated.


LVL 1
alanjbrownAsked:
Who is Participating?
 
geobulConnect With a Mentor Commented:
Hi,

Divide that in two separate sql executions:

Insert into Timetran
(casecode, personno, date .........)
Values
(:ccode , :pno, :tdate, ................)

using a TADOQuery or TADOCommand and

select @@IDENTITY as 'Ident'

in your TADODataset

Regards, Geo
0
 
alanjbrownAuthor Commented:
Dosn't that leave the posability of the identity being incremented by another user in between the two operations?

regards

Alan
0
 
geobulCommented:
select @@IDENTITY as 'Ident'
should be
select @@IDENTITY as Ident
I think. And these two components (ADOQuery and ADODataset) should use the same TADOConnection component without closing the connection in between the two calls.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
geobulCommented:
@@IDENTITY is in the scope of your current session and therefore can't interfere with other users.
0
 
alanjbrownAuthor Commented:
Thanks for the rapid response

Alan
0
 
geobulCommented:
The pleasure was mine :-)
0
All Courses

From novice to tech pro — start learning today.