Expression column prevents DataAdapter from Configuring.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MarcG - 28 Aug 2007 22:02 GMT

I have a database view that I am using as a table in a dataset. Initially,
right clicking on the base Fill,GetData query and selecting "configure"
produces the expected behaviour.

One of the fields is "IsNA" and its properties show it as System.Int32 and
its Name and Source are IsNA.

I then add the following three expression columns:

IsComplete ... Iif (EndTime is Null,IsNA,1)
CountedStepDuration ... iif(IsNA=0,StepDuration,0)
CountedNominalStepTime ... iif(IsNA=0,NominalStepTime,0)

StepDuration and NominalStepTime are both System.Int32 columns in the view.

Now, if I right click on Fill,GetData and select Preview, I get the Preview
Data dialog and when I click Preview, I get the expected results in the
Results pane, including the correctly calculated fields.

However, if instead of clicking Preview, I click Configure, I get an error
dialog with the text

Configure TableAdapter TimeOnStep failed.
Cannot find column [IsNA]

If I go back and change the expression for IsComplete

from Iif (EndTime is Null,IsNA,1)
to Iif (EndTime is Null,0,1)
or Iif (EndTime is Null,StepDuration,1)

the Configure works without issuing the error dialog. Note that just to make
sure I wasn't tripping over a keyword, I also tried
Iif (EndTime is Null,[IsNA],1)
but it didn't help. The expression property does not object in either case
when the expression is validated when the entry loses focus.

If I specify Iif (EndTime is Null,XYZ,1), where I have no such column, when
I move the cursor out of the expression, I get a "Properties Window" error
dialog with the message "Property value is not valid". Clicking on the
Details button reveals the message "Cannot find column [xyz]".

So, it looks like the expression property itself is happy with the reference
to IsNA, but the Configure action doesn't seem to like it.

???
Marc

Reply to this Message

WenYuan Wang [MSFT] - 29 Aug 2007 13:41 GMT

Hello Marc,

According to your description, you meet an issue that VS 2005 IDE throw an
exception reads "Configure TableAdapter TimeOnStep failed. Cannot find
column [IsNA]" in Configuration Wizard if there is an expression column
"Iif (EndTime is Null,IsNA,1)". Please correct me if I misunderstood
anything.

I have tried to reproduce this issue on my side. I follow the steps as
below:
1)    created a table (named Table_1) (include two columns c1 and IsNA ) on
SQL 2005.
2)    added a Typed DataSet in VS 2005.
3)    dragged Table_1 from Sever Explorer into DataSet designer. (VS 2005
generated DataTable Table_1 for me)
4)    right-clicked on c1 column and selected Add->Column
5)    Typed "c3" for Column name, typed "Iif (IsNA is Null,IsNA,1)" in
Expression, and changed DataType to System.Int32
6)    At last, I right clicked on "Fill,GetData()" and select "Configure...".
All this works fine for me. I didn't get Error Dialog. I also tried these
steps both on VS 2005 Team Suit with sp1(8.0.50727.867) and on VS 2005 Team
Suit without sp1(8.0.50727.42). All work fine.

It seems the issue is not related to IsNA keyword. Am I missing something
here?

In order to narrow down the issue, would you please try the above method
and let me know the version of your VS 2005? If you have any more concern,
please also feel free to let me know. It's my pleasure to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

MarcG - 29 Aug 2007 16:04 GMT

Wen Yuan,

I GOT IT!!!! This is a VS bug.

