Mysql数据适配器InsertCommand

本文关键字：InsertCommand 适配器 数据 Mysql | 更新日期: 2023-09-27 18:24:43

我有以下代码

MySqlConnection myCon = new MySqlConnection(System.Configuration.ConfigurationManager.AppSettings["mySQLCon"]);
MySqlDataAdapter dAdap = new MySqlDataAdapter();
dAdap.InsertCommand = new MySqlCommand("insert into PMduedates(eCId,ECClass,ddate,PMType) values(eCId,ECClass,ddate,PMType)", myCon);
dAdap.InsertCommand.Parameters.Add("eCId", MySqlDbType.VarChar, 20, "eqpID");
dAdap.InsertCommand.Parameters.Add("ECClass", MySqlDbType.VarChar, 100, "class");
dAdap.InsertCommand.Parameters.Add("ddate", MySqlDbType.VarChar, 100, "MaintType");
dAdap.InsertCommand.Parameters.Add("PMType", MySqlDbType.Date, 10, "Duedate");
myCon.Open();
dAdap.Update(dtDue);
myCon.Close();

在上面的代码中，dtDue是一个数据表，eqpID、class、MaintType和Dudate是数据列。当我执行代码时，它不会给出任何错误，但数据不会在表PMduedates中更新。

Mysql数据适配器InsertCommand

为什么DataAdapter要在DataTable中插入记录？没有人告诉它这样做。只有当行在RowState.Added中时，它才会插入行，你可以这样强制它：

foreach (DataRow row in dtDue.Rows)
    row.SetAdded();

但由于您无论如何都是手动提供insert命令和参数，我也会手动执行查询：

using(var myCon = new MySqlConnection(System.Configuration.ConfigurationManager.AppSettings["mySQLCon"]))
using(var cmd = new MySqlCommand("insert into PMduedates(eCId,ECClass,ddate,PMType) values(eCId,ECClass,ddate,PMType)", myCon))
{
    cmd.InsertCommand.Parameters.Add("eCId", MySqlDbType.VarChar, 20, "eqpID");
    cmd.InsertCommand.Parameters.Add("ECClass", MySqlDbType.VarChar, 100, "class");
    cmd.InsertCommand.Parameters.Add("ddate", MySqlDbType.VarChar, 100, "MaintType");
    cmd.InsertCommand.Parameters.Add("PMType", MySqlDbType.Date, 10, "Duedate");
    myCon.Open();
    int countInserted = cmd.ExecuteNonQuery();
} // using closes the connection implicitely

我认为问题是您已经将参数和列命名为相同的名称。请更改参数名称，例如：

MySqlConnection myCon = new MySqlConnection(System.Configuration.ConfigurationManager.AppSettings["mySQLCon"]);
MySqlDataAdapter dAdap = new MySqlDataAdapter();
dAdap.InsertCommand = new MySqlCommand("insert into PMduedates(eqpID,class,Duedate,MaintType) values(@eCId,@ECClass,@ddate,@PMType)", myCon);
dAdap.InsertCommand.Parameters.Add("@eCId", MySqlDbType.VarChar, 20, "eqpID");
dAdap.InsertCommand.Parameters.Add("@ECClass", MySqlDbType.VarChar, 100, "class");
dAdap.InsertCommand.Parameters.Add("@ddate", MySqlDbType.VarChar, 100, "MaintType");
dAdap.InsertCommand.Parameters.Add("@PMType", MySqlDbType.Date, 10, "Duedate");
myCon.Open();
dAdap.Update(dtDue);
myCon.Close();
相关文章：