Solved

Getting Identity of row inserted id MS SQL database

Posted on 2004-10-21
224 Views
Last Modified: 2010-04-05
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.


0
Question by:alanjbrown
    6 Comments
     
    LVL 17

    Accepted Solution

    by:
    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
     
    LVL 1

    Author Comment

    by:alanjbrown
    Dosn't that leave the posability of the identity being incremented by another user in between the two operations?

    regards

    Alan
    0
     
    LVL 17

    Expert Comment

    by:geobul
    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
     
    LVL 17

    Expert Comment

    by:geobul
    @@IDENTITY is in the scope of your current session and therefore can't interfere with other users.
    0
     
    LVL 1

    Author Comment

    by:alanjbrown
    Thanks for the rapid response

    Alan
    0
     
    LVL 17

    Expert Comment

    by:geobul
    The pleasure was mine :-)
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
    Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
    Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    933 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now