Forum Discussion

Kneelay's avatar
Kneelay
New Contributor II
2 years ago
Solved

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
    2 years 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

     

JackLacava's avatar
JackLacava
Icon for OneStream Employee rankOneStream Employee
2 years ago

Is that chunk of code inside a cell loop? That's a bad idea - you're opening and closing a connection for each cell, I wouldn't be surprised if some were dropped.

A better strategy would be:

  1. open the connection (the Using line)
  2. build your FULL sql statement by looping through cells. The syntax is simple, you can see it inhttps://stackoverflow.com/questions/2624713/insert-multiple-rows-without-repeating-the-insert-into-part-of-the-stateme
  3. once the loop is over, execute your SQL with ExecuteSql
  4. end using

This makes it easier to also double-check all your records, by logging the sql query before step 3, to verify your assumptions about uniqueness.

Kneelay's avatar
Kneelay
New Contributor II
2 years ago

Yes that's correct, 

I tried following what you showed but not sure if it's correct. Somehow I still see the 112 records being added to the error log. I created a test variable and was trying to see how many times it loops, seems like only 10, even though it shows up 112 times.