[Solved] use aggregate sums in update

[Buz Barnes]

Alright guys I am trying to use the aggregate sums in the grid footer to do an update everytime a grid value is updated.  When they edit a row or add a record in the grid I need an update to fire that uses the NEW footer values to update a seperate totals table after the record is changed or iserted and after the grid rebind with the new totals in the footer.  Of course to make it more interesting they want all five grids on the same page and i need to maintain those as well on update. 

Protected Sub radgrid_OtherDetail_ItemUpdated(ByVal source As Object, ByVal e As Telerik.Web.UI.GridUpdatedEventArgs) Handles radgrid_OtherDetail.ItemUpdated

getMyTotals()

 

End Sub

 

Protected Sub radgrid_LodgingDetail_ItemUpdated(ByVal source As Object, ByVal e As Telerik.Web.UI.GridUpdatedEventArgs) Handles radgrid_LodgingDetail.ItemUpdated

GetMyTotals()

 

End Sub

 

Protected Sub radgrid_DiningDetail_ItemUpdated(ByVal source As Object, ByVal e As Telerik.Web.UI.GridUpdatedEventArgs) Handles radgrid_DiningDetail.ItemUpdated

GetMyTotals()

 

End Sub

 

Protected Sub radgrid_EventDetail_ItemUpdated(ByVal source As Object, ByVal e As Telerik.Web.UI.GridUpdatedEventArgs) Handles radgrid_EventDetail.ItemUpdated

GetMyTotals()

 

End Sub

 

Protected Sub radgrid_TransDetail_ItemUpdated(ByVal source As Object, ByVal e As Telerik.Web.UI.GridUpdatedEventArgs) Handles radgrid_TransDetail.ItemUpdated

GetMyTotals()

 

End Sub

Protected

 

Sub GetMyTotals()

 

 

'Get the totals from grids for parameters in the totals update

 

Dim footerItemOther As GridFooterItem = CType(radgrid_OtherDetail.MasterTableView.GetItems(GridItemType.Footer)(0), GridFooterItem)

 

Dim footerItemDining As GridFooterItem = CType(radgrid_DiningDetail.MasterTableView.GetItems(GridItemType.Footer)(3), GridFooterItem)

 

Dim footerItemTransDetail As GridFooterItem = CType(radgrid_TransDetail.MasterTableView.GetItems(GridItemType.Footer)(0), GridFooterItem)

 

Dim footerItemLodging As GridFooterItem = CType(radgrid_LodgingDetail.MasterTableView.GetItems(GridItemType.Footer)(0), GridFooterItem)

 

Dim footerItemEvent As GridFooterItem = CType(radgrid_EventDetail.MasterTableView.GetItems(GridItemType.Footer)(0), GridFooterItem)

SqlDS_UpdateOtherTotals.UpdateParameters.Item(

 

"Total_Actual_Other_Expenses").DefaultValue = CDbl(footerItemOther.ToString)

SqlDS_UpdateOtherTotals.UpdateParameters.Item(

 

"Total_Actual_Travel_Expenses").DefaultValue = CDbl(footerItemOther.ToString)

SqlDS_UpdateOtherTotals.UpdateParameters.Item(

 

"Modified_Date").DefaultValue = Date.Now()

SqlDS_UpdateLodgingTotals.UpdateParameters.Item(

 

"Total_Actual_Lodging_Expense").DefaultValue = CDbl(footerItemOther.ToString)

SqlDS_UpdateLodgingTotals.UpdateParameters.Item(

 

"Total_Actual_Travel_Expenses").DefaultValue = CDbl(footerItemOther.ToString)

SqlDS_UpdateLodgingTotals.UpdateParameters.Item(

 

"Modified_Date").DefaultValue = Date.Now()

SqlDS_UpdateDiningTotals.UpdateParameters.Item(

 

"Total_Actual_Dining_Expense").DefaultValue = CDbl(footerItemOther.ToString)

SqlDS_UpdateDiningTotals.UpdateParameters.Item(

 

"Total_Actual_Travel_Expenses").DefaultValue = CDbl(footerItemOther.ToString)

