使用外键更新表中的数据 [英] update data in tables with foreign key

问题描述

我在通过c＃widows表更新表中的数据时遇到问题我有2个表，与外键链接我没有找到任何查询2表的解决方案



i使用此代码进行简单更新

但是我想用外键更新2个表中的数据

所以请给我发送代码

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

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

推荐答案

您可能希望将SQLCommand拆分为两个命令并在事务中运行它们。 （看看myconn.BeginTransaction（），myconn.Commit（）和myconn.Rollback（）。如果第二个查询失败，你将留下一半处理过的记录。



然后，因为你正在做INSERTS，你想运行selectcommand.RunNonQuery（）;



一旦你运行它，你会想要研究使用SQLParameters。它们可以保护你免受有人将错误数据输入你的文本框。



祝你好运

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

我为此举例说明请在插入时始终使用事务，在两个或多个表中更新数据....................

Dbconnection _db = new Dbconnection（ ）;

_db.Orgconnectionstring = _db.Orgconnectionstrig +; Data Source =+ dbpath +\\Database\\ CORPSOP_ORG.accdb;

OleDbConnection conn = new OleDbConnection（_db.Orgconnectionstring）;

conn.Open（）;

OleDbTransaction tc = conn.BeginTransaction（）;

尝试

{

int retval = 0;

OleDbCommand cmdcommand = new OleDbCommand（从SubDepartment中删除（DeptID =+ omdept.DeptID +），conn）;

cmdcommand.Transaction = tc;

cmdcommand.ExecuteNonQuery（） ;

cmdcommand = new OleDbCommand（从部门删除（DeptID =+ omdept.DeptID +），conn，tc）;

cmdcommand.ExecuteNonQuery（）;



tc.Commit（）;

return retval;

}

catch（例外情况）

{

tc.Rollback（）;

返回0;

}

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

很可能你违反了外键约束。



你的两个SQL语句a更清楚一点：



Master：

Most probably you violate the foreign key constraint.

Your two SQL statements a little bit more clear:

Master:

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

详细信息：

Detail:

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 + "')

详细说明插入语句（emp_account）中有哪个外键到主表？

至少this.textBox3.Text（I假设employee.idemployee是主要的）应该详细插入到fullfill foreignkey约束（当然有适当的字段）。



如果你给予它会有很多答案关于这两个表及其字段的简短概述。

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.

这篇关于使用外键更新表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！