Primary key update for AddNew value in GGC

[Nicolas]

Hi, I have some code doing the Audit trail in GGC_RecordValueChanging event, and I am trying to catch the SQL server record Primary Key value of this new record (below here is the value in control PID.text)

In the simplified version of the code I used, what happens is as follows: the update of Datable helps to get the proper PK value, but the refreshed Datatable is not taking the value in the AddNew row. When the GGC1 refreshed, I have 2 new rows instead of 1 row, one row is empty, and the other with correct value. When I change to a new form and come back, then it's all good, the empty row has disappeared, and the new field value is recorded under the proper PK in the server...

I tried to use various combinations of BeginEdit, EndEdit, AddNew, even reset the GGC DataSource find a way around this

Private Sub GGC1_RecordValueChanging(sender As Object, e As RecordValueChangingEventArgs) Handles GGC1.RecordValueChanging

if PID.text < 0 then

      Me.Validate()

      curBindingSource.EndEdit()

      curTableAdapter.Update(curDataTable)

      Call AuditTrail( "New", PID.text)

      GGC1.refresh

else

      Call AuditTrail( "Edit", PID.text)

End if

End Sub

Just to let you know, initially, I had only the Call AuditTrail function, and with this only the Row behavior is good, but cannot get the proper PK value, specially if you deleted rows prior to this new addrow

I hope it's clear? Thanks in advance! - Nicolas

[Piruthiviraj Malaimelraj]

Hi Nicolas, 

Thanks for your interest in Syncfusion products. 

We have analyzed your reported scenario. We suspect from your example code that you are trying to restrict the new record which has invalid primary key value and trying to get the primary key value of the newly added record from SQL Server table. We are little bit unclear with your query and we need some more details about your scenario. Could you please provide us with the following details, 

·         Provide us with the sample video or screen shots of your scenario which represents the replication procedure of the issue. 

·         Provide with details of your customization that shows way of getting the primary key values from SQLServer. 

·         In sample code, you have used AuditTrail() method with different parameters (“New”, “Edit”) based on PID.Text value. Please let us know , the actual scenario of this method customization. 

These above details would be more useful for us to provide the exact solution at the earliest. Please let us know if we misunderstood anything. 

Regards, 

Piruthiviraj 

[Nicolas]

Hi Piruthiviraj!

here are the answers to your 3 questions:

1) Please find attached the screenshots, step-by-step, showing the behavior

2) I get the new PK with the following code that updates the SQL server - we could use the dataset generated PK, but should not as deletion may occur in between. For example, if the last PK value was 10 and you added and deleted a few rows, then dataset generated PK value would be 11 while the real PK value in SQL server would be 14. The following snippet insures a proper PK value

      Me.Validate()

      curBindingSource.EndEdit()

      curTableAdapter.Update(curDataTable)

3) The standard scenario "Edit" is all good, now I look at the "New" scenario

Hope this helps! - Nicolas

Attachment: screenshots_79585c8a.rar

[Piruthiviraj Malaimelraj]

Hi Nicolas, 

Thanks for the update. 

In order to avoid the duplicate rows added when grid is refreshed, you can use TableDirty property instead of Refresh method in SourceListRecordChanged event. We had analyzed grid with SqlServer data base by your replication procedure. The primary key value of newly added record is not changing in SqlServer if deleted or added the some rows.  We are unclear with “ if the last PK value was 10 and you added and deleted a few rows, then dataset generated PK value would be 11 while the real PK value in SQL server would be 14” in your update. Could you please explain your requirement clearly and when the improper primary key value is returned and what is the exact primary key you need to get in your case?. Please provide us your customization for retrieving the primary key value of the records. These would more helpful for us to find the exact root cause of your scenario. 

Code example: 

Me.Validate() 

Me.dataTableBindingSource.EndEdit() 

Me.dataTableTableAdapter.Update(dataSourceDataSet) 

Me.gridGroupingControl1.Table.TableDirty = True 

Regards, 

Piruthiviraj 

[Nicolas]

Hi Piruthiviraj, I used the TableDirty = True, and worked as expected, and table behave as expected : it shows one line only instead of 2 lines. Additionally the Audit trail can catch the correct PK value.

However, I receive the message "Object reference not set to an instance of an object" when the program exits the Sub and is back to the runtime form. This message went on a standard GGC message box, not those I use. I tried to catch the exception but it is not within the sub (RecordValueChanging where I placed the lines as show in your snippet + audit trail function), and placed break points all over to catch it, but could not catch the ex

Just to let you know, I tried the event SourceListRecordChanged with e.action = RecordChangedType.Added, but it is picking RecordValueChanging at first, so I placed the update lines inside the RecordValueChanging. I tried alternative with Validate / EndEdit inside the RecordValueChanging, and the remain (Update + TableDirty) within SourceListRecordChanged, but the results are the same, with the same error message

For the explanation on PK, it is irrelevant now that I can use the succession Me.Validate / end edit on binding source / Update table, which is the case here, as it is updating the Sql server directly and we can get the true PK value (PID.text is now showing the real PK value in Audit trail function) in opposition of using the PK value from the dataset (aka not using the Validate, endedit, update succession) which is not necessarily in phase with the Sql server. But As I said, as now we are updating the server directly, we have the true PK value, so leave this explanation  behind

Thanks in advance! - Nicolas

[Piruthiviraj Malaimelraj]

Hi Nicolas, 

Thanks for the update. 

