get back the identity of a newly inserted row thru ODBC

get back the identity of a newly inserted row thru ODBC

Post by Wendy Lie » Fri, 04 Apr 1997 04:00:00



Hi:
        using VC++'s odbc driver connecting to a SQLServer 6.5 database
        i have a table which contains an identity column

        after begin trans, open recordset, addnew() and update() [successfully] to
this table, i want to get back the identity column

so that i can
        end trans() successfully.

        is this possible ?  any help is appreciated.

thanks.
wendy

 
 
 

1. Getting the identity value of a newly inserted row

Hi,

I have a question regarding identity columns in SQL Server 7.

Sometimes, after inserting a row into a table using ADO, you would want to
get the identity created for the newly inserted row. For instance, if table
EMPLOYEE has an identity column called EmployeeId, after calling
Recordset.Update, you can read Recordset("EmployeeId").Value and get the
identity value assigned to that newly inserted row. This works okay unless
EMPLOYEE table has an insert-trigger that inserts rows to some other table.
Say, after inserting a new EMPLOYEE row, you must add into the AUDIT table
that "employee so and so has been added." , and the primary key for the
AUDIT table is another identity column (called AuditID). If EMPLOYEE table
has an insert-trigger as described above, after calling Recordset.Update
method, what you read from Recordset("EmployeeID").Value is the number that
was auto-generated for the identity column of AUDIT table, and not the
number that was auto-generated for the identity column of EMPLOYEE table. I

was added, the identity of the AUDIT table was the one that was returned
instead.

So, me and my comworker think that an alternative solution to reading the
identity value after a recordset update is by doing something like "Select
Max(EmployeeID) From EMPLOYEE". This strategy looks like it's going to work
90% of the time. But our concern is, if the database is busy (many
concurrent users doing concurrent inserts on the same tables), the "Select
Max(IdentityColumn)" strategy might not work.

I guess my main question is: Is there an efficient, bullet-proof way of
retrieving the newly created identity column value for a row that was just
inserted into a table that fires a trigger which in turn inserts a row into
another table that also has an identity column?

Any tips, recommendations would be greatly appreciated. Thanks!

-Gabe

2. FP application performance over leased line

3. Getting IDENTITY value of newly inserted row

4. VC++ Crashes with ACCESS runtime

5. Identity cannot be determined for newly inserted rows

6. 16 & 32 bit Registry entries

7. Error - The provider is unable to determine identity for newly inserted rows (0x80040E1B)

8. Help: latest version of ORACLE server for ODBC, suppporting MutilThreading?

9. The provider is unable to determine identity for newly inserted rows

10. Get Identity Number for Newly Inserted Row

11. Identity Number of Newly Inserted Row

12. How to get Identity column values for newly inserted row

13. Identity cannot be determined for newly inserted rows