Board index » delphi » Identity field using MS-SQL Server

Identity field using MS-SQL Server

What is necessary to BDE return me the value of the Identity field when I
post one insertion record.
When I'm trying to insert one record in any table, the BDE cannot return me
what Identity value has this new record and how this value is not returned,
the relationship with other tables is broken.
 

Re:Identity field using MS-SQL Server


Hi,
You can query the @@IDENTITY value.

Ex.

query1.SQL.Clear;
query1.SQL.Add('insert into ...');
query1.ExecSQL;

query1.SQL.Clear;
query1.SQL.Add('select @@IDENTITY ');
query1.Open;

myVar := query1.Fields[0].AsInteger;

Thanks,

Imran.

===========================================
Imran Hussain
MCP, MCSD
imr...@imranweb.com
FOR FREE Software http://www.imranweb.com/freesoft
===========================================

Quote
Pegoraro wrote:
> What is necessary to BDE return me the value of the Identity field when I
> post one insertion record.
> When I'm trying to insert one record in any table, the BDE cannot return me
> what Identity value has this new record and how this value is not returned,
> the relationship with other tables is broken.

--

Re:Identity field using MS-SQL Server


The best answer to this is not to use identity fields, but to formulate your
own auto-generation routine. A simple (and effective) method is to use a
table which holds a string to describe the table, and an integer which holds
the next number to use. Using a separate database connection from your
applications one, and careful use of transactions, you can write a routine
to obtain this number for the table required, add one to it and update the
auto-gen table with the new value. You then have a unique number which can
be used in before post to assign to what used to be your identity field.
This may sound long winded, but the advantage of it is that it works with
any transaction logging database, not just Sql-Server.
Quote
Pegoraro wrote in message <7bornv$7...@forums.borland.com>...
>What is necessary to BDE return me the value of the Identity field when I
>post one insertion record.
>When I'm trying to insert one record in any table, the BDE cannot return me
>what Identity value has this new record and how this value is not returned,
>the relationship with other tables is broken.

Re:Identity field using MS-SQL Server


In article <7bornv$7...@forums.borland.com>,
  "Pegoraro" <mpegor...@alltech-bio.com> wrote:

Quote
> What is necessary to BDE return me the value of the Identity field when I
> post one insertion record.
> When I'm trying to insert one record in any table, the BDE cannot return me
> what Identity value has this new record and how this value is not returned,
> the relationship with other tables is broken.

This is a very frequently asked question, and the answer is usually don't use
identity fields because the bde either can't give it back to you, or it
doesn't work with them easily.  Others will know more, I'm sure.  This is
just what I've read over time.

Try giving www.dejanews.com's power search a try to look up identity column in
delphi groups and see what turns up.

Paul Munn, pm...@my-dejanews.com

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own    

Re:Identity field using MS-SQL Server


Pegoraro escreveu na mensagem <7bornv$7...@forums.borland.com>...

Quote
>What is necessary to BDE return me the value of the Identity field when I
>post one insertion record.
>When I'm trying to insert one record in any table, the BDE cannot return me
>what Identity value has this new record and how this value is not returned,
>the relationship with other tables is broken.

Use stored procedures to do that

Ex.

create procedure InsertInTable
@Param1 type,
@Param2 type,
...
as
insert into MyTable (Field1,Field2,...)
(@param1, @Param2,...)

Return @@IDENTITY

Call the procedure to insert  a new record and get the identity field using
the result parameter of stored procedure.

Avoid to use identity on detail tables. I got problams using dbgrids...

[]'s

Mamede Milanez

Re:Identity field using MS-SQL Server


In the same session as the insert, have the very next statement as

select @@identity

This will be the latest value from an identity column used.

George Aligianis

Other Threads