C# · 12月 27, 2021

c# – 从DataGridView自动更新数据库中的值

我正在使用 MySql与C#结合使用项目.
DataGridView的数据由DB中多个表的连接提供.要显示数据我使用以下,工作,代码: adapter.SelectCommand = new MysqLCommand( ” SELECT” + ” l.lot AS Lot,”+ ” m.comment AS Bemerkungen,” + … (multiple columns from different tables) … ” FROM m ” + ” JOIN m2p ON m.m2p_id = m2p.id” + … (more joins) …,this._MysqLConnection);dataGridView1.DataSource = data;adapter.Fill(data);

现在,GUI的用户可以修改某个列(“注释”列).所以我分配了一个eventHandler到CellEndEdit事件,当用户修改了允许的列的adapter.Update(data)被调用.现在这不执行正确的操作.

要定义我的update命令,我使用以下代码:

adapter.UpdateCommand = new MysqLCommand( ” UPDATE m” + ” JOIN l ON m.l_id = l.id” + ” SET m.comment = @comment” + ” WHERE l.lot = @lot”,this._MysqLConnection);adapter.UpdateCommand.Parameters.Add(“@comment”,MysqLDbType.Text,256,”Bemerkungen”);adapter.UpdateCommand.Parameters.Add(“@lot”,”Lot”);

你能解释一下我如何修复我的代码来自动更新数据库?

编辑:
添加进一步的源代码:

private MysqLDataAdapter warenlagerMysqLDataAdapter,kundenMysqLDataAdapter;private DataTable warenlagerData,kundenData;private DataGridView warenlagerGridView;private void updateWarenlagerView(object sender,EventArgs e) { warenlagerMysqLDataAdapter.Update(warenlagerData);}private void initialzeFields() { warenlagerGridView.CellEndEdit += new DataGridViewCellEventHandler(this.updateWarenlagerView); warenlagerMysqLDataAdapter = new MysqLDataAdapter(); warenlagerData = new DataTable(); }private void initializeWarenlagerView() { warenlagerMysqLDataAdapter.SelectCommand = new MysqLCommand( ” SELECT” + ” c.name AS Ursprung,” + ” m2p.art_nr AS ArtNr,” + ” m.delivery_date AS Eingangsdatum,” + ” CONCAT(FORMAT(m.delivery_amount / 100,2),’kg’) AS Eingangsmenge,” + ” l.lot AS Lot,” + ” m.quality AS Qualität,” + ” m.comment AS Bemerkungen,” + ” CONCAT(m.units,’kg’) AS Units,” + ” CONCAT(FORMAT(s.amount / 100,’kg’) AS Lagermenge,” + ” FORMAT(m.base_price / 100,2) AS Einkaufspreis,” + ” FORMAT(s.amount/10000 * m.base_price,2) AS Wert” + ” FROM mushrooms AS m ” + ” JOIN mushroom2path AS m2p ON m.mushroom2path_id = m2p.id” + ” JOIN countries AS c ON m.origin_id = c.id” + ” JOIN lots AS l ON m.lot_id = l.id” + ” JOIN stock AS s ON s.mushrooms_id = m.id”,this._MysqLConnection); warenlagerGridView.DataSource = warenlagerData; warenlagerMysqLDataAdapter.Fill(warenlagerData); warenlagerMysqLDataAdapter.UpdateCommand = new MysqLCommand( ” UPDATE mushrooms AS m” + ” JOIN lots AS l ON m.lot_id = l.id” + ” SET m.comment = @comment” + ” WHERE l.lot = @lot”,this._MysqLConnection); warenlagerMysqLDataAdapter.UpdateCommand.Parameters.Add(“@comment”,”Bemerkungen”); warenlagerMysqLDataAdapter.UpdateCommand.Parameters.Add(“@lot”,”Lot”); }

这是关于这个问题的整个代码.我100%确定adapter.Update(data)方法被调用(调试).而传递给适配器的数据.Update()方法包含新的数据.

解决方法 请尝试这个更新查询它的工作原理. UPDATE mushrooms SET comment = @commentWHERE l_id=(select id from l where lot=@lot)