Problems with Identity Columns and SQLCommandBuilder

[geethalakshmi.a]

I have a problem when dealing with identity columns and I request you to provide me a solution. I have described the problem below.
Problem Description:

• I am currently developing an application which includes reading data from an excel file and updating around 10 tables in a SQL Sever 2000 database. Out of these 10 tables, 1 table acts as a parent table and the other 9 Tables are child to it.

• The parent table's primary key is set as an identity.

• Considering the number of tables/rows that are updated, I decided to use SQLDataadapter and SQLCommandbuilder for creation of DML statements and data updation.

• Now, how do I get the indentity value that was generated from sqldataadapter when I don't have control over the insert command that is generated by CommandBuilder.

• I need that identity value to specify the foreign key and execute the insert statements on child tables.

Could anyone please provide me a solution for this problem?

 

[TechGnome]

Best bet (and this is my personal opin).... dump the builders.... use Stored Procs to insert the data. Have the SP that inserts the parent record return the new ID field (which can be done by RETURN @@IDENTITY)... then use that to pass to the other SP(s) when inserting the child records.

-tg