Delphi ADO parameterized insert SQL Statement and SELECT SCOPE_IDENTITY() problem

• Previous message: Mike Shkolnik: "Re: loading fixed width text file to Access table using Delphi 4"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Date: 29 Oct 2003 23:33:14 -0800

hi

I hope you ADO Gurus can help me..

I am using Delphi 6 with SQL Server 2000 using mdac 2.7 and using the
TADOCommand in a multi tiered app.

Here is my problem explained.

I am inserting into a table with an identity column. I need to
retrieve the freshly created identity value back so that i can use it
in delphi code.
I use the SELECT SCOPE_IDENTITY() SQL statement to retrieve the new
identity value back as this table has triggers that also insert into
other identity columns and therefore SELECT @@IDENTITY does not work..

So if I fire the SELECT SCOPE_IDENTITY() statement after the following
standard insert statement i get the correct identity value..

  INSERT INTO ATable (FirstName ) Values ('ROGER')

HOWEVER HOWEVER

if I fire the SELECT SCOPE_IDENTITY() statement after a PARAMETERIZED
INSERT
statement then i ALWAYS get a returned identity value of 0 (Zero)

for eg..

  INSERT INTO ATable (FirstName ) Values (:FIRSTNAME)

I am stunned that this works for standard queries and not for
parameterized queries as BOTH insert statements WORK in regards the
insert into the table... but somehow the combination of this
parameterized query with the SELECT SCOPE_IDENTITY() does not return
the identity value correctly.....

PLEASE HELP......

Thanks in advance for your help and time.
Edwin

• Previous message: Mike Shkolnik: "Re: loading fixed width text file to Access table using Delphi 4"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]