使用ForeignKeyConstraint时，对子表的更新失败 [英] Update to child table fails when using ForeignKeyConstraint

问题描述

嗨。我有两个datagridviews（主要细节）。当我双击父行时，我打开一个新的表单，其中子数据网格视图与数据集的关系有限。我正在使用ForeignKeyConstraint删除父行并自动删除子行。一切都好。但是，如果我添加一个新的父行并双击它我可以添加新的子行，但是在更新数据集时，我没有得到任何错误，但子表没有获得新的行。如果我不使用ForeignKeyConstraint我可以添加新的子行没有问题，但如果我删除父行，更新数据集时我得到一个错误，因为在数据集中仍然是子行。调试应用程序"添加"新子行的状态。但永远不会更新数据库。

将数据读入数据集的代码：

暗淡colsPadres（1）由于DataColumn的结果，昏暗colsHijas（1）由于DataColumn的结果，colsPadres（0）= obtenerDetalleArticulos.Tables（QUOT; TARTICULOS_CLIENTES"）。列（" idArticulo"）
colsPadres（1）= obtenerDetalleArticulos.Tables（" TARTICULOS_CLIENTES"）。列（" idCliente"）
colsHijas（0）= obtenerDetalleArticulos.Tables（" tarticulos_precios_cliente"）。列（QUOT; idArticulo"），点击colsHijas（1）= obtenerDetalleArticulos.Tables（QUOT; tarticulos_precios_cliente"）色谱柱（QUOT; idCliente"）。结果，"relacion烯卡斯卡达人borrar洛斯precios对CADA articulo.cliente <无线电通信>昏暗idKeyRestraint作为ForeignKeyConstraint =新ForeignKeyConstraint（colsPadres，colsHijas），点击idKeyRestraint.DeleteRule = Rule.Cascade点击'idKeyRestraint.UpdateRule = Rule.Cascade结果idKeyRestraint.AcceptRejectRule = AcceptRejectRule.Cascade结果，obtenerDetalleArticulos.Tables（QUOT; tarticulos_precios_cliente"）Constraints.Add（idKeyRestraint），点击obtenerDetalleArticulos.Relations.Add（QUOT; ClientesPrecios" ;, colsPadres，colsHijas）。[/ CODE]结果

更新数据集的代码：

' '''检查行状态，请执行BEFORE UPDATE父表结果，对于每个博士于dsArticulos.Tables（QUOT; tarticulos_precios_cliente"）。行结果，辅助= dr.RowState '' STATUS = ADDED点击下一步

MyBase.SQL =" select * from TARTICULOS_CLIENTES ta where idArticulo =" &安培; idArticulo结果，命令=无结果，MyBase.InitializeCommand（），点击"使用CommandBuilder的在下面的呼叫，点击InitializeDataAdapter（真），点击dataAdapter.Update（dsArticulos，" TARTICULOS_CLIENTES"）

'' '' 检查行状态，请执行AFTER UPDATE父表结果，对于每个博士于dsArticulos.Tables（QUOT; tarticulos_precios_cliente"）行结果，辅助= dr.RowState。'STATUS =不变。为什么点击下一步

"更新子表，点击MyBase.SQL = QUOT; SELECT * FROM tarticulos_precios_cliente TA其中idArticulo = QUOT; &安培; idArticulo结果，命令=无结果，MyBase.InitializeCommand（），点击"使用CommandBuilder的在下面的呼叫，点击InitializeDataAdapter（真），点击dataAdapter.Update（dsArticulos，" tarticulos_precios_cliente"）

任何想法？

谢谢您

解决方案

这里的我不能肯定地说，这是你的问题，但我怀疑你将DataRelation上的AcceptRejectRule设置为Cascade。如果它设置为Cascade，则在父行上调用AcceptChanges（）或RejectChanges（）也会在子行上调用它。