Go back to your sample as you have it at the end (both IsNA and c3 are
present.

Change the C3 expression to iif(c1 is Null, 1,0) So that there is no
reference to IsNA.

Do the Configure (which will work) and go into the query builder and uncheck
IsNA so that it is no longer part of the results. Close the configure dialog.
Observe that IsNa is no longer visible in the table in the dataset.

Do the Configure again and check IsNA so that it is now part of the results,
close the designer and observe that IsNA is in the table again BELOW C3.

Change the C3 expression to iif(c1 is Null, IsNA, 0), hit TAB to leave the
expression field and observe that there is no error dialog.

Right click on Fill,GetData and select Configure... and observe the error
dialog "Cannot find column [IsNA]".

I believe that the problem comes from the fact that the Configure action is
(erroneously) sensitive to the order in which the columns appear.

I got into this fix because originally my IsComplete expression was
different, and I later introduced the IsNA field, updated the table adapter
to include the field (thus adding it at the end), and changed IsComplete to
depend on IsNA.

If you go back to your example, delete C3 and add it again, it will appear
at the end of the list. Add the expression iif(C1 is Null, IsNA, 1) and do
the Configure and all will be well. In my case, I couldn't do this since my
equivalent of C3, (IsComplete) is referenced in expressions in dependent
tables.

Incidentally, when I attempted to delete the IsComplete column I got an
message box with the text "Failed to delete object(s) '{0}'. {1}."

This bug is localized to the dataset designer - when the app is run there is
no error. However, in its current state I can not edit any of the other
tables in the dataset since they are all in a chain with TimeOnStep at the
bottom and use TimeOnStep fields in their expressions.

I went into the XSD source and swapped the IsNA and IsComplete elements so
that IsComplete follows IsNA. When I went back into VS the table reflected
this new order and Configure now works.

Reply to this Message

MarcG - 29 Aug 2007 16:52 GMT

Spoke too soon - not out of the woods yet.

My table, ChecklistSections is on the many side of a many to one relation to
TimeOnStep, the table we have already been discussing.

TimeOnStep now configures, however, when I attempt to configure the superior
table, ChecklistSections, that has expression columns with aggregate
functions (e.g., Sum(Child.NominalStepTime)), I get a messagebox saying:

" Configure aTableAdapter CheckListSections failed.
Unbound reference in the aggregate expression 'System.Data.AggregateNode' "

I had this problem when the problem with TimeOnStep showed up and I assumed
that it was because there was a problem where the lower expression was in
error so the upper expression could not resolve. This was an incorrect
assumption on my part since the TimeOnStep table now configures. So this
can't be blamed on my manually reordering the two lines in the XSD file.

Again, this is a VS designer problem since the app builds and runs properly.

Reply to this Message

MarcG - 29 Aug 2007 22:22 GMT

PS -

The reason why I don't just wipe the dataset and start over is that it has
several tables, each of which has a number of expression columns, so
reproducing the dataset would be a pile of work and there's a good chance of
screwing up one or more expressions along the way.

Marc

Reply to this Message

WenYuan Wang [MSFT] - 30 Aug 2007 13:42 GMT

Hello Marc,

Thanks for your reply. I have reproduced all these issues("Cannot find
column [IsNA]" exception and "Configure aTableAdapter CheckListSections
failed." Exception) on my side.

We need to perform more research on this issue . I will reply here as soon
as possible.
If you have any more concerns on it, please feel free to post here.

Thanks for your understanding!
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

WenYuan Wang [MSFT] - 31 Aug 2007 11:38 GMT

Hello Marc
Thanks for your waiting.

I have reproduced these issues.
1) If the columns are in wrong order, Expression column will prevent
DataAdapter from Configuring
2) If there is an Expression Column (Express: sum(child.column1)) in Table,
this will prevent DataAdapter from Configuring.

For the first issue, we could edit XSD file directly to work around.
However, there is no work around for the second problem so far.

I have searched on our bug list today. However, there is no entry which is
related to this issue now.

I agree it seems like a VS 2005 designer problem. It is necessary for me to
consult VS product team on this issue. I'm indeed sorry for any
inconvenience this may have caused. If I could get any information, I'll
update here.

Thanks again for your patience.
Have a great weekend,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Expression column prevents DataAdapter from Configuring.

Free Magazines