Click here to Skip to main content
15,443,631 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 6-Aug-15 2:11am
v2
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     CRLF
yes

1 solution

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

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