Calculate columns

[johmolan]

How do I calculate 2 columns in a database and return the answer back to the table? Let us say I have Column A, ColumnB and Column C in my table and I want to Calculate Column A and B and return the answer in Column C.

I use a dataset connected to the access-databasefile.

I have a datagrid showing the table as well.

 

[Robert_Zenz]

Normally I'd do that with a simple SQL-Statement:

UPDATE SET ColumnC = ColumnA + ColumnB

Bobby

 

[cjard]

Better off using a local Expression to do this

Add another column to your typed datatable, and set its expression property to:
"[ColA] + [ColB]"

DataColumn.Expression Property (System.Data)

 

[johmolan]

Could you please show me an exaple on how to do this so the update function will work??
------------------------------------------------------------
I use

VBTestDatabaseDataSet.Boring.Planlagte_kostnaderColumn.Expression = "[Planlagte timer] * [Kr pr time]"

But when I try to save the data to the database I get an error sayin the save failed because my column [Planlagte_kostnader] is an expression.

How do I get the right result back to the table?



------------------------------

Did not exactly mean to much to me.

"yes I am a newbe"

 

[johmolan]

I tried to fix this but it will not save the sum og the columns to the datatable since it is an expression.

I checked out the link above. it says:

Dim instance As DataColumn
Dim value As String

value = instance.Expression

instance.Expression = value

Can someone please explain how this works?

Dim instance as Datacolumn = Name of the column?
Dim value as String = ?
value = instance.Expression = ?(Columna * Columnb)?

 

[cjard]

You wouldnt save the result back to the db, you'd calculate it using an expression each time it was needed

 

[johmolan]

but I need the result saved to the db, isn't that possible?

 

[cjard]

You wouldnt save any calculable result back to the DB! What if one of the operands is altered?

 

[johmolan]

I want i saved back to de db,

This way I can print out the table directly from the db and the calculations are correct.
The db is only used with this program, and if the operand is changed the result will also change and get saved back to the db. This is why I asked how I could do it.

 

[cjard]

i'd consider using a trigger on the table so that no matter how it was updated, the db would recalculate

can also manually rewrite your update statement in your prog, to do the update..

or just jsut a reports package when you print the table, and get it to do the calc

 

[MattP]

i'd consider using a trigger on the table so that no matter how it was updated, the db would recalculate

I believe he's using MS Access.

 

[johmolan]

I use a ms-access database

 

[johmolan]

Would it be possible to copy the values in the expressed column into another column in the table? or is that not a possibility

 

[cjard]

I don't think there is any problem in including a value from a calculated column in an update query but i'm fairly sure you have to add it yourself manually to the update query and then edit the parameters collection to specify the source column

I was going to say you can modify the update query to just have the extra bit in:

UPDATE table SET price = ?, quantity = ?, calculatedWorth = price * quantity WHERE id = ?

but this will use the old values, not the new ones in the calc

Access doesnt do named parameters so you'd have to replicate your parameter values:
UPDATE table SET price = ?, quantity = ?, calculatedWorth = ? * ? WHERE id = ?

and update the params colelction so that the price/quantity columns are mentioned twice

It's a pity youre not using a real database, because SQLServer or Oracle have s many ways to skin this particular cat, though I will reiterate that you shouldnt be storing values calculated from other values because they can go out of sync, and it's a waste of space