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

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();
}

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

1 solution

Solution 1

Add your solution here

Preview 0

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

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us