I need to update Two Tables in SQL 2000, row at a time, using two different datasets. The following is my requirement:
1. Insert a row in Table1 using a Datset1 - Already Dataset got new records added.
2. Get the identity Column from the table1.
3. Insert row in Table2 using DataSet2 (already with new records). Primary Key of Table1 is Foreign Key in Table2.
4. Above insert process should be in the same transaction ( i mean same commitment cycle). If the insert failed in Table2, rollback should occur in both tables.
Also I defined the relationship between dataset1 and dataset2. Please suggest me the solution.