Re: [ADVANCED-DOTNET] ADO.NET Parent/Child Relation Referencing

[Jon Stonecash]

I do not know if this is what you want or that it will work.  Why not put an
expression in the List table columns to sum the items in the list.  Then
modify your sum of list items to sum the sum.

Jon Stonecash

From: Doug finke <[EMAIL PROTECTED]>
Reply-To: "Moderated discussion of advanced .NET topics."
<[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Subject: [ADVANCED-DOTNET] ADO.NET Parent/Child Relation Referencing
Date: Thu, 19 Dec 2002 05:58:20 -0800

How deep can you reference with Parent/Child Relation Referencing?

I have three tables to relate, the first table is clients, the second
table is how many lists each has and the third table holds the items of
each list.

In the client table I create a column and set it's expression to count how
many lists that client has, no problem. An error occurs when I want to sum
the items Amount in that list.



System.Data.SyntaxErrorException: Syntax error in aggregate argument:
Expecting a single column argument with possible 'Child' qualifier.
   at System.Data.ExpressionParser.ParseAggregateArgument(FunctionId
aggregate)
   at System.Data.ExpressionParser.Parse()
   at System.Data.DataExpression..ctor(String expression, DataTable
table,
Type type)
   at System.Data.DataColumn.set_Expression(String value)
   at testRelationsError.Form1.Build() in
c:\temp\testrelationserror\form1.cs:line 129
   at testRelationsError.Form1.Form1_Load(Object sender, EventArgs e) in
c:\temp\testrelationserror\form1.cs:line 95
   at System.Windows.Forms.Form.OnLoad(EventArgs e)
   at System.Windows.Forms.Form.OnCreateControl()
   at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
   at System.Windows.Forms.Control.CreateControl()
   at System.Windows.Forms.Control.WmShowWindow(Message& m)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
   at System.Windows.Forms.ContainerControl.WndProc(Message& m)
   at System.Windows.Forms.Form.WmShowWindow(Message& m)
   at System.Windows.Forms.Form.WndProc(Message& m)
   at System.Windows.Forms.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,
IntPtr wparam, IntPtr lparam)


  void Build()
  {
   DataSet ds = new DataSet();

   ds.Tables.Add(new DataTable());
   ds.Tables.Add(new DataTable());
   ds.Tables.Add(new DataTable());

   ds.Tables[0].Columns.Add("ClientID", typeof(int));
   ds.Tables[0].Columns.Add("ClientName");

   ds.Tables[1].Columns.Add("ClientID", typeof(int));
   ds.Tables[1].Columns.Add("ListID", typeof(int));
   ds.Tables[1].Columns.Add("ListName");

   ds.Tables[2].Columns.Add("ListID", typeof(int));
   ds.Tables[2].Columns.Add("Amount", typeof(int));
   ds.Tables[2].Columns.Add("Ticker");

   PopulateData(ds);

   ds.Relations.Add("Lists", ds.Tables[0].Columns["ClientID"],
ds.Tables[1].Columns["ClientID"]);
   ds.Relations.Add("ListDetail", ds.Tables[1].Columns["ListID"],
ds.Tables[2].Columns["ListID"]);

   DataColumn dc =null;

   dc = new DataColumn("# in List");
   dc.Expression = "Count(Child(Lists).ListID)";
   ds.Tables[0].Columns.Add(dc);

   dc = new DataColumn("Total Amount");
   dc.Expression = "Sum(Child(Lists)(ListDetail).Amount)";
   ds.Tables[0].Columns.Add(dc);

   dataGrid1.DataSource = ds;
   dataGrid1.DataMember = "Table1";
  }

  void PopulateData(DataSet ds)
  {
   Random r = new Random();
   DataRow dr = null;
   int ListID = 0;

   for(int i = 0; i < 5; i++)
   {
    dr = ds.Tables[0].NewRow();
    dr["ClientID"] = i;
    dr["ClientName"] = "Client " + i;
    ds.Tables[0].Rows.Add(dr);

    for(int j = 0; j < r.Next(1,10); j++)
    {
     dr = ds.Tables[1].NewRow();
     dr["ClientID"] = i;
     dr["ListID"] = ListID;
     dr["ListName"] = "List " + j;
     ds.Tables[1].Rows.Add(dr);

     for(int k = 0; k < r.Next(1,10); k++)
     {
      dr = ds.Tables[2].NewRow();
      dr["ListID"] = ListID;
      dr["Amount"] = r.Next(1000,2000);
      dr["Ticker"] = "Ticker " + k;
      ds.Tables[2].Rows.Add(dr);
     }

     ListID++;
    }
   }
  }

You can read messages from the Advanced DOTNET archive, unsubscribe from
Advanced DOTNET, or
subscribe to other DevelopMentor lists at http://discuss.develop.com.

_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
http://join.msn.com/?page=features/virus

You can read messages from the Advanced DOTNET archive, unsubscribe from Advanced DOTNET, or
subscribe to other DevelopMentor lists at http://discuss.develop.com.