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
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.
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.