Datagrid calculation help please

[johncassell]

Hi There,

I have a datagrid with several columns, I would like to be able to get the sum of one of those columns.

At the moment I have a label which calls a query on my tableadapter which does the sum. Only problem is that if I make any changes inside the grid, I must save them before the label wil update with correct figures.

Is it possible to calculate changes which have not been saved yet?

Thanks

John

 

[Arg81]

there are two ways in which to do this. One is to use your query to create the sum as another column, and then bind this to a label.

The 2nd way is to do this via form code. This is how I do it - works well, it does what it needs to do and I don't suffer any problems from it.

The code I use to sum up total costs is;

Private Sub TotalCost()
        Dim rows As Integer = Me.grdRecipeIngredients.Splits(0).Rows.Count
        Dim value As Decimal

        Dim i As Integer
        For i = 0 To rows - 1
            Dim s As Decimal = Me.grdRecipeIngredients(i, "IngredientCost")
            value = value + s
        Next i
        Me.lblTotalCost.Text = value.ToString("C")
End Sub

HOWEVER I use ComponentOne grids, and I'm not sure whether the code will be the same for the standard VS2005 DataGridView. you may need to play around with it a bit..

 

[cjard]

As Arg hinted in his first line:

You have a datatable, call it X
And it has a column, call it ItemPrice
And it has 10 rows, ItemPrice is 1 .. 10 (total is 55)

In the dataset designer, add another Column called SumItemPrice
Set the .Expression property of SumItemPrice to be SUM([ItemPrice])

Now EVERY value in that column will be 55

Because this looks a bit weird in a grid:

1, 55
2, 55
3, 55
...
8, 55
9, 55
10, 55

You would, like Arg81 says, bind that to a label so that no matter what record was on show right now (whichever is the .Current item in the grid) the label always shows 55

 

[johncassell]

Hiya fellas,

I have gone down the route to create it as another column and your instructions have explained things perfectly.

Thanks very much.

John

 

[FreeriderUK]

I tried to use this method to calculate the totals of an invoice - I just want to see the total in a text box.

But this returns the total of ALL invoice lines - not just from the invoice showing in the InvoicesDGV - which in turn, displays the invoice lines in InvoiceLinesDGV.

Should I be looking for a different way to get the correct total, or is it still possible?

 

[cjard]

Er.. The DataTable is doing the calculation, and the datatable is the model of data. It knows nothing of what control is showing what filtered view of some of its data.. You cannot reasonably expect it to calculate only what is showing in a particular grid!

Either align the model and the grid by having in the model ONLY what is shown in the grid (delete some rows from the datatable)

Or have your calc based on:
SUM(IIF([showMe] = true, [InvoiceAMount], 0 ))
And your filter based on:
.Filter = "[ShowMe] = true"

 

[FreeriderUK]

Or have your calc based on:
SUM(IIF([showMe] = true, [InvoiceAMount], 0 ))
And your filter based on:
.Filter = "[ShowMe] = true"

I'm not exactly sure what you mean.
Are you talking about the TableAdapter & bindingSource?
What does [ShowMe] relate to?

I've been thinking...

Wouldn't it be easier to include an InvoiceTotal in the Invoices table?
I seem to remember doing this many years ago, but was never sure it was the "proper" way to go.

 

[cjard]

no

TableAdapter = a device for moving data between DataTable and [a database]
BindingSource = position-aware middleman that mediates between DataTable and [a control that is databound]


[ShowMe] is a boolean column in your DataTable. I could have called it [VisibleInGrid]. You didnt create it yet. You set rows to be shown to have a TRUE in the [ShowMe] column
You set the .Filter of the BS to filter to only rows where ShowMe is TRUE
Then you have another column with an EXPRESSION property set to sum the rows where ShowMe is set to TRUE
THus only rows that are shown, are summed

If all this is too complex, then jsut remove the rows from your DataTable that you DONT want to show (you shouldnt be downloading huge amounts of data you wont use anyway) so the only rows that are there, are shown and also summed


No, do NOT put a column in your invoice details for the total; you ahve to recalc it every time you add a line. Either put this in the parent record (Invoice, not InvoiceDetails) and have the SUM() work along the relationship, or just have a phantom column that recalcs the totals every time a line is added but DO NOT persist the total to the database

FOr more info, see MSDN, DataColumn.Expression property