How To Insert Data Within Two Tables [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

how to insert data using multiple tables

What i Have Done is Create two Tables

1. Table1 - 开发者_开发问答RollNo,Fname,Mname,Lname
2. Table2 - Rollno,Marks

Now I have Set the Foreign Key as RollNo

Now the Point is :-

I have used the DetailsView in ASP.NET in Default mode as Insert

In the Data Source Config I Have Written the query for SELECT as the INNER JOIN of the Two Tables

This is Perfect.

The PROBLEM IS WITH THE INSERT QUERY.It does not allow me to Insert values into Two Tables

From the Above it is Clear that for my INSERT operation Fname,Mname,Lname is from Table1 and Marks is Table2.And Rollno is foreign Key.So How do I do This

INSERT INTO TWO TABLES.FROM a Single User Form

Keep In Mind Guys I Am using DetailsView in Asp.NET which allows me select only one SQLDataSource at a time..

---

Inserts are by design single table operations. If you want to insert into two related tables you need to insert into the primary table first, retrieve the key just created and insert into the second table with the key you just retrieved.

---

You can use a stored procedure and Transactions to perform your two insert statements. But you cannot perform an insert on two tables at the same time.

---

You cant insert in both tables at once first you have to insert the primary key stuff than come to the foreign key table..You can use @@IDENTITY which will give you the primary key value against your insertion then you can use it to insert in second table

Use This way

  Insert into Table1(FName, MName, LName)
  values(@val1 , @val2, @val3)

  Insert into Table2(RollNo, Marks)
   values(@@Identity, val4)