Click here to Skip to main content
15,901,368 members
Please Sign up or sign in to vote.
0.00/5 (No votes)

I have two tables, data is inserted into both table simultaneously on button click. Both table Having identities and one table's foreign key is another table primary key. So problem is that how to insert the value for foreign key table because it has not yet created.
So please suggest me the right direction.

objCommand = new SqlCommand("INSERT INTO Table1 (field1,field2,field3) VALUES (value1,value2,value3); SELECT SCOPE_IDENTITY()", objConnection);

Here "SELECT SCOPE_IDENTITY()" will give you Newly inserted record ID.

Now you have take this value into some variable and again fire a query by passing this variable and that table respected fields like,

string prID=objCommand.ExecuteScalar().ToString();

new SqlCommand("INSERT INTO Table2 (primaryID,field1,field2,field3) VALUES (prID,value1,value2,value3); SELECT SCOPE_IDENTITY()", objConnection);

I Hope this will help you.
Share this answer
Sequence the calls to your query. You could always call the insert on the primary key query and then call the insert foreign key query.
Share this answer
Balwant.mnd 16-Apr-11 4:13am    
Please explain with example.
Abhinav S 16-Apr-11 5:38am    
I dont have any examples with me at the moment.
Searching for @@Identity may give you some examples on the internet, however.
Balwant.mnd 16-Apr-11 4:17am    
Because that value is auto generated and how i insert it to another when it is generated.
Abhinav S 16-Apr-11 5:37am    
You can get the last identity column from the first table. Then insert it into the second table.
See h - use @@Identity to get the primary key column.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900