We have analyzed your scenario. The RecordValueChanging event is triggered whenever the current cell is changing. So when moved to next cell which has null value , the exception is thrown. In our previous update, we have provided the solution with SourceListRecordChanged event. This event will be triggered, whenever moved to the next record. To avoid this exception, you can use SourceListRecordChanged event instead of RecordValueChanging event or please let us know, the requirement of RecordValueChanging event for your scenario. It would be more useful us to provide the exact solution at the earliest. 

Regards, 

Piruthiviraj 

[Gangabharathy Murugasen]

Sent: Wednesday, November 09, 2016 12:16 PM
To: Syncfusion Support <[email protected]>
Subject: RE: Syncfusion support community forum 127247, Primary key update for AddNew value in GGC, has been updated. 

Hi Piruthiviraj , Thank you for the details. I am using currently the RecordValueChanging mainly for my AuditTrail function which is using e.column and e.NewValue among other things. These are not available in SourceListRecordChanged, So i cannot basically move my AuditTrail function to SourceListRecordChanged. As I see it, I think we need to either find a way to validate the e.NewValue or trigger the SourceListRecordChanged. As I mentionned previously, I already tried to do an EndEdit on the BindingSource in the RecordValueChanging in order to trigger SourceListRecordChanged event with no success as the same  problem occurs.  

 

Thank you - Nicolas 

 

 

PS: is this just me, the Syncfusion website is all messed up? I tried with Edge, Explorer, Chrome and Firefox, with all the same result 

 

 

 

[Nicolas]

Hi, the website problem was related to an ad-block app, I disabled it for this site, and now it's all good.

Please check my latest code

            'Updates PK value
            Me.Validate()
            GlobalVariables.CurrentBindingSource.EndEdit()
            MyTableTableAdapter.Update(GlobalVariables.curDT)
            GridGroupingControl1.Table.TableDirty = True
            'Audit trail
            Call modMain.AuditTrailGGC(GridGroupingControl1, sender, e, "New", PID.Text)
            'Update server value
            Call UpdateSqlData(GlobalVariables.curDT, " [" & sender.TableDescriptor.Columns(0).Name & "] = " + PID.Text, e.Column, e.NewValue)
            'Update GGC dataset
            GridGroupingControl1.Table.Reload()

Now I have my dataset showing the good value, but I have still the same problem (Object reference not set to an instance of an object). - Nicolas

[Piruthiviraj Malaimelraj]

Hi Nicolas, 

Thanks for the update. 

We had analyzed your reported scenario. The NullReference exception has been thrown due to the invalid primary key. When the current cell is editing , the primary key for that record is created as -1 and while updating with this invalid key ,the current record gets disposed, so it throws the exception. For your requirement, you can use TableControlCurrentCellEditingComplete 

event and the current value and column name can be retrieved by using  GridTableCellStyleInfo for your customization. Please make use of the below code, 

 

Code example: 

this.gridGroupingControl1.TableControlCurrentCellEditingComplete += new GridTableControlEventHandler(gridGroupingControl1_TableControlCurrentCellEditingComplete); 

void gridGroupingControl1_TableControlCurrentCellEditingComplete(object sender, GridTableControlEventArgs e) 

{ 

  //To get the current cell value and column name for your customization. 

    GridTableCellStyleInfo style = e.TableControl.GetTableViewStyleInfo(e.TableControl.CurrentCell.RowIndex, e.TableControl.CurrentCell.ColIndex); 

    if (style.TableCellIdentity.Column != null && e.TableControl.CurrentCell.Renderer != null) 

    { 

        string columnName = style.TableCellIdentity.Column.Name; 

        object cellValue = style.CellValue; 

    } 

    this.Validate(); 

    this.dataTableBindingSource.EndEdit(); 

    this.dataTableTableAdapter.Update(dataSourceDataSet); 

    this.gridGroupingControl1.Table.TableDirty = true; 

} 

Regards, 

Piruthiviraj

[Nicolas]

Hi Piruthiviraj ! Finally got it! With your great help again!

When I applied your snippet, it did not worked, but after a few iteration I found that we should not make TableDirty = True under this event. So here is my snippet - I added a trigger to catch only new records, and removed TableDirty

Thank you! - Nicolas

Private Sub GridGroupingControl1_TableControlCurrentCellEditingComplete(sender As Object, e As GridTableControlEventArgs) Handles GridGroupingControl1.TableControlCurrentCellEditingComplete

Dim style As GridTableCellStyleInfo = e.TableControl.GetTableViewStyleInfo(e.TableControl.CurrentCell.RowIndex, e.TableControl.CurrentCell.ColIndex)

If Not IsNothing(style.TableCellIdentity.Column) AndAlso Not IsNothing(e.TableControl.CurrentCell.Renderer) AndAlso GlobalVariables.AddNew = 1 Then

Dim ColumnName As String = style.TableCellIdentity.Column.Name

Dim Value As Object = style.CellValue

Me.Validate()

GlobalVariables.CurrentBindingSource.EndEdit()

MyTableTableAdapter.Update(GlobalVariables.curDT)

Dim ID As Integer = DLastDT(sender.TableDescriptor.Columns(0).Name, GlobalVariables.curDT, "")

Call AuditTrailGGC_New(GridGroupingControl1, sender, "New", ID, ColumnName, Value)

GlobalVariables.AddNew = 0

End If

End Sub

[Piruthiviraj Malaimelraj]

Hi Nicolas, 

Thanks for the update. 

We are glad to know that the given solution is resolved your scenario. Please let us know if you have any other queries. 

Regards, 

Piruthiviraj