Getting the identity value of a newly inserted row

Getting the identity value of a newly inserted row

Post by Gabe Hilad » Thu, 18 Oct 2001 05:00:14



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

 
 
 

Getting the identity value of a newly inserted row

Post by HC » Fri, 19 Oct 2001 02:46:31


I am not sure will this help
In your trigger,add following statement right after
inserting a row into Employee table


HC

Quote:>-----Original Message-----
>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
Quote:>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
Quote:>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
>guess this behavior has something to do with ADO

referencing the global

after EMPLOYEE row
>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
Quote:>identity value after a recordset update is by doing

something like "Select
Quote:>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
Quote:>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

>.


 
 
 

Getting the identity value of a newly inserted row

Post by Kally Wang( » Sat, 20 Oct 2001 14:48:42


Hi Gabe,

To get the correct identity value with an insert trigger, please specify
the dynamic property: Server Data on Insert provided by Microsoft OLE DB
Provider for SQL Server (SQLOLEDB). The property specifies whether an
application can retrieve values from the database for newly inserted rows.
The following sample code may help you to solve the problem:

Note: Please use server cursor instead of client cursor and the property
only works when cursor type is adOpenKeyset.
   Dim cn As New ADODB.Connection
   Dim rs As New ADODB.Recordset
   cn.ConnectionString =
"Provider=SQLOLEDB;Server=yourServer;Database=yourDatabase;uid=sa;pwd=;"
   cn.Open
   rs.ActiveConnection = cn        
   rs.Properties("Server Data On Insert").Value = True
   rs.Open "SELECT * FROM Employee", , adOpenKeyset, adLockOptimistic
   rs.AddNew
   rs.Fields(1) = "Test"
   rs.Update
   ' Here should be the correct identity value inserted into Employee Table
instead of Audit Table        

   Debug.Print rs.Fields(0)
   rs.Close
   cn.Close

        For more information, please read the following article:
        Q219029 HOWTO: Retrieving Calculated Fields from SQL Server 7.0
        http://support.microsoft.com/support/kb/articles/q219/0/29.asp

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copy Right, 2001 Microsoft Corporation. All rights reserved.

Regards,
Kally Wang
Microsoft Support Engineer


Subject: Getting the identity value of a newly inserted row
Date: Tue, 16 Oct 2001 16:00:14 -0400
Newsgroups: microsoft.public.vb.database.ado

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

 
 
 

1. Getting IDENTITY value of newly inserted row

Hi,

I have a quick question.  How can I get the identity value of a newly
inserted row using the Recordset object.  Here is what I am trying right
now:

        Set toRS = New Recordset
        toRS.Open "PMSUsers", oDb.CN, adOpenForwardOnly, adLockOptimistic
        toRS.AddNew
        toRS("UsrLoginID") = strEncUsername
        toRS("UsrPassword") = strEncPassword
        toRS("CustomerID") = CustomerID
        toRS.Update
        AddUser = toRS("UsrID") 'Return New UserID to calling function

The UsrID column is defined as an IDENTITY column in SQL.  However, it's
always returning 0 for some reason, and according to SQL Server, new ids are
being correctly generated.  Any help would be appreciated.  Thanks.

Xin Li

2. Joining the team

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

4. blackbox.trc

5. Getting IDENTITY value of a row that was just INSERTED

6. PDOXWIN QUERY- HELP!

7. Retrival of newly created row auto generated identity column values

8. Oracle Financial Applications

9. Retrieving Identity value of newly added row

10. Identity cannot be determined for newly inserted rows

11. get back the identity of a newly inserted row thru ODBC

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

13. Identity cannot be determined for newly inserted rows