如果您将DataSet传递给一堆表适配器，这是非常糟糕的，因为表适配器将在它发送到数据库的每一行上调用AcceptChanges（）。作为父行得到被推到数据库中，所有的子行的RowState被设置为DataRowState.Unchanged，然后当传递给它的表适配器子表的，没有得到更新。

好消息是，如果这确实是您的问题，只需更改DataRelation的属性即可轻松解决。

 

Hi. I have two datagridviews (master detail). When i double click on a parent row i open a new form with a child datagridview bounded to a relation od the dataset.  I'm using ForeignKeyConstraint to delete a parent row and delete automatically child rows. Everything is ok. But if i add a new parent row and double click it i'm able to add new child rows but when updating the dataset i get no error but the child table doesn't get the new rows. If i don't use ForeignKeyConstraint i can add new child rows with no problem but if i delete a parent row, when updating the dataset i get an error because in the dataset are still the child rows. Debugging the application the status of the new child row is "added" but never updates the DB.

 

code to read data into the dataset:

 

Dim colsPadres(1) As DataColumn
Dim colsHijas(1) As DataColumn
colsPadres(0) = obtenerDetalleArticulos.Tables("TARTICULOS_CLIENTES").Columns("idArticulo")
colsPadres(1) = obtenerDetalleArticulos.Tables("TARTICULOS_CLIENTES").Columns("idCliente")
colsHijas(0) = obtenerDetalleArticulos.Tables("tarticulos_precios_cliente").Columns("idArticulo")
colsHijas(1) = obtenerDetalleArticulos.Tables("tarticulos_precios_cliente").Columns("idCliente")
'relacion en cascada al borrar de los precios para cada articulo.cliente
Dim idKeyRestraint As ForeignKeyConstraint = New ForeignKeyConstraint(colsPadres, colsHijas)
idKeyRestraint.DeleteRule = Rule.Cascade
'idKeyRestraint.UpdateRule = Rule.Cascade
idKeyRestraint.AcceptRejectRule = AcceptRejectRule.Cascade
obtenerDetalleArticulos.Tables("tarticulos_precios_cliente").Constraints.Add(idKeyRestraint)
obtenerDetalleArticulos.Relations.Add("ClientesPrecios", colsPadres, colsHijas)[/CODE]

 

code to update dataset:

 

''''CHECK ROW STAUTS BEFORE UPDATE PARENT TABLE
For Each dr In dsArticulos.Tables("tarticulos_precios_cliente").Rows
   aux = dr.RowState  ''STATUS=ADDED
Next

MyBase.SQL = "select * from TARTICULOS_CLIENTES ta where idArticulo=" & idArticulo
command = Nothing
MyBase.InitializeCommand()
'use commandbuilder in the following call
InitializeDataAdapter(True)
dataAdapter.Update(dsArticulos, "TARTICULOS_CLIENTES")

''''CHECK ROW STAUTS AFTER UPDATE PARENT TABLE
For Each dr In dsArticulos.Tables("tarticulos_precios_cliente").Rows
   aux = dr.RowState  ''STATUS=UNCHANGED . WHY?
Next

'update child table
MyBase.SQL = "select * from tarticulos_precios_cliente ta where idArticulo=" & idArticulo
command = Nothing
MyBase.InitializeCommand()
'use commandbuilder in the following call
InitializeDataAdapter(True)
dataAdapter.Update(dsArticulos, "tarticulos_precios_cliente")

 

Any ideas?

 

Thank you

解决方案

I can't say for sure that this is your problem, but I would suspect that you have the AcceptRejectRule on the DataRelation set to Cascade.  If it's set to Cascade, calling AcceptChanges() or RejectChanges() on a parent row also calls it on the child rows. 

 

This is very bad if you're passing your DataSet to a bunch of table adapters, since a table adapter will call AcceptChanges() on every row it sends to the database.  As the parent rows get pushed out to the database, all the child rows' RowState gets set to DataRowState.Unchanged, and then when the child table's passed to its table adapter, nothing gets updated.

 

The good news is that if this is really your problem, it's easy to fix just by changing the DataRelation's properties.

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