How to get Inserted Id after ExecSQL()?

[Dmitry Konnov]

Hi All,

I do Insert to database by
INSERT INTO Table
sql statement with parameters and ExecSQL method.

How can i get newly created Id of record after ExecSQL() method is called?

thank you.
Dmitry

[Vladimir Melnikov 1389786979]

Post by Dmitry Konnov
How can i get newly created Id of record after ExecSQL() method is called?

Depends on sql server. For MS SQL you can use 'select @@identity' command.

[Brian Hollister]

When needed i like to do the following in my stored procs;


create proc dbo.InsAndRetID(
...
...
@LastID int output

INSERT INTO Table
(blah, blah)

select @LastID = @@IDENTITY



Then you can check your output param on the front side.

hth,

brian

[Dmitry Konnov]

Brian,

Post by Brian Hollister
create proc dbo.InsAndRetID(
...
...
@LastID int output
INSERT INTO Table
(blah, blah)

In my design INSERT INTO Table statement located on client side
and manually prepared for each dataset.
Are there way to submit 'INSERT INTO Table' statement as parameter to stored
procedure by means of T-SQL?

thank you.
Dmitry

[Dmitry Konnov]

Hi Vladimir,
I do.
TADOConnection.StartTrans;

TADOQuery.ExecSQL(INSERT INTO.....)
TADOQuery.ExecSQL('select @@identity)

TADOConnection.CommitTrans;

are there guarantee that the @@identity will be session unique and tha
autoincrement Id not belong to inserts of other usesrs?

thank you.

Dmitry.

[Kevin Frevert]

Post by Dmitry Konnov
Hi Vladimir,
I do.
TADOConnection.StartTrans;
TADOQuery.ExecSQL(INSERT INTO.....)
TADOConnection.CommitTrans;
autoincrement Id not belong to inserts of other usesrs?

Yes, it should (I've never had an issue with using @@Identity), but to
'guarantee' uniqueness, take a look at Scope_Identity in Books Online.

Depending on how complicated the insert is, you might look into creating a
stored procedure to insert your values and return the next id as the stored
procedure's return value.

Something like *
Declare
@NextID int

SET NOCOUNT ON -- Supresses the 'Rows affected' message that can 'confuse'
ADO.

Insert into dbo.MyTable
(MyField1, MyField2)
Values
(@MyField1InputParameter, @MyField2InputParameter)

SET @NextID = Scope_Identity()

Return @NextID

*Code is off the top of my head, so there might be syntax errors.

Good luck,
krf

[Dmitry Konnov]

Kevin

Post by Kevin Frevert
Something like *
Declare
@NextID int
SET NOCOUNT ON -- Supresses the 'Rows affected' message that can 'confuse'
ADO.
Insert into dbo.MyTable
(MyField1, MyField2)
Values
*Code is off the top of my head, so there might be syntax errors.

I will check weather T-SQL statements are allowed directly from ADO.

thank you
Dmitry

[Kevin Frevert]

Post by Dmitry Konnov

Post by Kevin Frevert
*Code is off the top of my head, so there might be syntax errors.

I will check weather T-SQL statements are allowed directly from ADO.

They are. I recommend using TADOCommand.

I've posted an example to borland.public.attachments to demostrate.

Good luck,
krf

[Brian Bushay TeamB]

Post by Dmitry Konnov
I do Insert to database by
INSERT INTO Table
sql statement with parameters and ExecSQL method.
How can i get newly created Id of record after ExecSQL() method is called?

If you insert a record and post it using a TadoDataset the value of the Identity
field will be returned when you post it.
--
Brian Bushay (TeamB)
***@NMPLS.com

[Dmitry Konnov]

Brian

Post by Brian Bushay TeamB
If you insert a record and post it using a TadoDataset the value of the Identity
field will be returned when you post it.

You mean i have previocely
SELECT FROM Table to TADODataSet
then
TADODataSet->Append()
and
TADODataSet->Post()

then this is not an issue.
I use just INSERT INTO which directly inserts values to database.


thank you.
Dmitry

[Brian Bushay TeamB]

Post by Kevin Frevert

Post by Brian Bushay TeamB
If you insert a record and post it using a TadoDataset the value of the

Identity

Post by Brian Bushay TeamB
field will be returned when you post it.

You mean i have previocely
SELECT FROM Table to TADODataSet
then
TADODataSet->Append()
and
TADODataSet->Post()
then this is not an issue.

Yes this is the way ADO is designed to work although it will depend on the Oledb
driver you use.

Post by Kevin Frevert
I use just INSERT INTO which directly inserts values to database.

and bypasses ADO record entry
--
Brian Bushay (TeamB)
***@NMPLS.com

[Dmitry Konnov]

Brian

Post by Brian Bushay TeamB

Post by Dmitry Konnov
You mean i have previocely
SELECT FROM Table to TADODataSet
then
TADODataSet->Append()
and
TADODataSet->Post()
then this is not an issue.

Yes this is the way ADO is designed to work although it will depend on the Oledb
driver you use.

I dont want to update database this way, because this creates extra network
traffic.
As you can see - to insert record you previoucely select it.

Post by Brian Bushay TeamB

Post by Dmitry Konnov
I use just INSERT INTO which directly inserts values to database.

and bypasses ADO record entry

Yes. bypasses ADO record entry.
But i think I'm on the right path.

Dmitry.