Hi

All
I have problem in my project so i require a solution from you people as i also arrived for a solution and problem is as follows
1. i have 2 tables table1 and table2
2.table1's id is the foreign key of table 2
3. But from the insertion point of view the table2 will go first and then table1
4. so my solution for this is
Take the maximum id generated for the table1 and then insert insert first the max(id) to the table1 and then insert the value to the table2 ....

But the problem is I have given the id as autogenerated so i cannot insert the max value into that and another is that i came with the solution which i.e., disable the Autogenerate option and Autocommit option when user enters the data in the table 2 then i get max(id) from table1 and increment it by one and insert it into the table1 and then table 2 data enters ..........Now this is done when the auto commit option is off .......

So when commit is made after inserting the both table2 and table1 date then the whole thing will insert is this correct or Please can you give me solution for this ?

Thank you for reading this with patience



Thanks
Avinash

Hi,

A rule of thumb is that if table1’s id column is the foreign key of table2, you should definitely insert a new record into table1 first and then insert another record to the table2 with respect to the newly inserted record in table1.

Your own solution to insert a new record to both tables with the id column’s AutoGenerate property is set to false is correct.

If the id column’s AutoGenerate property is set to true, then you can insert a new record in the following way.

· Insert a new record into Table1. The insert command doesn’t need to specify the value of the ID column since it is auto generated by the SQL Server.

· Call the SCOPE_IDENTITY stored procedure to retrieve the ID value of the latest inserted record.

· Once you get the ID value, use it to compose another SQL insert command which insert a record into the Table2.

Here’s an article about Retrieving Identity or Autonumber Values (ADO.NET)

Regards,

Jacob

Hi,

A rule of thumb is that if table1’s id column is the foreign key of table2, you should definitely insert a new record into table1 first and then insert another record to the table2 with respect to the newly inserted record in table1.

Your own solution to insert a new record to both tables with the id column’s AutoGenerate property is set to false is correct.

If the id column’s AutoGenerate property is set to true, then you can insert a new record in the following way.

· Insert a new record into Table1. The insert command doesn’t need to specify the value of the ID column since it is auto generated by the SQL Server.

· Call the SCOPE_IDENTITY stored procedure to retrieve the ID value of the latest inserted record.

· Once you get the ID value, use it to compose another SQL insert command which insert a record into the Table2.

Here’s an article about Retrieving Identity or Autonumber Values (ADO.NET)

Regards,

Jacob

Hi
Jacob thanks for reply and the last link provided you is invalid please can you send me the link again

Here's the link: http://msdn2.microsoft.com/en-us/library/ks9f57t0.aspx

Or you can go to www.msdn.com and type "Identity and AutoNumber Column" in the upper-left search box to ge the same link.

Regards,

Jacob