How do I update the underlying data source when an cell is edited on a Pivot Grid using vb.net

[George Busby]

How can I determine the corresponding datasource record that is associated with a specific Pivot grid cell after the value is changed. I have set the grid up so I can edit the values but there is no event associated with the VB Pivot Grid control that fires after a change. So how do i trigger an event and how do I determine the recordset row that corresponds to the cell so I can update the value in the database.

[Subburaj Pandian Veluchamy]

Hi George, 

 

Thank you for contacting Syncfusion support.

We have analyzed the reported query “How to notify the changes while editing the cell values in pivot grid control”.  You can achieve this requirement by using the CurrentStartEditing and CurrentCellEditingComplete event handlers. 

CurrentCellStartEditing - Occurs before the current cell switches into editing mode. 

CurrentCellEditingComplete - Occurs when the grid completes editing mode for the active current cell.

Please refer the below code sample, 

 

# Form1.vb 

        Public Sub New() 

            InitializeComponent() 

            PivotGridSettings() 

            AddHandler pivotGridControl1.TableControl.CurrentCellStartEditing, AddressOfMe.TableControl_CurrentCellStartEditing 

            AddHandler pivotGridControl1.TableControl.CurrentCellEditingComplete,AddressOf Me.TableControl_CurrentCellEditingComplete 

        End Sub 

  

        'Occurs while edting the cells 

        Private Sub TableControl_CurrentCellStartEditing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) 

            'Write your code here. 

        End Sub 

  

        'Occurs after complete the editing. 

        Private Sub TableControl_CurrentCellEditingComplete(ByVal sender As Object,ByVal e As EventArgs) 

            'Write your code here. 

        End Sub 

  

 

For more details, please refer our User Guide documentation from the following link, 

UG link: https://help.syncfusion.com/windowsforms/pivotgrid/editing-values

 

If the above solution does not meet your actual requirement could you please share the detailed description about your requirement, it could be helpful to provide the solution at the earliest. 

 

Regards,
Subburaj Pandian V 

[George Busby]

That gets me a little closer, now I know how to trigger an event after modifying a cell value. The next step is to figure out how to update the database with the new value. How do i know which database record is associated with the cell that was edited? To make it a little more difficult the data source I am using for the pivot grid is a non editable view. So I will have to build a SQL statement to edit the database through an editable data source on the form. So I need to know the values of the headers for the row and column so I can build a SQL statement to find the record in the editable datasource that I have on the form. 

as an example I have two datasources

1. ViewDataRecordSource

2. EditDataRecordSource

I will use the ViewDataRecordSource to load the Pivot Grid.  When a cell is edited I will have to find the corresponding record in the EditDataRecordSource and edit the value that matches the cross between the row and column. In my case the rows will be a person's name there are two columns 1 is the month the second is the weeks within the month. So I will have to get the Name ( row header value) and the week ( the second column header) so I can find that row in the EditDataRecordSource.

This is my assumption on how I will have to do this, you may know a better way to accomplish what i am trying to do.

[Thirupathi Bala Krishnan]

Hi George,

Thanks for the update.

Currently we are checking on the reported query, we will validate and update you the further details on July 16, 2019. We appreciate your patience until then.

Regards,
Thirupathi B.
 

[Subburaj Pandian Veluchamy]

Hi George,

Thank you for your patience.

We have analyzed the reported query, 

“How to update the Itemsource value after editing the cell value.” 

“How to find the row and column headers value for edited cell”.

You can find out the edited cell row and column index value by using the CurrentCellproperty. And then find the row/column headers by using the PivotEngine property. Here we have used the one pivot rows, one pivot columns and one pivot calculations in the sample. You need to modify the condition based on your collections. 

Please refer the following code sample, 

  

# Form1.vb 

Private Sub TableControl_CurrentCellEditingComplete(ByVal sender As Object, ByVal e As EventArgs) 

  

‘find the row and column index of edited cell. 

    Dim row As Integer = Me.pivotGridControl1.TableControl.CurrentCell.RowIndex - 1 

    Dim column As Integer = Me.pivotGridControl1.TableControl.CurrentCell.ColIndex – 1 

  

‘find the first row header. 

    Dim rowheader As String = Me.pivotGridControl1.PivotEngine(row, 0).FormattedText 

‘find the first column header. 

    Dim columnheader As String = Me.pivotGridControl1.PivotEngine(0, column).FormattedText

‘find the cell value after editing. 

    Dim value As Integer = Convert.ToInt32(Me.pivotGridControl1.PivotEngine(row, column).FormattedText.ToString())

‘Updates the ItemSource with new value. 

    For Each sales As ProductSales In productSalesData 

  

        If sales.Country = columnheader AndAlso sales.Date = rowheader Then 

            sales.Quantity = value 

        End If 

    Next 

End Sub 

  

If we misunderstood your actual requirement, could you please share the detailed description about your requirement so that it could be helpful to provide the solution at the earliest. 

 

Regards,
Subburaj Pandian V