SQL Table Editor Unable to Save Data

[RobbSalzmann]

OS Version 7.4.2
I have a SQL Table Editor in a dashboard.  Three columns: Date, Amount, Descr.  The user can edit Amount and Descr. No Inserts, no deletes.
The table loads with a bunch of dates.  The user fills in amounts in records where needed. The descr and amount columns both accept nulls.
With the "Save Data Server Task" set to "No Task", when an amount is added to one record (or several), and save is clicked, the following error occurs:

With the "Save Data Server Task" set to "Execute Dashboard Extender Business Rule", I specify a Rule and get the same "Wrong Number of database rows" error.

If I use the SQL Table Editor code supplied by OneStream in the Rule, the error in the log is:

I'm trying another version of this which omits the use of the Using block. Same "Wrong Number of database rows" error and the log just shows "Unknown Error"

Public Class MainClass
  Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardExtenderArgs) As Object
    Dim saveDataTaskResult = Nothing
    Try
      Select Case args.FunctionType          
        Case Is = DashboardExtenderFunctionType.SqlTableEditorSaveData
          If args.FunctionName.XFEqualsIgnoreCase("SaveInvoiceStagedAmounts") Then
            SaveRecords(si, args)            
            saveDataTaskResult = New XFSqlTableEditorSaveDataTaskResult()
            saveDataTaskResult.IsOK = True
            saveDataTaskResult.ShowMessageBox = False
            saveDataTaskResult.Message = ""
            saveDataTaskResult.CancelDefaultSave = True               
          End If
      End Select
    Catch ex As Exception
      Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
    End Try
    Return saveDataTaskResult
  End Function
  
  Private Sub SaveRecords(si As SessionInfo, args As DashboardExtenderArgs)
    Dim dbConn As DbConnInfo = Nothing
    Try
         dbConn = BRApi.Database.CreateDbConnInfo(si, args.SqlTableEditorSaveDataTaskInfo.SqlTableEditorDefinition.DbLocation, args.SqlTableEditorSaveDataTaskInfo.SqlTableEditorDefinition.ExternalDBConnName)
        dbConn.BeginTrans()
        BRApi.Database.SaveDataTableRows(dbConn, args.SqlTableEditorSaveDataTaskInfo.SqlTableEditorDefinition.TableName, args.SqlTableEditorSaveDataTaskInfo.Columns, args.SqlTableEditorSaveDataTaskInfo.HasPrimaryKeyColumns, args.SqlTableEditorSaveDataTaskInfo.EditedDataRows, True, False, True)
        dbConn.CommitTrans()
        'prevent the UI from trying to save what we already saved, while allowing the ui to refresh to show the changes by 
        'leaving cancelDefaultSave as false
        args.SqlTableEditorSaveDataTaskInfo.EditedDataRows.RemoveAll(Function(x) x.InsertUpdateOrDelete = DbInsUpdateDelType.Insert)
        args.SqlTableEditorSaveDataTaskInfo.EditedDataRows.RemoveAll(Function(x) x.InsertUpdateOrDelete = DbInsUpdateDelType.Update)
    Catch ex As Exception
        dbConn.RollbackTrans()
        Throw New XFException($"{Environment.NewLine}{Me.GetType().ToString()}.{System.Reflection.MethodBase.GetCurrentMethod().Name}(): {ex.Message}", ex)
    Finally
        dbConn.Close()
    End Try
  End Sub
End Class

[ckattookaran · Accepted solution]

That is interesting. It should do what you are doing as a SQL already unless somehow the keys are messed up, and SQLTableEditor is sending queries to update multiple records.

[NicolasArgente]

Hi @RobbSalzmann !
Could you show us your raw table? And also a printscreen of your SQL Editor dashboard page?
If I were you, I would look in the RPTA solution (Application Reports) - I am pretty sure it is installed already on your server. You might find there a Dashboard under 0_Settings_RPTAT and there is an SQL editor with it. Have a look at this code!
Cheers

[RobbSalzmann]

Thanks, @NicolasArgente 
The table is as described above: 3 columns, Date, Amount, Description.  Amount and Description can be null. Dates are already filled in.  The user adds amounts where needed.
I have several STE components in use. They all have to be parsed in a rule - it usually takes days to find the magic combination of things to get them to work - UGH.  I've never been able to simply use an STE to edit a table.  I wonder what I'm missing here? 
Here is my configuration:

[ckattookaran]

Not sure why you are trying to remove the insert and updates.

  Private Sub SaveRecords(si As SessionInfo, args As DashboardExtenderArgs)
    Try
        Using dbConn As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
			dbConn.BeginTrans()
			BRApi.Database.SaveDataTableRows(dbConn, args.SqlTableEditorSaveDataTaskInfo.SqlTableEditorDefinition.TableName, args.SqlTableEditorSaveDataTaskInfo.Columns, args.SqlTableEditorSaveDataTaskInfo.HasPrimaryKeyColumns, args.SqlTableEditorSaveDataTaskInfo.EditedDataRows, True, False, True)
			dbConn.CommitTrans()
        end using
    Catch ex As Exception
        Throw New XFException($"{Environment.NewLine}{Me.GetType().ToString()}.{System.Reflection.MethodBase.GetCurrentMethod().Name}(): {ex.Message}", ex)
    End Try
  End Sub

Is the above not working? Also I do see why you are creating a DbConn variable, but a Using should works just fine isn't it?

[RobbSalzmann]

Since the STE cannot save the data on its own, I parse the modified rows and save the updates/inserts with queries.
The modified rows must be removed to allow the UI to refresh and update the STE with new (manually updated) entries.

If I use the default SQL Table Editor code supplied by OneStream in the Rule, the error in the log is:

A using block implies the System.IDisposeable interface is implemented.  DBConnInfo does not seem implement this interface.  I find outside declarations and the Try-Catch-Finally approach is better for debugging.  

[RobbSalzmann]

@ckattookaran I replaced my code with yours and ran it.  Here are the results:

IMO, the default STE helper code supplied with the Dashboard Extender rules is flawed in its error handling.  I use what I posted to overcome this.

[RobbSalzmann]

The following code is working.  It uses the STE as a dirty-cell aware spreadsheet, then writes and executes its own queries.  The requirement is straightforward: update rows in a table with values the user enters. 

IMO, a component called "SQL Table Editor" should be able to do this unassisted by code when its Save button is clicked.  I would like to be wrong here and learn what I'm missing.  At this point it feels like a bug.

[ckattookaran · Accepted solution]

That is interesting. It should do what you are doing as a SQL already unless somehow the keys are messed up, and SQLTableEditor is sending queries to update multiple records.

[RobbSalzmann · Accepted solution]

Yep, I added a PK column and the STE cooperates.  Professionally I have some thoughts about this, but I'll leave it as solved and kudo you the solution for giving me the idea and the time you spent sharing your thoughts!  Cheers.