Configure TableAdapter fails when using expression columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kjetil Klaussen - 28 Feb 2006 14:08 GMT

Hi,

I’m having some problems with configuring a TableAdapter on a DataSet. The
problem occurs when I have to related tables defined in a dataset
(parent-child relation). If I add an expression column to the child table to
map to a column in the parent and I try to configure the TableAdapter on the
child table, I get an error message saying;

Configure TableAdapter [table name] failed.
Cannot find the parent relation ‘[relation name]’.

To reproduce the behavior I’ll be using the Northwind database as an example.

1.    Create a new dataset and add two related tables from the Northwind
database (e.g. by dragging ‘Orders’ and ‘Customers’ into the designer from
the Server Explorer).
2.    Add a column to the child table ‘Orders’ and call it ‘expCustomerName’.
3.    Open the properties sheet for this column and set the ‘Expression’ field
to ‘Parent(FK_Orders_Customers).CompanyName’. This will map the content of
the column ‘Customers.CompanyName’ into the column ‘expCustomerName’.
4.    Right-click on the ‘OrdersTableAdapter’ in the DataTable ‘Orders’ and
choose ‘Configure…’

This will result in the following error message;

Configure TableAdapter Orders failed.
Cannot find the parent relation ‘FK_Orders_Customers’.

Signature

Sincerely,

Kjetil Klaussen
Application Developer, Trondheim

Reply to this Message

gary7 - 28 Feb 2006 16:35 GMT

Hi Kjetil; I just tried this and was successful.

You should add the new column and expression, and configure the adapter
before dragging and dropping the data controls onto the form.

1) What I did was set the data tables for the app as Customer table and
Orders Table. Set the relation as Parent(Customers)/Child(Orders).

2) Added a new column to the OrdersTable = expCustomerName

3) Configured the adapter.
4) Dragged the Customer Table in 'Details View' onto the form.
5) Dragged Orders Table in 'DataGridView' onto the form.
6) Checked for added column present in grid view.
7) Ran App and verified that Company Name was appearing in new column.

Hope this helps!

Reply to this Message

gary7 - 28 Feb 2006 16:37 GMT

Oh and also, the expression I used: Parent.CompanyName. Works great!

Reply to this Message

Kjetil Klaussen - 01 Mar 2006 08:23 GMT

Hi,

Thanks for your reply, Gary!

First of all I must clarify that my problem is not with the Windows Forms
designer – it’s with the dataset designer. So adding the dataset to a form
got nothing to do with it. But you we’re right; changing from
‘Parent(FK_Orders_Customers).CompanyName’ to ‘Parent.CompanyName’ did the
trick! But then again; what if I add another related table to my dataset? For
instance, adding the ‘Employees’ table. The ‘Orders’ table is also a child of
this table and so if I try to set ‘Parent.CompanyName’ on the expression
column, I get this message;

“Property value is not valid
The table [Orders] involved in more than one relation. You must explicitly
mention a relation name in the expression 'System.Data.LookupNode'.”

And so my question remains unanswered; how do I configure a TableAdapter for
a table that has an expression column that relates to a parent’s column?

Signature

Sincerely,

Kjetil Klaussen
Application Developer, Trondheim

> Oh and also, the expression I used: Parent.CompanyName. Works great!

Reply to this Message

gary7 - 02 Mar 2006 16:31 GMT

Hi Kjetil;

I would suggest maybe setting all related tables in whatever the
relationship, then configure the adapter BEFORE setting the computed
column and its expression.

This makes sense (at least to me for now) in that computed columns in
datasets rely on information that is persistent in the database -- or
data from some other point in the application -- but require dataset
elements (or objects) to carry out the expression. So if you try to
configure an adapter with a computed column that relies on information
not present in the database(I.E.: computed column), you will receive an
error, or the same could happen if you attempt to configure the table
when the mapping includes columns not present in the database.

Seems the order of operations is the key in configuring the table; but
I haven't had a chance to work on this in the way you described in your
last post.

Let me know if things are still stuck, and I'll try to experiment a bit
as well.

Gary

Reply to this Message

Kjetil Klaussen - 02 Mar 2006 16:49 GMT

Yes, I agree; it makes sense to set up all relations and configure the table
adapter before adding expression columns. And in most cases this will work
just fine and you won’t suffer from any unexpected error messages.

But what if you’ve set up everything and it all works like a charm, and then
later on you find out that you need to add another method to your table
adapter? Maybe you want to add a method to get the "last 10 orders". Then
you’ll be coming back to your dataset, that now includes an expression
column, and you’ll want to re-configure you’re table adapter. Guess what’s
going to happen…

To me this seems like a bug in Visual Studio 2005 – or at least a very
annoying "feature"...

Signature

Sincerely,

Kjetil Klaussen
Application Developer, Trondheim

> Hi Kjetil;
>
[quoted text clipped - 19 lines]
>
> Gary

Reply to this Message

gary7 - 02 Mar 2006 17:04 GMT

Hi Kjetil;

I re-posted my last comments to include more specific info, hope it is
helpful.

I understand what you are saying, and it may very well be a bug, but
I'll have to do some experimentation before I make that call as well.
It would seem that setting new methods should be OK in the dataset; but
I seem to remember that when you do so, a new table is created to
handle the new method, and probably would not include the prior methods
and table mappings, or computed columns with expressions.

Not sure just yet, but seems like a fun research project!

Let me know how things go!

Gary

Reply to this Message

gary7 - 02 Mar 2006 16:53 GMT

Hi Kjetil;

I would suggest maybe setting all related tables in whatever the
relationship, then configure the adapter BEFORE setting the computed
column and its expression.

