Calculating Fields

[SEF]

I have a data grid in a form, i want to sum one of the columns in the data grid and then use that sum to populate a textbox.

The field name is 'Pallets' in the Table 'Orders', i want to sum the total number of pallets delivered over a period, so i have the orders in the data grid, i just need to sum the number of pallets, then dump that value in a textbox.

Need some advice on the best way to achieve this, im new to vb and learning as i go.

many thanks

 

[Arg81]

I have something similar, which is what cjard helped me with.

Is your Order table a child table to another? I.E. does your Order grid change values as you browse through the data?
E.G. You select a customer and all the orders for that customer appear in the grid. As you browse through the customers, the data in the grid changes accordingly?

Basically create another DataTable called SumPallets. In here create a SELECT SQL query (no need to create INSERT, UPDATE, DELETE) ;

SELECT sum(Pallets) as sumPal FROM Orders

Add a relationship from your Sum dataTable to your Order dataTable so that the correct value is shown as you browse through.

In "Data Sources" explorer, browse to your dataSet, expand Parent Table (customer?), expand Child Table (orders) and look for this sumPallets table. Expand, and you will see sumPal. Drag and drop this onto your label to bind it correctly.

There are various ways you can load this data. The easiest way for now is to load it all at form_load and let the dataRelation take care of the value of the label.

So...

Private Sub Form1_Load.........
 
me.customerdataadapter.fill(dataset.customerorder)
me.orderdataadapter.fill(dataset.orderdetails)
me.sumPalletdataadapter.fill(dataset.sumPallet)
 
End sub

Then as you browse through your rows, not only will you see the datagrid information change accordingly, you will also see that the value of the label (sum of pallets) changes as well.

...Hopefully that can help you
Regards,

 

[SEF]

Resolved

Thanks that done it!

Glad you guys are here to help us new ones

Once again Thanks