calculate rows

[johmolan]

I use VS 2008, I have connected to an access database and made a datagrid from 1 table.

What I want to do is to make column C be the resault of column A and B.
I Tried :

BasisDataset.Tablename.ColumnC_Column = (BasisDataset.Tablename.ColumnA_Column * BasisDataset.Tablename.ColumnB_Column)

Can anyone give me a hint if this is possible in some way?

 

[jimmajsterski]

Am I reading your code wrong, or are you really multiplying an empty column by itself and expecting a "not empty" product?

 

[johmolan]

I was very tired last night so it is all my fault. Now I have edited the code I tried, So you can see what I really meen

 

[MattP]

It's possible using DataColumn Expressions. You can use an expression based off of a calculated column as well as shown here.

		Dim dt As New DataTable

		dt.Columns.Add("ID", GetType(Integer)).AutoIncrement = True
		dt.Columns.Add("Length", GetType(Integer))
		dt.Columns.Add("Width", GetType(Integer))
		dt.Columns.Add("Area", GetType(Integer), "Length * Width")
		dt.Columns.Add("Area Greater Than 20", GetType(String), "IIF(Area > 20, 'Yes', 'No')")

		With dt.Rows
			.Add(Nothing, 5, 5)
			.Add(Nothing, 10, 1)
			.Add(Nothing, 3, 4)
			.Add(Nothing, 8, 5)
		End With

		Me.DataGridView1.DataSource = dt

 

[johmolan]

Seems to me that what I asked i not possible. I asked if it was possible using the premade functions in VS. (BasisDataset.Tablename.ColumnC_Column etc)

I think The easiest way beside that would be to just make a db connection and use the SQL Lannguage to do the calculation

 

[cjard]

MattP gave you the answer. Here is the visual way:

Open the DataSet designer
Locate the table you wish to sum Column A and Column B
Make another column, C
Set the Expression property of C to be the string: "[ColumnA] + [ColumnB]"


Do it in SQL if you want, but this way updates the info as soon as it is entered

For more info see msdn DataColumn.Expression

or in code:
BasisDataset.Tablename.ColumnC_Column.Expression = "[ColumnA_Column] + [ColumnB_Column]"

 

[johmolan]

Thank you so very muck for you answer, it really helped me alot.

One more thing though.

How do I in code show the sum of the column in the textbox

Textbox.Text= Sum(BasisDataset.Tablename.ColumnC_Column)???

I can not seem to find the right way here either

Maybe I have to mak an variable forst and then into the textbox after??

 

[cjard]

If youre using databinding then you can add another column to your datatable and give it the expression:

.Expression = "SUM([WhateverColumnName])"

Now every row in the table will have a value that is the sum of all rows in the column:

ID, Val, Sum(Val)
A, 1, 10
B, 2, 10
C, 3, 10
D, 4, 10

So you can databind this column to a textbox, and no matter what row youre on, the textbox value will (look like it does) not change

 

[johmolan]

When I edit the info in column a and b the resault now shows in column c as it should, but when i edit the values and want to save the changes I get an error.

Ok, I will try that out, but why am I not able to save my table?

It says something like I can't update since the row is an expression?

 

[cjard]

when i edit the values and want to save the changes I get an error.

It says something like I can't update since the row is an expression?

Er, well thanks for that nice specific error report. Please reproduce the problem and paste the exact error

 

[johmolan]

Ok I will try to translate it as well as I can since my error-messages comes in Norwegian.

It says:System.InvalidOperationException {"The Columnarrangements from SourceColumn Planlagte kostnader Failed because DataColumn Planlagte kostnader is an expression."}


So it seems that I can not save the table since my column is an expression
But I am sure there is a way to fix that too

 

[cjard]

Having expression based columns does not ordinarily prevent a table from being saved into a database, but in all my projects I have been careful not to include the expression columns in database queries. Are you attempting to save the calculated values? Why? WHy not jsut calculate them next time theyre out?

 

[johmolan]

I use a datagrid to show the resaults, don't I need to have the expression in the query to me able to display the resaults? If not how do I then do it

 

[cjard]

Ah.. if you put the expression into the SQL query, then that would be why you ended up creating a read only column, possibly a read-only table.

When I say Expression, I mean a CLIENT SIDE calculation done by the DataTable.. NOTHING to do with the database, sql, etc

It was easier to make a video and project to illustrate my point than to talk about it. Refer to the attachment