Returning Identity

[Steve Jorgensen]

I'ts not quite as efficient, but why not use a recordset to add the record and
read the IDENTITY value that is assigned? For best performance, include a where
clause in the SQL that generates the recordset that returns no rows (e.g. FooID
IS NULL).

 

[Spokes]

I have a project where I want to dynamically build an INSERT SQL statement
and execute it, either through a connection or command. The PKs for the
tables on the BE are identity fields. The INSERT works fine. My problem is
returning value of the identity to the front end. I know how to return the
value from a stored procedure. I really don't want to write a stored
procedure and the corresponding sub procedure in VBA for every table.

Any thoughts?

Thanks.

Bob

 

[Peter Wilson]

You want something like the following where VendorId is an
identity column in the Vendor table:


Alter Procedure spNewVendor
(
@NewVendorId int OUTPUT
)
As
INSERT tblVendor (VendorNumber, VendorName)
VALUES (9999, 'New Vendor')
SET @NewVendorId = @@IDENTITY

 

[Vadim Rapp]

S> I have a project where I want to dynamically build an INSERT SQL
S> statement and execute it, either through a connection or command.
S> The PKs for the tables on the BE are identity fields. The INSERT
S> works fine. My problem is returning value of the identity to the
S> front end. I know how to return the value from a stored
S> procedure. I really don't want to write a stored procedure and the
S> corresponding sub procedure in VBA for every table.

S> Any thoughts?

NewId = adoConnection.Execute("insert into table1 (cc) values('cc') select
scope_identity()").NextRecordset.Fields(0)


Vadim

 

[Spokes]

Vadim, most excellent. That's what I needed!

I can't thank you enough.

Bob

 

[Steve Jorgensen]

Note that this will work only so long as you never try to use a recodset to
insert the row. If you ever might need to do that, you should make sure you
don't use IDENTITY columns on tables inserted into from triggers.

 

[Spokes]

Thanks for the advice. Luckily, in this project, I'm not using any
recordsets to insert rows.

Bob

 

[Peter Wilson]

Sorry,
I did not read the question carefully enough.