Use expression for value in data table column

[diamondsc]

I am using vb.net in visual studio 2005. I have a dataset in my project that contains multiple tables. I have used the dataset designer to add a new column to a table. I want to use an expression in the Expression Property to set the value for this column. The value should be a sum of a column in a different table in the dataset. I want to enter an expression into the Expression Property to do this. I have tried the following:

Select Sum(columnname) From tablename

I get the following error:

Property value is not valid.
Syntax error: Missing operand after 'sum' operator

Can this be done? If so, what is the correct syntax for the sql expression? Thanks.

 

[mansii]

Try

Select Sum('columnname') From tablename

Regards,
mansii

 

[diamondsc]

I neglected to say I've tried quotes everywhere I could think of in the SQL statement and I get the same error. I also tried referencing the column in the following ways:

Sum([tblName].[ColumnName])
Sum([tblName.ColumnName])
Sum([DataSetName.tblName.ColumnName])

I've placed the brackets in all the different configurations. I get the same message each time:

Property value is not valid.
Cannot find column
[DataSetName.tblName.ColumnName]

I guess my question at this point is, is it possible to set an expression in the Expression Property of the dataset designer to reference a column from a table other than the table that contains the calculated column? Thanks.

 

[diamondsc]

I found the correct syntax for the expression. It is:

Sum(Child(RelationshipName).ColumnName)

This only works if the two tables are related which in my case they are. However, I'm not getting the result that I should be getting. The relationship does exist between the two tables and there are existing rows in the child table that relates to one existing row in the parent table. I should be getting the sum of the values in the column in the child table from the rows that relates to the one record in the parent table but I am receiving a null value. What am I doing wrong?

Thanks.