SqlDS_UpdateDiningTotals.UpdateParameters.Item(

 

"Modified_Date").DefaultValue = Date.Now()

SqlDS_UpdateTransTotals.UpdateParameters.Item(

 

"Total_Actual_Transportation_Expense").DefaultValue = CDbl(footerItemOther.ToString)

SqlDS_UpdateTransTotals.UpdateParameters.Item(

 

"Total_Actual_Travel_Expenses").DefaultValue = CDbl(footerItemOther.ToString)

SqlDS_UpdateTransTotals.UpdateParameters.Item(

 

"Modified_Date").DefaultValue = Date.Now()

SqlDS_UpdateEventTotals.UpdateParameters.Item(

 

"Total_Actual_Event_Expense").DefaultValue = CDbl(footerItemOther.ToString)

SqlDS_UpdateEventTotals.UpdateParameters.Item(

 

"Total_Actual_Travel_Expenses").DefaultValue = CDbl(footerItemOther.ToString)

SqlDS_UpdateEventTotals.UpdateParameters.Item(

 

"Modified_Date").DefaultValue = Date.Now()

sqlDS_hugeTotal.Update() 

 

End Sub
Here is my sql:
UPDATE [Travel_Reimbursement] SET [Trip_Identifier] = @Trip_Identifier, [Total_Actual_Travel_Expenses] = @Total_Actual_Travel_Expenses, [Total_Actual_Other_Expenses] = @Total_Actual_Other_Expenses, [Total_Actual_Dining_Expense] = @Total_Actual_Dining_Expense, [Total_Actual_Lodging_Expense] = @Total_Actual_Lodging_Expense, [Total_Actual_Transporation_Expense] = @Total_Actual_Transporation_Expense, [Modified_Date] = @Modified_Date, [Total_Actual_Event_Expense] = @Total_Actual_Event_Expense WHERE [Reimbursement_Identifier] = @Reimbursement_Identifier

I'm guessing my timing is wrong and the values in the footer have not been recalced before the update fires.  Should i be using the itemupdated event or itembound and if i use itembound will it fire 5 times everytime the page loads?   I'm also wide open if anyone has a better way to accomplish this.

[Sebastian]

Hello Buz,

I think that the more appropriate place to perform the calculations and update the items in the grid footers is the Updating/Inserting events of the SqlDataSource control. Thus you will be able to change the values of the update parameters and avoid calling explicitly the Update() method of the data source control.

An alternative method to calculate the totals and display them in grid footer is presented in this section of the online documentation:

http://www.telerik.com/help/aspnet-ajax/grdtotalsingridfooters.html

Best regards,

Sebastian
the Telerik team

Check out Telerik Trainer , the state of the art learning tool for Telerik products.

[Buz Barnes]

I'm not trying to just display the new totals in the footer after an update.  I am trying to update an actual totals table in the database with the new footer values after an individual row in a grid has been updated.  There are five grids on the page and i will have to update the actual totals database table after any update, insert or delete to any of the grids.  I am having trouble figuring out when to fire the update totals table statement so that it has the new footer values rather than cached old values. 
1-add or edit a value in one of the grids
2- the grid does an autoupdate on the data effecting the grid
3- an update or insert statement (depending if there is a totals record for this travel request) is run with the new totals updating the totals table in the datase.
4- page dispays grid again with the new values.

[Sebastian]

Hello Buz,

Thank you for the clarification - now I see your point more clearly. Still I think that the most appropriate place to update the totals table and reflect the changes in all grids is the Updating/inserting event of the SqlDataSource control attached to the corresponding grid instance.

This is the stage before the actual update of the grid source where you can get the new values and reflect them in the totals. Thus when the Updated/Inserted events of the data source controls and the ItemUpdated/itemInserted events of the grid respectively are fired, the modification will be done and the grids will be refreshed accordingly.

With your approach you may need to invoke explicitly the Rebind() method of the grid(s) to ensure that the latest data from the totals table will be taken into account.

Best regards,
Sebastian
the Telerik team

Check out Telerik Trainer , the state of the art learning tool for Telerik products.