Board index » delphi » Using adoCommand with a table that has an identity column

Using adoCommand with a table that has an identity column


2007-05-08 05:04:52 PM
delphi239
Hej,
I have a table in MS SQL Sever that has an identity column.
I want to insert a new row in the table and return the identity value. Is
this possible?
I was experimenting with the following:
ADODataSet1.Recordset := ADOCommand1.Execute;
I was hoping that the dataset would contain the newly created row... but I
can not get it to work.
Any ideas or other solution sto the problem?
/Richard
 
 

Re:Using adoCommand with a table that has an identity column

Richard Bibby skrev:
Quote
Hej,

I have a table in MS SQL Sever that has an identity column.

I want to insert a new row in the table and return the identity value. Is
this possible?

Hej!
After the insert you can query @@IDENTITY to get the last identity value
generated.
ADODataSet.CommandText := 'SELECT @@Identity AS COLID';
regards
/Micke
 

Re:Using adoCommand with a table that has an identity column

On Tue, 08 May 2007 13:15:25 +0100, Mikael Eriksson <XXXX@XXXXX.COM>
writes:
Quote
>I have a table in MS SQL Sever that has an identity column.
>I want to insert a new row in the table and return the identity
>value. Is this possible?
After the insert you can query @@IDENTITY to get the last identity value
generated.
ADODataSet.CommandText := 'SELECT @@Identity AS COLID';
I see in my code that I changed to using "select scope_identity as..."
rather than
"select @@identity"
Thanks to the original poster (and google) for reminding me of the
following...
Also worth pointing out the difference between @@IDENTITY and
SCOPE_IDENTITY()
@@IDENTITY returns the last ID generated for an identity column in any
table.
SCOPE_IDENTITY returns the last ID generated in the caller scope.
The main difference is that if your statement inserts a table with an
identity column, and that table has a trigger that populates another
table with an identity column, @@IDENTITY will return the ID generated
for the table populated by the trigger, while SCOPE_IDENTITY() will
return the ID for the table specified in the insert statement.
--
Paul Scott
Information Management Systems
Macclesfield, UK.
 

Re:Using adoCommand with a table that has an identity column

Richard,
I've uploaded an example to borland.public.attachments.
Good luck,
krf
"Richard Bibby" <XXXX@XXXXX.COM>writes
Quote
Hej,

I have a table in MS SQL Sever that has an identity column.

I want to insert a new row in the table and return the identity value. Is
this possible?

I was experimenting with the following:

ADODataSet1.Recordset := ADOCommand1.Execute;

I was hoping that the dataset would contain the newly created row... but I
can not get it to work.

Any ideas or other solution sto the problem?

/Richard

 

Re:Using adoCommand with a table that has an identity column

THanks for the help!
"Kevin Frevert" <XXXX@XXXXX.COM>writes
Quote
Richard,

I've uploaded an example to borland.public.attachments.

Good luck,
krf

"Richard Bibby" <XXXX@XXXXX.COM>writes
news:46403d36$XXXX@XXXXX.COM...
>Hej,
>
>I have a table in MS SQL Sever that has an identity column.
>
>I want to insert a new row in the table and return the identity value.
>Is this possible?
>
>I was experimenting with the following:
>
>ADODataSet1.Recordset := ADOCommand1.Execute;
>
>I was hoping that the dataset would contain the newly created row... but
>I can't get it to work.
>
>Any ideas or other solution sto the problem?
>
>/Richard
>