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