datatable relations with column expression bug

[Edward Mostrom]

I have 2 tables that have a relation between them. The parent table
also has an expression in a column that sums the values in the child
table. Both tables are hooked up to datagrids.

Everything seems to work fine as far as adding and changing rows in the
child table. But if I delete a row in the child table that has a value
in it, the parent table continues to have a total that includes the
deleted child row... even after calling accpetChanges on the dataset.

I can scan through the rows in the child table and sure enough, there
are no deleted rows because acceptChanges removed it. The datagrid for
the child table doesn't show the row either... just the parent seems to
be keeping track of the phantom row.

If I change a value in another row in the child table and do another
acceptChanges, then finally the parent stops adding the deleted row to
its calculation.

When I am deleting the row, I am doing so from the datagrid by selecting
the row and hitting the delete key... haven't checked if I get the same
problem by deleting the row programatically.

Any ideas as to what is going on?

 

[Mohamoss]

Hi Edward
There must be some event that was fired only on the case of modification
and not when a row was deleted( from the grid UI) . would you mind posting
an appropriate snippet of your code

Mohamed Mahfouz
MEA Developer Support Center
ITworx on behalf of Microsoft EMEA GTSC

 

[Edward Mostrom]

Here it is.
If you change a value in the child page, it is reflected on the parent.
If you select a child row and hit the delete key, the parent still shows
the same value (still sees the deleted row). If you then change a value
on another row of the child, the parent is now calculating correctly and
no longer sees the deleted row.

Thanks for looking at it.



namespace test
{
using System;
using System.IO;
using System.Data;
using System.Windows.Forms;
using System.Drawing;


public class MyApp : System.Windows.Forms.Form
{

private System.Windows.Forms.TabControl tabControl;
private System.Windows.Forms.TabPage parentPage, childPage;
private System.Windows.Forms.DataGrid parentDataGrid,
childDataGrid;
private DataSet ds;
private Button acceptButton;

public static void Main()
{
Application.Run(new MyApp());
}


public MyApp()
{

//
// Data Sets
//
this.ds = new DataSet();
CreateDS(ds);

//
// Parent Page
//
this.parentPage = new TabPage("Parent");
this.parentPage.Size = new System.Drawing.Size(456,256);
this.parentDataGrid = new System.Windows.Forms.DataGrid();
parentDataGrid.BeginInit();
parentDataGrid.CaptionVisible = false;
parentDataGrid.Location = new System.Drawing.Point(2,2);
parentDataGrid.Size = new System.Drawing.Size(452,252);
parentDataGrid.TabIndex = 1;
parentDataGrid.Anchor = AnchorStyles.Top | AnchorStyles.Left |
AnchorStyles.Right | AnchorStyles.Bottom;
parentDataGrid.DataSource = ds.Tables["Parent"].DefaultView;
((DataView)parentDataGrid.DataSource).AllowNew = false;
((DataView)parentDataGrid.DataSource).AllowDelete = false;
parentDataGrid.AllowNavigation = false;
parentDataGrid.EndInit();
this.parentPage.Controls.Add(this.parentDataGrid);

//
// Child Page
//
this.childPage = new TabPage("Child");
this.childPage.Size = new System.Drawing.Size(456,256);
this.childDataGrid = new System.Windows.Forms.DataGrid();
childDataGrid.BeginInit();
childDataGrid.CaptionVisible = false;
childDataGrid.Location = new System.Drawing.Point(2,42);
childDataGrid.Size = new System.Drawing.Size(452,212);
childDataGrid.TabIndex = 1;
childDataGrid.Anchor = AnchorStyles.Top | AnchorStyles.Left |
AnchorStyles.Right | AnchorStyles.Bottom;
childDataGrid.DataSource = ds.Tables["Child"].DefaultView;
((DataView)childDataGrid.DataSource).AllowNew = false;
//((DataView)childDataGrid.DataSource).AllowDelete = false;
childDataGrid.AllowNavigation= false;
childDataGrid.EndInit();
this.childPage.Controls.Add(this.childDataGrid);

//
// Tab Control
//
this.tabControl = new System.Windows.Forms.TabControl();
this.tabControl.Location = new System.Drawing.Point(2,2);
this.tabControl.Name = "Tab";
this.tabControl.SelectedIndex = 0;
this.tabControl.Size = new System.Drawing.Size(460,260);
this.tabControl.TabIndex = 0;
this.tabControl.Anchor = AnchorStyles.Top| AnchorStyles.Left |
AnchorStyles.Bottom | AnchorStyles.Right;
tabControl.TabPages.Add( new TabPage("X") ); // fix for layout
problems on first page - temp page
tabControl.TabPages.Add(parentPage);
tabControl.TabPages.Add(childPage);

//
// Accept Button
//
acceptButton = new Button();
acceptButton.Location = new System.Drawing.Point(120,10);
acceptButton.Size = new System.Drawing.Size(100,24);
acceptButton.Text = "AcceptChanges";
acceptButton.Click += new EventHandler( this.Accept_Click );
this.childPage.Controls.Add(this.acceptButton);

//
// Form
//
this.AutoScaleBaseSize = new System.Drawing.Size(5,13);
this.ClientSize = new System.Drawing.Size(464,264);
this.Text = "test";
this.Controls.Add(this.tabControl);

}


private void Accept_Click(object sender, EventArgs e)
{
ds.AcceptChanges();
}


public static void CreateDS(DataSet ds)
{


DataTable parent = new DataTable("Parent");
parent.Columns.Add(new DataColumn("ID", typeof(string)));
parent.Columns.Add(new DataColumn("Assigned", typeof(decimal)));
parent.PrimaryKey = new DataColumn[] { parent.Columns["ID"] };
ds.Tables.Add(parent);

DataTable child = new DataTable("Child");
child.Columns.Add(new DataColumn("ID", typeof(string)));
child.Columns.Add(new DataColumn("Category", typeof(string)));
child.Columns.Add(new DataColumn("Amount", typeof(decimal)));
child.PrimaryKey = new DataColumn[] { child.Columns["ID"],
child.Columns["Category"]
};
ds.Tables.Add(child);


ds.Relations.Add("ParentChild",
new DataColumn[1]{parent.Columns["ID"]},
new DataColumn[1]{child.Columns["ID"]});


parent.Columns["Assigned"].Expression =
"ISNULL(Sum(Child(ParentChild).Amount), 0)";

DataRow r = parent.NewRow();
r["ID"] = "A";
parent.Rows.Add(r);
for( Decimal d = 1; d< 10; d++ )
{
r = child.NewRow();
r["ID"] = "A";
r["Category"] = "B-" + d.ToString();
r["Amount"] = d;
child.Rows.Add(r);
}
ds.AcceptChanges();

}
}

}

 

[Edward Mostrom]

I have found a way to work around this. Before accepting changes, clear
out the expression on the parent table. Accept changes. Put the
expression back on the parent table. Accept changes. Kind of kludgy;
but it works around the bug.


Also, another bug related to adding rows with a parent expression.
Using the same code as before, comment out the line in the child
datagrid:
((DataView)childDataGrid.DataSource).AllowNew = false;

this allows you to add new rows now to the child. If you add a new row,
the parent calculates correctly. But if you try to change a value on
any of the other rows on the child table, and error is thrown about no
Original data to access. I can add and change as many new rows as I
want; but I can't change an existing row. Once I acceptChanges() to the
dataset, everything is working fine again.

Ed