Database Problem how to insert data from table

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi,

B
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.
Thanks

Posted 15-Apr-11 21:55pm

Balwant.mnd

Add a Solution

2 solutions

Solution 1

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.

Posted 15-Apr-11 22:02pm

Abhinav S

Comments

Balwant.mnd 16-Apr-11 4:13am

Please explain with example.
Thanks

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 hhttp://msdn.microsoft.com/en-us/library/ks9f57t0%28v=vs.80%29.aspx - use @@Identity to get the primary key column.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Pratik Bhesaniya · Accepted Answer · 2011-04-16T00:41:00

C#

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,

C#

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.