Inserting data into a SQL table within a business rule

[Marco]

Hi everyone. I'm trying to add values to a SQL table, I have checked with several functions of the BRApi.Database, but none of them work for me. My code example:

#The variables

Dim sqlTest As String = $"INSERT INTO AllocationResults (Step, Amount, Annotation, SourceRule, DriverRule, Type, StepDesc, TargetRule, AllocRule, Generation, Cube, Entity, Consolidation, Scenario, Time, View, Account, Flow, Origin, Ic) VALUES (1, {Amount}, {Annotation}, {SourceRule}, {DriverRule}, {Type}, {StepDesc}, {TargetRule}, {AllocRule}, {Generation}, {Cube}, {Entity}, {Consolidation}, {Scenario}, {Time}, {View}, {Account}, {Flow}, {Origin}, {Ic})"

Using dbConnApp As DBConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si)

     BRApi.Database.ExecuteActionQuery(dbConnApp, sqlTest, False,True)

End Using 

[tledet]

With an extensibility rule,
Dim sqlStatement As String = String.Empty

sqlStatement = INSERT INTO [TableName] ([FieldName]) VALUES ('FieldNameValues') replace TableName with AllocationResults and FieldName and FieldNameVales based on the SQL provided.
Next
Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)

BRApi.Database.ExecuteSql(dbConnApp, sqlStatement, False)
End Using

May be necessary to log the SQL i.e., BRApi.ErrorLog.LogMessage(si,$"sql = {sqlStatement}") for troubleshooting purposes.

[Marco]

Hi, the result of the SQL is this, but when checking the Application Database table, no values have been entered.

INSERT INTO AllocationResults (Step, Amount, Annotation, SourceRule, DriverRule, Type, StepDesc, TargetRule, AllocRule, Generation, Cube, Entity, Consolidation, Scenario, Time, View, Account, Flow, Origin, Ic, UD1, UD2, UD3, UD4, UD5, UD6, UD7, UD8) VALUES (1, -1197045.224463520, 'A_PL_32906_D1201_PL_32911: (Source Value 1,197,045.22 * PctSplit 100.00 %) * (Driver Value 1.00 / Driver Total 1.00) * FxRate 1.0000 * Alloc Sign -1', 'S_PL_32906_D1201', 'D_PL_32906_D1201', 'NetOff', 'Step 1', 'T_PL_32906_PL_32911', 'A_PL_32906_D1201_PL_32911', 1.01, 'HLFPLN', '32902_32906', 'Local', 'ForecastAllocation', '2023M1', 'Periodic', 'cra_639950', 'OutCostsAllocation', 'BeforeAdj', 'None', 'D1201', 'P0000', 'LOCAL_DATA', 'PJ00000', 'None', 'None', 'None', 'None')

[DanielWillis]

Is there no error?

[tledet · Accepted solution]

Change "View" to [View] as view is a reserved T-SQL KEYWORD

[Marco]

Thank you very much, this was what I was missing, I appreciate it a lot