Forum Discussion

Kneelay's avatar
Kneelay
New Contributor II
9 months ago

SQL Table Insert Not adding all Records

HI All,  I'm running into an issue when running a Finance Business Rule, essentially this rule creates a data buffer and loops through where U8 member is not 0. From here we have a SQL statement wh...
Finance
  • RobbSalzmann's avatar
    RobbSalzmann
    9 months ago
    Dim valueList As List(Of String) = New List(Of String)
Dim x As Integer = 0

For Each Cell As DataBufferCell In SrcDataBuffer.DataBufferCells.Values
    ' Execute If Allocation percentage Is Not equal To 0
    If Cell.CellAmount <> 0 Then			            
        x += 1
        sAcct = Cell.DataBufferCellPk.GetAccountName(api)
        sUD1 = Cell.DataBufferCellPk.GetUD1Name(api)
        sFlow = Cell.DataBufferCellPk.GetFlowName(api)
        sUD2 = Cell.DataBufferCellPk.GetUD2Name(api)
        sUD4 = Cell.DataBufferCellPk.GetUD4Name(api)
		valueList.Add($"('{sqlentity}','{sEntitymember}', '{sUD4}', '{sFlow}', '{sUD2}', '{sUD1}', '{sAcct}', {Cell.CellAmount})")
    End If
Next

Dim insertValues = String.Join(", ", valueList)

Dim sql As String = $"INSERT INTO XFT_BSM_Mapping_Total$ 
                     ([BU], [Entity], [BSM_Account], [BSM_Measure], [Responsibility_Center], [Company_Code], [Account],[Allocation_Percentage])
                     VALUES {insertValues}"
brapi.ErrorLog.LogMessage(si,x)

Using dbConn As DBConnInfo = BRAPi.Database.CreateApplicationDBConnInfo(si)
	BRAPi.Database.ExecuteSql(dbConn , sql, True)
End Using

     

RobbSalzmann's avatar
RobbSalzmann
Valued Contributor II
9 months ago

You should be using BRApi.Database.ExecuteSql here, not ExecuteActionQuery.
ExecuteActionQuery is for things like create and alter .

Also, avoid running SQL queries inside of loops.

  • Kneelay's avatar
    Kneelay
    New Contributor II
    9 months ago

    Thanks, I changed it to ExecuteSql instead. I'm trying to think of the best way to do this besides running the SQL query inside the loop, but having some issues with that. 

    • RobbSalzmann's avatar
      RobbSalzmann
      Valued Contributor II
      9 months ago

      Post your code so far.  I can give you a hand with it.  About how many data cells are you inserting?

       

      • Kneelay's avatar
        Kneelay
        New Contributor II
        9 months ago

        Posted below to my reply to Jack. For this particular case should be 112. It seems if it's less records it's doing it correctly.