for each loop is duplicating

Question

Hi,

I have 2 for each statement. 1 is to get order and another one is to get the order details.

I have 2 orders and each order got 1 order details.

When i get the data from another application and do insert into my database , it returns 1 order with 2 order details.

so in total in my database instead of total of 2 order details I have 4, which is duplicating.

This is my code (the duplicating is happening in sales order part):

Dim dt As DataTable = New DataTable()
dt.Columns.Add("RefNo", GetType(String))
dt.Columns.Add("OrderDate", GetType(String))
dt.Columns.Add("TotalAmt", GetType(String))
dt.Columns.Add("TotalDisc", GetType(String))
dt.Columns.Add("Email", GetType(String))
dt.Columns.Add("OrderNum", GetType(String))
dt.Columns.Add("TotalTax", GetType(Double))
dt.Columns.Add("TotalWeight", GetType(Integer))
dt.Columns.Add("Remarks", GetType(String))
dt.Columns.Add("FirstName", GetType(String))
dt.Columns.Add("LastName", GetType(String))
dt.Columns.Add("ShipCode", GetType(String))
dt.Columns.Add("ShipPrice", GetType(Double))
dt.Columns.Add("SAddress", GetType(String))
dt.Columns.Add("SName", GetType(String))
dt.Columns.Add("STel", GetType(String))
dt.Columns.Add("SCity", GetType(String))
dt.Columns.Add("SPostCode", GetType(String))
dt.Columns.Add("SState", GetType(String))
dt.Columns.Add("SCountry", GetType(String))

    For Each obj In st.orders
        Dim RefNo As Long = obj.id
        Dim OrderDate As String = Convert.ToDateTime(obj.created_at).ToString("yyyy-MM-dd hh:mm:ss")
        Dim TotalAmt As Double = obj.total_price
        Dim TotalDisc As Double = obj.total_discounts
        Dim Email As String = obj.email
        Dim OrderNum As String = obj.name 'Order Name
        Dim TotalTax As Double = obj.total_tax
        Dim TotalWeight As Long = obj.total_weight
        Dim Remarks As String = obj.note
        Dim FirstName As String = ""
        Dim LastName As String = ""
        Dim ShipCode As String = ""
        Dim ShipPrice As Double
        If obj.customer IsNot Nothing Then
            FirstName = obj.customer.first_name
            LastName = obj.customer.last_name
        End If
        If obj.shipping_lines.Count > 0 And obj.shipping_lines IsNot Nothing Then
            ShipCode = obj.shipping_lines(0).title
            ShipPrice = obj.shipping_lines(0).price
        End If
        Dim SAddress As String = ""
        Dim SName As String = ""
        Dim STel As String = ""
        Dim SCity As String = ""
        Dim SPostCode As String = ""
        Dim SState As String = ""
        Dim SCountry As String = ""
        If obj.shipping_address IsNot Nothing Then
            SAddress = obj.shipping_address.address1
            SName = obj.shipping_address.first_name
            STel = obj.shipping_address.phone
            SCity = obj.shipping_address.city
            SPostCode = obj.shipping_address.zip
            SState = obj.shipping_address.province
            SCountry = obj.shipping_address.country
        End If

        dt.Rows.Add(RefNo, OrderDate, TotalAmt, TotalDisc, Email, OrderNum, TotalTax, TotalWeight, Remarks, FirstName, LastName, ShipCode, ShipPrice, SAddress, SName, STel, SCity, SPostCode, SState, SCountry)
    Next

    'datatable2 to insert record for list of products details from line_items
    Dim dt2 As DataTable = New DataTable()
    dt2.Columns.Add("RefNo", GetType(String))
    dt2.Columns.Add("LineID", GetType(String))
    dt2.Columns.Add("Title", GetType(String))
    dt2.Columns.Add("ProductID", GetType(String))
    dt2.Columns.Add("Quantity", GetType(Integer))
    dt2.Columns.Add("Grams", GetType(Double))
    dt2.Columns.Add("Price", GetType(Double))
    dt2.Columns.Add("SKU", GetType(String))

    For Each obj In st.orders
        For Each lineitems In obj.line_items
            Dim RefNo As Long = obj.id
            Dim LineID As Long
            Dim Title As String = ""
            Dim ProductID As Long
            Dim Quantity As Integer
            Dim Grams As Double
            Dim Price As Double
            Dim SKU As String = ""
            If obj.line_items IsNot Nothing Then
                LineID = lineitems.id
                Title = lineitems.title
                ProductID = lineitems.product_id
                Quantity = lineitems.quantity
                Grams = lineitems.grams
                Price = lineitems.price
                SKU = lineitems.sku
            End If

            dt2.Rows.Add(RefNo, LineID, Title, ProductID, Quantity, Grams, Price, SKU)
        Next
    Next

    'POST SALES ORDER TO EMAC
    Dim SOID1 As Integer
    Dim OrderDate1 As Date
    Dim UpdatedDate As Date
    Dim OrderNum1 As String = ""
    Dim SOCustCode As String = "300-ONLINE"

    'Post Sales Order to allocated shops db to save in tblSalesOrder and SalesOrderDetail

    For Each row As DataRow In dt.Rows

        'Get db connection for each branch
        Dim mDbsName As String = "WH_PCH"

        'check if the sales order number already exist in emac (if exist exit)
        Dim dataConn2 As SqlConnection = DBConnectionHelper.GetDataConn(mDbsName)
        Dim CountRefNo As Integer
        dataConn2.Open()
        Dim tsql2 As String = "SELECT Count(RefNo) From tblSalesOrder where RefNo =@RefNo"
        Using command As SqlCommand = New SqlCommand(tsql2, dataConn2)
            command.Parameters.AddWithValue("@RefNo", row("OrderNum"))
            CountRefNo = command.ExecuteScalar

            'If record not exist then insert into tblSIOrder
            If CountRefNo = 0 Then

                Dim dataConn As SqlConnection = DBConnectionHelper.GetDataConn(mDbsName)
                Dim ComBranchID As Integer = 1
                dataConn.Open()


                'Insert Statement
                Using transaction As SqlTransaction = dataConn.BeginTransaction
                    Try

                        'Sales Order
                        Dim sqlStr As String = "EXEC USP_InsertSalesOrder @SOID,@RefNo,@SODate

                        Dim sqlCmd As SqlCommand = New SqlCommand(sqlStr, dataConn, transaction)
                        sqlCmd.Parameters.Clear()

                        sqlCmd.Parameters.Add(New SqlParameter("@SOID", "0"))
                        sqlCmd.Parameters.Add(New SqlParameter("@RefNo", row("OrderNum")))
                        sqlCmd.Parameters.Add(New SqlParameter("@SODate", row("OrderDate")))

                        OrderDate1 = row("OrderDate")
                        UpdatedDate = row("OrderDate")
                        OrderNum1 = row("OrderNum")

                        ' Execute
                        Dim sqlDR As SqlDataReader = sqlCmd.ExecuteReader()
                        If sqlDR.Read() Then
                            SOID1 = sqlDR(0)
                        End If
                        sqlDR.Close()
                        sqlCmd.Dispose()

                        transaction.Commit()

                        'Sales Order Part
                        For Each row2 As DataRow In dt2.Rows

                            Using transaction2 As SqlTransaction = dataConn.BeginTransaction
                                Try


                                    'sql
                                    Dim sql2 As String = "EXEC USP_InsertSalesOrderPart @SOPID,@SOID,@SORefNo

                                    Dim cmd2 As SqlCommand
                                    cmd2 = New SqlCommand(sql2, dataConn) With {.Transaction = transaction2}

                                    cmd2.Parameters.Clear()
                                    cmd2.Parameters.Add(New SqlParameter("@SOPID", 0))
                                    cmd2.Parameters.Add(New SqlParameter("@SOID", SOID1))
                                    cmd2.Parameters.Add(New SqlParameter("@SORefNo", OrderNum1))

                                    ' Execute
                                    Dim sqlDR1 As SqlDataReader = cmd2.ExecuteReader()
                                    sqlDR1.Close()
                                    cmd2.Dispose()

                                    transaction2.Commit()

                                Catch ex As Exception
                                    transaction2.Rollback()
                                    dataConn.Close()
                                    Exit Sub
                                End Try

                            End Using

                        Next

                    Catch ex As Exception
                        transaction.Rollback()
                        dataConn.Close()
                        Exit Sub
                    End Try

                End Using

                dataConn.Close()

            End If

        End Using

        dataConn2.Close()
    Next

Answer 1

Rather than trying to understand why you get things double, I would suggest that you make a bigger change. Sending one row at a time is not the way to work with data. There can be a big performance penalty if there is a long distance between the application and the database. Which is not uncommon in the days of the cloud.

Instead it is better to send all data once by using a table-valued parameter. This also evades the needs for loops, and thereby the risk for that things go wrong with loops.

If you have never worked with table-valued parameters before, I have an article on my web site which is an introduction to this concept.Using Table-Valued Parameters in SQL Server and .NET.