Click here to Skip to main content
15,936,897 members
Please Sign up or sign in to vote.
1.80/5 (2 votes)
i have a problem while updating data in tables through c# widows form i have 2 tables,linked with foreign key i didn't find any solution for query for 2 tables

i use this code for simple update
but i want to update data in 2 tables with foreign key
so plz send me that code

C#
string myconnection = "datasource=127.0.0.1;port=3306;username=root;password=7621361";
MySqlConnection myconn = new MySqlConnection(myconnection);

MySqlCommand selectcommand = new MySqlCommand("insert into mydb.employee (idemployee,name,type) values ('"+this.textBox3.Text+"','"+this.textBox4.Text+"','"+this.textBox5.Text+"') ; " + "insert into mydb.emp_account(idemp_account,loan,bill,balance,total) values ( '" + this.textBox6.Text + "','" + this.textBox7.Text + "','" + this.textBox8.Text + "','" + this.textBox9.Text + "','" + this.textBox10.Text + "');", myconn);
MySqlDataReader myreaderr;


                try
                {
                    myconn.Open();
                    myreaderr = selectcommand.ExecuteReader();
                    MessageBox.Show("saved");
                    while (myreaderr.Read())
                    {
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
Posted
Updated 19-Dec-13 2:45am
v2
Comments
Murugan Kolanji 19-Dec-13 14:34pm    
What was the error?
creepz03 19-Dec-13 18:58pm    
You want a much better approach? ..then create a stored procedure.
[no name] 19-Dec-13 20:03pm    
SP do not help if one violates constraints.

You may want to split your SQLCommand into two commands and run them in a transaction. (Look at myconn.BeginTransaction(), myconn.Commit(), and myconn.Rollback(). Here if the second query fails you will be left with a half processed record.

Then, because you are doing INSERTS, you want to run selectcommand.RunNonQuery();

Once you get it running, you will want to look into using SQLParameters. They will protect you from someone entering bad data into your textboxes.

Good Luck
 
Share this answer
 
v3
i m giving example for this please use transaction always when you are insert,update data in two or more tables....................
Dbconnection _db = new Dbconnection();
_db.Orgconnectionstring = _db.Orgconnectionstrig + ";Data Source= " + dbpath + "\\Database\\BIZSOP_ORG.accdb";
OleDbConnection conn = new OleDbConnection(_db.Orgconnectionstring);
conn.Open();
OleDbTransaction tc = conn.BeginTransaction();
try
{
int retval = 0;
OleDbCommand cmdcommand = new OleDbCommand("Delete From SubDepartment where(DeptID=" + omdept.DeptID + ")", conn);
cmdcommand.Transaction = tc;
cmdcommand.ExecuteNonQuery();
cmdcommand = new OleDbCommand("Delete From Department where(DeptID=" + omdept.DeptID + ")",conn,tc);
cmdcommand.ExecuteNonQuery();

tc.Commit();
return retval;
}
catch (Exception ex)
{
tc.Rollback();
return 0;
}
 
Share this answer
 
Most probably you violate the foreign key constraint.

Your two SQL statements a little bit more clear:

Master:
SQL
insert into mydb.employee (idemployee,name,type) 
values ('"+this.textBox3.Text+"','"+this.textBox4.Text+"','"+this.textBox5.Text+"')


Detail:
SQL
insert into mydb.emp_account(idemp_account,loan,bill,balance,total) 
values ( '" + this.textBox6.Text + "','" + this.textBox7.Text + "','" + this.textBox8.Text + "','" + this.textBox9.Text + "','" + this.textBox10.Text + "')


Where in the detail insert statement (emp_account) you have a foreign key to the master table?
At least this.textBox3.Text (I assume employee.idemployee is primary) should apear in detail insert to fullfill foreignkey constraint (with appropriate field of course).

It would help a lot to answer if you give a short overview about this two tables and there fields.
 
Share this answer
 
v5
Comments
abdulrehman7621 5-Jan-14 5:16am    
yes these two table are linked with foreign key
primary keys: idemployee and idemp_account
foreign key is idemployee in emp_account table
but i don't know how to use it in insert statement

data of employees are already inserted, now i want to insert data in emp_account by using foreign key of idemployee

i hope, you help me in this
i'm not expert in this. this is my beginning in c#
thanxs
[no name] 5-Jan-14 8:17am    
First of all happy new year ;) And thank you for accepting my answer even it is not yet the final solution for you.

This is more database structure related then c#. In case employes are inserted you have to make sure that emp_account has the right foreign key. To explain it more in detail please post the two tables end its fields. It makes it easier to explain.
abdulrehman7621 8-Jan-14 5:06am    
thanxs bro :)
happy new year, welcome
mydb.employee (idemployee,name,type)
mydb.emp_account(idemp_account,idemployee,loan,bill,balance,total)

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