CODEWITHSUNDEEP
c#
Jomarlyn Tamargo
Jomarlyn Tamargo

Reputation: 17

Insert Data in two table with Primary key

this my BottomSave i have two table Employee and Contract with primary key of Enumber.

from adding data in to table employee is working

but in adding data into table contract but is not adding

because of my primary key. is there something wrong in my inserting to contract?

                MySQL connect = new MySQL();
                connect.Connection_Today();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = MySQL.con;

                // Adding data to table Employee from database

                cmd.CommandText = "Insert into Employee(Enumber,Inumber,Pnumber,Fname,Mname,Sname,Age,Bdate,Gender,Mstatus,Nationality,Eaddress,Cnumber,Picture) Values (@Enumber,@Inumber,@Pnumber,@Fname,@Mname,@Sname,@Age,@Bdate,@Gender,@Mstatus,@Nationality,@Eaddress,@Cnumber,@Picture)";
                cmd.Parameters.AddWithValue("@Enumber", txtEnumber.Text);
                cmd.Parameters.AddWithValue("@Inumber", txtInumber.Text);
                cmd.Parameters.AddWithValue("@Pnumber", txtPassport.Text);
                cmd.Parameters.AddWithValue("@Fname", txtPassport.Text);
                cmd.Parameters.AddWithValue("@Mname", txtMname.Text);
                cmd.Parameters.AddWithValue("@Sname", txtSname.Text);
                cmd.Parameters.AddWithValue("@Age", txtAge.Text);
                cmd.Parameters.AddWithValue("@Bdate", DtpBdate.Text);
                cmd.Parameters.AddWithValue("@Gender", cbGender.Text);
                cmd.Parameters.AddWithValue("@Mstatus", cbMstatus.Text);
                cmd.Parameters.AddWithValue("@Nationality", txtNationality.Text);
                cmd.Parameters.AddWithValue("@Eaddress", txtEAddress.Text);
                cmd.Parameters.AddWithValue("@Cnumber", txtCnumber.Text);
                cmd.Parameters.AddWithValue("@Picture", ImageToBase64(PbImage.Image,System.Drawing.Imaging.ImageFormat.Jpeg));
                cmd.ExecuteNonQuery();



                // Adding data to table Contract from database
                cmd.CommandText = "Insert into Contract(Enumber,Position,Salary,CDuration,Scontract,Econtract) Values(@Enumber,@Position,@Salary,@CDuration,@Scontract,@Econtract)";
                cmd.Parameters.AddWithValue("@Enumber", txtEnumber.Text);
                cmd.Parameters.AddWithValue("@Position", txtPosition.Text);
                cmd.Parameters.AddWithValue("@Salary", txtSalary.Text);
                cmd.Parameters.AddWithValue("@CDuration", txtCDuration.Text);
                cmd.Parameters.AddWithValue("@Scontract", dtpStart.Text);
                cmd.Parameters.AddWithValue("@Econtract", dtpEnd.Text);
                cmd.ExecuteNonQuery();
                connect.Disconnect();

Upvotes: 0

Views: 86

Answers (1)

Soner Gönül
Soner Gönül

Reputation: 98740

You need to clear your parameter with SqlParameterCollection.Clear method before putting them in your second query because your command already have those parameter names which is added in the first command.

cmd.CommandText = "Insert into Contract(Enumber,Position,Salary,CDuration,Scontract,Econtract) Values(@Enumber,@Position,@Salary,@CDuration,@Scontract,@Econtract)";
cmd.Parameters.Clear();
...

Also use using statement to dispose your connection and command automatically instead of calling Close or Dispose methods manually.

using(var con = new SqlConnection(conString))
using(var cmd = con.CreateCommand())
{
    // Set your CommandText property of your command with parameters.
    // Add your parameters values.
    // Open your connection
    // Execute your query
    // Set your second command to your CommandText property
    // Clear your parameters.
    // Add new parameter values.
    // Execute your second query.
}

By the way, don't use AddWithValue as much as you can. It may generate unexpected and surprising results sometimes. Use Add method overload to specify your parameter type and it's size.

Upvotes: 2

Related Questions