We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

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

Thread ID:





145888 Jul 12,2019 02:23 AM UTC Jul 16,2019 12:12 PM UTC WinForms 4
Tags: PivotGridControl
George Busby
Asked On July 12, 2019 02:23 AM UTC

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 [Syncfusion]
Replied On July 12, 2019 12:25 PM UTC

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() 
            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 ObjectByVal 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, 
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. 
Subburaj Pandian V 

George Busby
Replied On July 13, 2019 01:40 AM UTC

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 [Syncfusion]
Replied On July 15, 2019 02:00 PM UTC

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.

Thirupathi B.

Subburaj Pandian Veluchamy [Syncfusion]
Replied On July 16, 2019 12:12 PM UTC

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 
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. 
Subburaj Pandian V 


This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile
Live Chat Icon