Returning Identity

  • Thread starter Thread starter Steve Jorgensen
  • Start date Start date
S

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).
 
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
 
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
 
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
 
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.
 
Thanks for the advice. Luckily, in this project, I'm not using any
recordsets to insert rows.

Bob
 
Back
Top