Inserting Primary Key of Emp table into Dept is a foreign key

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

I had a situation where I want to put Primary key of EMP into DEPT Tables are
EMP emp.id emp.name emp.date
DEPT dept.id dept.name emp.id

C#

using (SqlConnection conn = new SqlConnection(conStr))
           {

               SqlCommand cmd = new SqlCommand();
           cmd.CommandText = "INSERT INTO EMP(empname,empdate) VALUES(@Name, @Date);"
           + "INSERT INTO DEP(deptname, empid) VALUES(@deptname, @empid);"
               cmd.CommandType = CommandType.Text;
               cmd.Connection = conn;
               cmd.Parameters.AddWithValue("@Name", txtb1.Text);
               cmd.Parameters.AddWithValue("@Date", txtb2.Text);
               cmd.Parameters.AddWithValue("@deptname", txtb3.Text);
               cmd.Parameters.AddWithValue("@empid", "select empid from emp where empid = new inserted record");

"select empid from emp where empid = new inserted record" this means that i want to select id which i am going to insert now... as that posible or any other way to do so...

Posted 6-Aug-15 1:49am

Altaful Haq

Updated 6-Aug-15 2:11am

John C Rayan

v2

Add a Solution

Comments

ZurdoDev 6-Aug-15 8:01am

I don't understand what you are doing. Based on your title, it's likely very easy to do but your text is very unclear.

Altaful Haq 7-Aug-15 1:33am

humm yah. but i want to insert a new generated empid of emp table into a Dept table dept.empid on same save button. because i put insertion textboxs of these two table on same form. as i generate the new record an emp table it also generate a new record in dept table with a foreign key empid

Altaful Haq 7-Aug-15 1:36am

its just an example actuly i have two table USER and USER_EMRG_INFO which have one to one relation. and i want to generate both record from the same table.

John C Rayan 7-Aug-15 3:51am

Are you happy with the solution given ?

Altaful Haq 7-Aug-15 6:23am

yes

John C Rayan 6-Aug-15 8:12am

Your relationship is wrong. DEP->EMP is 1 to many relationship which means DEPT_ID should be in EMP table.

And you have to use SCOPE_IDENTITY() to get identity of the last record inserted.

Altaful Haq 7-Aug-15 1:36am

its just an example actuly i have two table USER and USER_EMRG_INFO which have one to one relation. and i want to generate both record from the same table.

Thanks7872 6-Aug-15 8:14am

Try to search for "Triggers in SQL Server". You can make use of 'After insert Trigger'.

Joan Magnet 6-Aug-15 8:23am

Use a stored procedure instead of passing the sql text command, it's easy to mantain, and some commands are not supported.

1 solution

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)

Richard Deeming · Accepted Answer · 2015-08-06T07:58:00

As mentioned in the comments, a stored procedure would be a better approach. However, you can still do it with inline SQL:

C#

const string query = @"DECLARE @EmpID int;

INSERT INTO EMP (empname, empdate) VALUES (@Name, @Date);
SET @EmpID = Scope_Identity();

INSERT INTO DEP (deptname, empid) VALUES (@deptname, @EmpID);";

using (SqlConnection conn = new SqlConnection(conStr))
using (SqlCommand cmd = new SqlCommand(query, conn))
{
    cmd.Parameters.AddWithValue("@Name", txtb1.Text);
    cmd.Parameters.AddWithValue("@Date", txtb2.Text);
    cmd.Parameters.AddWithValue("@deptname", txtb3.Text);
    
    conn.Open();
    cmd.ExecuteNonQuery();
}