This makes sense (at least to me for now) in that computed columns in
datasets rely on information that is persistent in the database -- or
data from some other point in the application -- but require dataset
elements (or objects) to carry out the expression. So if you try to
configure an adapter with a computed column that relies on information
not present in the database(I.E.: computed column), you will receive an

error, or the same could happen if you attempt to configure the table
when the mapping includes columns not present in the database.

As for more than one child table,
MSDN says:

If a table has more than one child, the syntax is: Child(RelationName).
For example, if a table has two child tables named Customers and
Orders, and the DataRelation object is named Customers2Orders, the
reference would be:

Avg(Child(Customers2Orders).Quantity)

Here's the link for the article that includes this info:

http://tinyurl.com/6dlv4

Seems the order of operations is the key in configuring the table; but
I haven't had a chance to work on this in the way you described in your

last post.

Let me know if things are still stuck, and I'll try to experiment a bit

as well.

Gary

Reply to this Message

Kjetil Klaussen - 02 Mar 2006 17:03 GMT

Yes, I'm aware of this syntax as I pointed out in my post on 1st of March.
And this is exactly what causes the problem. Please read my last post.

Signature

Sincerely,

Kjetil Klaussen
Application Developer, Trondheim

> Hi Kjetil;
>
[quoted text clipped - 36 lines]
>
> Gary

Reply to this Message

gary7 - 02 Mar 2006 17:12 GMT

I'm sorry, but that is not what I read in your initial post. I see an
attempt to use sytax that is for multiple parent - Child tables, not
two. You needed the syntax for 2.

Perhaps the syntax is in fact what is needed.

I think you need to set this up as order of operations and call the
specific table relation. If that isn't working, I'm not sure what else
to say except good luck!

Reply to this Message

Kjetil Klaussen - 03 Mar 2006 08:51 GMT

I’m not quite sure if I catch what you’re saying here; “You needed the syntax
for 2.”

It’s correct that I’m using the syntax for multiple parents, and the reason
is simple; in real world application that’s what relational design is all
about! Have you ever seen a RDBM that has only singular relations? And so
yes; my quest for multiple parents in the dataset designer continues…

As for the ‘Child(RelationName)’ syntax; I really don’t know why you brought
this up; the relation I’m interested in is a child-parent relation – not
parent-child. To take it back to my Northwind example, the table I’m adding
my expression column to is the ‘Orders’ table which is a child of ‘Customers’
and ‘Employees’ (meaning that ‘Orders’ have the foreign keys ‘CustomerID’ and
‘EmployeeID’). It makes no sense to talk about ‘Child(RelationName)’ in the
‘Orders’ table when I want to display an expression column on its parent’s
column ‘CustomerName’.

Anyway; I’ve decided to send an enquiry to Microsoft support and see what
they come up with. The only work-around I can find is to temporarily delete
the expression column, reconfigure my table adapter, and add the expression
column back when I’m done.

Signature

Sincerely,

Kjetil Klaussen
Application Developer, Trondheim

> I'm sorry, but that is not what I read in your initial post. I see an
> attempt to use sytax that is for multiple parent - Child tables, not
[quoted text clipped - 5 lines]
> specific table relation. If that isn't working, I'm not sure what else
> to say except good luck!

Reply to this Message

gary7 - 03 Mar 2006 19:03 GMT

Hi Kjetil;

OK, here's how things have worked so far (and trying to keep with the
relationship you have in mind), I set up a data set from Northwind with
multiple tables and their associated relationships:

Product Table, and related tables -- Categories, Suppliers, and Order
Details.

To the Product Table I add a column in the data set designer called
CompanyName.

I want to use the expression property to map the value from the
Description column of the parent table (Categories).
(I arbitrarily picked a related table column -- no other reason)

The syntax for the expression results in this entry for the column:
(Parent(Products_Categories).Description)

And now the new column I added in the designer populates with the
description data from the related Parent table.

If I now try to configure the data table in the data set design, I get
an error stating that the relationship can't be found.
That makes sense since I changed the object properties by adding a new
column to the existing table, and since this new computed column and
its expression are in the data set, and NOT the data base, a new
configuration at this point MUST fail. So if I want to configure the
adapter, I'll need to add a new one, with whatever methods are required
for update, fill, etc..

But...

What I can do however, is add a new query by right clicking on the
table in the designer, and implementing the new query wizard. This will
allow you to create a new configuration in the table, along with the
original fill, get data config.
This has worked great in my experimentation; and I didn't have to
create new tables and adapters, or delete anything existing.

I hope this is closer to what you are attempting, Please let me know!

Good Luck!

Gary

Reply to this Message

gary7 - 03 Mar 2006 21:46 GMT

Hi Kjetil;

one other thing I have discovered is that Child table references in
expressions are used primarily for aggregating values, and are not for
general expressions.

So something like:

SUM(Child(<Relation_Name>).ColumnName)

is a correct use, but a non-aggregating function is not(dropping the
prepend operator SUM).

Whether this can be overcome through code, I don't know and sounds way
to complex to attempt.

Hope this helps!
Gary

Reply to this Message

gary7 - 28 Feb 2006 16:41 GMT

Hi Kjetil; I just tried this and was successful.

You should add the new column and expression, and configure the adapter

before dragging and dropping the data controls onto the form.

1) What I did was set the data tables for the app as Customer table and

Orders Table. Set the relation as Parent(Customers)/Child(Orders).

2) Added a new column to the OrdersTable = expCustomerName (type =
string)

3) Set the Expression as Parent.CompanyName

4) Configured the adapter.
5) Dragged the Customer Table in 'Details View' onto the form.
6) Dragged Orders Table in 'DataGridView' onto the form.
7) Checked for added column present in grid view.
8) Ran App and verified that Company Name was appearing in new column.

Hope this helps!

Reply to this Message

Configure TableAdapter fails when using expression columns

Free Magazines