C# insert into details table with master details id

Question

I am really new to C# and I have searched all over but I didn't find answer to problem.

in my application I have a table for example (employee details) second table is leaves.

what I want is to link employee details tables primary key to leaves table.

employee can have many leaves

So when I insert leaves record for employee it should store with employee details table id and when I search in leaves form with emid it shows all his leaves in datagridview.

employee details table

emid
empno
emname
emjoindate
emmobile
emaddress

leaves table

leaveid
emid
leavestartdate
leaveenddate

private void Save()
        {
                con = new SqlConnection(cs.DBConn);
                con.Open();
                string cb = "insert into leaves(leavestartdate,leaveenddate,notes) VALUES (@d1,@d2,@d3)";


                cmd = new SqlCommand(cb);

                cmd.Connection = con;

                cmd.Parameters.Add(new SqlParameter("@d1", SqlDbType.Date, 31, "leavestartdate"));
                cmd.Parameters.Add(new SqlParameter("@d2", SqlDbType.Date, 31, "leaveenddate"));
                cmd.Parameters.Add(new SqlParameter("@d3", SqlDbType.NVarChar, 500, "notes"));

                cmd.Parameters["@d1"].Value = txtstartdate.Text.Trim();
                cmd.Parameters["@d2"].Value = txtenddate.Text.Trim();
                cmd.Parameters["@d3"].Value = txtnotes.Text.Trim();
                con.Close();


            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }

Possible duplicate of [How do I create a foreign key in SQL Server?](http://stackoverflow.com/questions/48772/how-do-i-create-a-foreign-key-in-sql-server) — Akshay, Feb 02 '17 at 07:29
You need to use employee record id when you inserting new leave record. So in your code you need to add one more parameter for `emid` field and set it to emplayee.emid value — Renatas M., Feb 02 '17 at 08:06
can you please edit my code to make it better understandable for me — Bukhalifa, Feb 02 '17 at 08:07

score 1 · Answer 1 · answered Feb 02 '17 at 07:42

1

have you ever tried using select query. Because from select query also we can do this.

answered Feb 02 '17 at 07:42

s.k.Soni

1,139
1
19
37

i am unable to understand how to use two tables for insert data – Bukhalifa Feb 02 '17 at 07:59

kgzdev · Accepted Answer · 2017-02-05T08:23:48.997

1

You need to add emid parameter to insert query.

string cb = "insert into leaves(emid,leavestartdate,leaveenddate,notes) VALUES (@emid,@d1,@d2,@d3)";

and then set value of parameter @emid

then you can select leaves of any emid like this

string cb = "select * from leaves where emid = @emid";

EDIT: To get emname and emmobile from employee details table you can use this query

SELECT L.*, ED.emname , EM.emmobile 
FROM leaves L INNER JOIN
employee_details ED ON L.emid = ED.emid
WHERE L.emid = @emid

edited Feb 05 '17 at 08:23

answered Feb 02 '17 at 08:30

kgzdev

2,770
2
18
35

how do i get employee leaves record in datagridview on search – Bukhalifa Feb 05 '17 at 05:35
is it possible if i search emid from leaves it shows employee name and mobile number from employee details table in to textbox? – Bukhalifa Feb 05 '17 at 07:24
After inserting emid to both tables, you will have key to connect records. I will edit my post, and that way you can get detailed data – kgzdev Feb 05 '17 at 08:20

C# insert into details table with master details id

2 Answers2