Forum Discussion

RobbSalzmann's avatar
RobbSalzmann
Valued Contributor II
2 years ago

SQL Table Editor Unable to Save Data

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's avatar
    ckattookaran
    2 years ago

    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's avatar
    RobbSalzmann
    2 years ago

    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.



  • NicolasArgente's avatar
    NicolasArgente
    Valued Contributor

    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's avatar
      RobbSalzmann
      Valued Contributor II

      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:

       

  • 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's avatar
      RobbSalzmann
      Valued Contributor II

      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's avatar
      RobbSalzmann
      Valued Contributor II

      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's avatar
    RobbSalzmann
    Valued Contributor II

    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's avatar
      ckattookaran
      VIP

      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's avatar
        RobbSalzmann
        Valued Contributor II

        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.