question

durgavalliammai-7801 avatar image
0 Votes"
durgavalliammai-7801 asked XingyuZhao-MSFT commented

for each loop is duplicating

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
sql-server-generaldotnet-visual-basicdotnet-runtime
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


The loop ‘For Each row2 As DataRow In dt2.Rows’ does not seem to use any value of row2. Probably you should extract and use some fields of row2 and clarify the usage of USP_InsertSalesOrderPart.

By the way, ‘If obj.line_items IsNot Nothing Then’ is always true.

0 Votes 0 ·

For Each row2 As DataRow In dt2.Rows this return 2 values. but while inserting im getting as 4 values.

***The result I want :
My Order :

WHFT1100

WHFT1101


My OrderDetails :

WHFT1101 0 0 1278 FG-CHIC-0007 BBQ CHICKEN 500GMS

WHFT1100 0 0 1278 FG-CHIC-0007 BBQ CHICKEN 500GMS


But what I'm getting inserted into database now is :

My Order :

WHFT1100

WHFT1101


My OrderDetails :

WHFT1101 0 0 1278 FG-CHIC-0007 BBQ CHICKEN 500GMS

WHFT1100 0 0 1278 FG-CHIC-0007 BBQ CHICKEN 500GMS

WHFT1101 0 0 1278 FG-CHIC-0007 BBQ CHICKEN 500GMS

WHFT1100 0 0 1278 FG-CHIC-0007 BBQ CHICKEN 500GMS

0 Votes 0 ·
Viorel-1 avatar image Viorel-1 durgavalliammai-7801 ·

Probably you should filter the rows inside your inner For loop, something like this:

 'Sales Order Part
 For Each row2 As DataRow In dt2.Rows
    Dim refNo2 as String
    refNo2 = row2(“RefNo”)
    if refNo2 = row(“RefNo”) Then ‘ or maybe compare with OrderNum1?
       . . . insert the data . . .
    End If
 Next



0 Votes 0 ·

Hi @durgavalliammai-7801 ,
Could you provide more details about 'st.orders' , 'obj.line_items' and 'tblSalesOrder' table? It will help us make a test.

0 Votes 0 ·

1 Answer

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.