Datagrid

[barry t]

I have a database with two tables customers & sales what i would like to do is in the sales datagrid is to have a total column that calculates the quantity*price columns and shows the result in the total column i don't know if i need to do this with unbound column or with a databound column i looked at the msdn2 on the mirosoft site and just got more confused hope someone can point out steps to do this. thankyou Barryt

[f1 fan]

you create an unbound column in the sales and use the Expression property whose value is quantity*price (assuming those are the two column names). Only create bound columns if you want data saved in the database or pulled from the database (you could have created the column in your query and pulled it into your grid but when a new row is added the total wouldnt automatically update - whereas it does with an expression).

[barry t]

Hi F1 fan
I added a unbound column in the columns collection editor with the following code in the sales cs but i am not getting anything in the datagrid if you have time to look at this you might tell what i am doing wrong heres the code.

private void CalcColumn()
{
DataColumn Column1 = new DataColumn("Total",typeof(string));
Column1.Expression = "Quantity * Price";
database1Dataset.Columns.Add(Column1);
}

Thanks Barry

[f1 fan]

database1Dataset.Columns.Add(Column1);
that line is incorrect
Cannot add a column to a dataset... need
database1dataset.Tables["sales"].Columns.Add(Column1);
if sales was the table name

[barry t]

database1Dataset.Columns.Add(Column1); that line is incorrect Cannot add a column to a dataset... need database1dataset.Tables["sales"].Columns.Add(Column1); if sales was the table name

f1 fan
I changed the code but still not showing anything in the total column any other ideas that might solve this problem. thanks Barry

[f1 fan]

when do you call CalcColumn()?
You havent quite grasped the concept which is where you are going wrong but thats why we are here :)
Are you using a strong typed dataset to get your data? You dont want to add the unbound column to the grid in a grid editor (this isnt an unbound grid column its an unbound datacolumn - there is a big difference in the two).

Wherever you get your dataset from... before you pass it to the grid to bind it, thats where you need to put those lines of code.

It will work, its just you arent calling it at the correct time (if at all).

So you need to
1. Get your dataset from the database
2. Add the unbound column with the expression to the datatable
3. Bind it to the grid.

If you have a strong typed dataset then you can add the column there and even set the expression there. IF not then you have to use those few lines of code.

Try and grasp this concept (unfortunately they are similar names so everyone gets confused in the beginning). You have a database which has tables and columns. You have an ado.net dataset which has tables and columns (these can map one to one but do not have to! thats very important.. it was done deliberately to give us more power... old ado before ado.net would not let us split the two). You have a grid that has columns. All columns are different entities and although you can map or bind them they can exist without each other.

Hope i might have cleared things up a wee bit

[barry t]

Hi f1 fan
I still cannot get it to work might have to put this on the back burner till i learn a bit more thanks for your help Barry

[plazmo]

do this with sql and it will save you a lot of trouble, here is a quick example assuming the table has a column called price and one called quantity

SELECT Price, Quantity, Price * Quantity AS Total FROM myTable

the server will preform the multiplication for you add assign it the name "Total"

---

if your looking at getting the total from all the columns in the table no just a single row you could use this aggregate function
SELECT COUNT(*) AS NumberOfInvoices, SUM(Price * Quantity) AS TableTotal FROM myTable

count gets the number off rows included, and sum is a continuing addition value of each rows values, as evaulated.

[barry t]

Hi plazmo
Thankyou for your reply it works really well just one question if i add two more columns amountpayed & balance to my sales table is it possible to have two querys for the same row to show quantity*price=total-amountpayed=balance.
thanks Barry

[plazmo]

Hi plazmo Thankyou for your reply it works really well just one question if i add two more columns amountpayed & balance to my sales table is it possible to have two querys for the same row to show quantity*price=total-amountpayed=balance. thanks Barry

the above doesnt make sense but i think i understand what you mean like this?

try this, i dont know if this works tho

SELECT price, quantity, quantity*price AS Total, Total-amountpayed AS Balance FROM myTable

if that doesnt work just replace Total with the formula for total

SELECT price, quantity, quantity*price AS Total, quantity*price-amountpayed AS Balance FROM myTable