Private Sub Proc_Translate_810_To_SqlDb(ByRef oEdiDoc As ediDocument, ByRef sAppPath As String)
Dim sSegmentID As String
Dim sLoopSection As String
Dim nArea As Integer
Dim sSql As String = ""
Dim sN1Entity As String = ""
Dim sQlfr As String = ""
Dim sValue As String = ""
Dim sEdiFileName As String = txtEdiFile.Text.Trim()
' Specify EDI document to read.
oEdiDoc.LoadEdi(sEdiFileName)
'Open connection to SQL database
oConnection = New SqlConnection(sConnection)
oConnection.Open()
' Gets the first data segment in the EDI document.
Dim oSegment As ediDataSegment = oEdiDoc.FirstDataSegment
' Loop that will traverse through the EDI document from top to bottom. This
' is required for FORWARD-ONLY cursor.
Do While Not oSegment Is Nothing
' Data segments are uniquely identified by their segment Identifier (ID).
' The loop section and the area (or table) properties of the data segment
' are read for each iteration of the loop.
sSegmentID = oSegment.ID
sLoopSection = oSegment.LoopSection
nArea = oSegment.Area
If nArea = 0 Then
' Data segments that are not designated an area (i.e. area = 0) are control
' segments that are part of the Interchange or Functional Group envelopes,
' and are not part of the Transaction Set.
If sSegmentID = "ISA" Then
'insert a record into interchange table
sSql = "INSERT INTO [Interchange] (ISA01_AuthorizationInformationQualifier, ISA02_AuthorizationInformation, " _
& "ISA03_SecurityInformationQualifier, ISA04_SecurityInformation, ISA05_InterchangeIdQualifier, " _
& "ISA06_InterchangeSenderId, ISA07_InterchangeIdQualifier, ISA08_InterchangeReceiverId, ISA09_InterchangeDate, " _
& "ISA10_InterchangeTime, ISA11_InterchangeControlStandardsIdentifier, ISA12_InterchangeControlVersionNumber, " _
& "ISA13_InterchangeControlNumber, ISA14_AcknowledgmentRequested, ISA15_UsageIndicator, " _
& "ISA16_ComponentElementSeparator) " _
& "values " _
& "(@ISA01_AuthorizationInformationQualifier, @ISA02_AuthorizationInformation, " _
& "@ISA03_SecurityInformationQualifier, @ISA04_SecurityInformation, @ISA05_InterchangeIdQualifier, " _
& "@ISA06_InterchangeSenderId, @ISA07_InterchangeIdQualifier, @ISA08_InterchangeReceiverId, @ISA09_InterchangeDate, " _
& "@ISA10_InterchangeTime, @ISA11_InterchangeControlStandardsIdentifier, @ISA12_InterchangeControlVersionNumber, " _
& "@ISA13_InterchangeControlNumber, @ISA14_AcknowledgmentRequested, @ISA15_UsageIndicator, " _
& "@ISA16_ComponentElementSeparator); " _
& "SELECT scope_identity()"
oDaInterchange.InsertCommand = New SqlCommand(sSql, oConnection)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA01_AuthorizationInformationQualifier", oSegment.DataElementValue(1, 0)) ' Authorization Information Qualifier (I01)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA02_AuthorizationInformation", oSegment.DataElementValue(2, 0)) ' Authorization Information (I02)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA03_SecurityInformationQualifier", oSegment.DataElementValue(3, 0)) ' Security Information Qualifier (I03)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA04_SecurityInformation", oSegment.DataElementValue(4, 0)) ' Security Information (I04)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA05_InterchangeIdQualifier", oSegment.DataElementValue(5, 0)) ' Interchange ID Qualifier (I05)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA06_InterchangeSenderId", oSegment.DataElementValue(6, 0)) ' Interchange Sender ID (I06)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA07_InterchangeIdQualifier", oSegment.DataElementValue(7, 0)) ' Interchange ID Qualifier (I05)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA08_InterchangeReceiverId", oSegment.DataElementValue(8, 0)) ' Interchange Receiver ID (I07)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA09_InterchangeDate", oSegment.DataElementValue(9, 0)) ' Interchange Date (I08)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA10_InterchangeTime", oSegment.DataElementValue(10, 0)) ' Interchange Time (I09)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA11_InterchangeControlStandardsIdentifier", oSegment.DataElementValue(11, 0)) ' Interchange Control Standards Identifier (I10)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA12_InterchangeControlVersionNumber", oSegment.DataElementValue(12, 0)) ' Interchange Control Version Number (I11)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA13_InterchangeControlNumber", oSegment.DataElementValue(13, 0)) ' Interchange Control Number (I12)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA14_AcknowledgmentRequested", oSegment.DataElementValue(14, 0)) ' Acknowledgment Requested (I13)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA15_UsageIndicator", oSegment.DataElementValue(15, 0)) ' Usage Indicator (I14)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA16_ComponentElementSeparator", oSegment.DataElementValue(16, 0)) ' Component Element Separator (I15)
nInterchangeKey = oDaInterchange.InsertCommand.ExecuteScalar()
ElseIf sSegmentID = "IEA" Then
sValue = oSegment.DataElementValue(1) ' Number of Included Functional Groups (I16)
sValue = oSegment.DataElementValue(2) ' Interchange Control Number (I12)
ElseIf sSegmentID = "GS" Then
'insert a record into FunctionGrop table
sSql = "INSERT INTO [FunctionalGroup] ( InterchangeKey, GS01_FunctionalIdentifierCode, GS02_ApplicationSendersCode, " _
& "GS03_ApplicationReceiversCode, GS04_Date, GS05_Time, GS06_GroupControlNumber, GS07_ResponsibleAgencyCode, " _
& "GS08_VersionReleaseIndustryIdentifierCode) " _
& "values " _
& "(@InterchangeKey, @GS01_FunctionalIdentifierCode, @GS02_ApplicationSendersCode, " _
& "@GS03_ApplicationReceiversCode, @GS04_Date, @GS05_Time, @GS06_GroupControlNumber, @GS07_ResponsibleAgencyCode, " _
& "@GS08_VersionReleaseIndustryIdentifierCode); " _
& "SELECT scope_identity()"
oDaFuncGroup.InsertCommand = New SqlCommand(sSql, oConnection)
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@InterchangeKey", nInterchangeKey)
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS01_FunctionalIdentifierCode", oSegment.DataElementValue(1, 0)) ' Functional Identifier Code (479)
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS02_ApplicationSendersCode", oSegment.DataElementValue(2, 0)) ' Application Sender's Code (142)
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS03_ApplicationReceiversCode", oSegment.DataElementValue(3, 0)) ' Application Receiver's Code (124)
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS04_Date", oSegment.DataElementValue(4, 0)) ' Date (373)
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS05_Time", oSegment.DataElementValue(5, 0)) ' Time (337)
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS06_GroupControlNumber", oSegment.DataElementValue(6, 0)) ' Group Control Number (28)
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS07_ResponsibleAgencyCode", oSegment.DataElementValue(7, 0)) ' Responsible Agency Code (455)
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS08_VersionReleaseIndustryIdentifierCode", oSegment.DataElementValue(8, 0)) ' Version / Release / Industry Identifier Code (480)
nGroupKey = oDaFuncGroup.InsertCommand.ExecuteScalar()
ElseIf sSegmentID = "GE" Then
sValue = oSegment.DataElementValue(1) ' Number of Transaction Sets Included (97)
sValue = oSegment.DataElementValue(2) ' Group Control Number (28)
End If ' sSegmentID = "GE"
ElseIf nArea = 1 Then
' Data segments in area 1 are processed here.
If sLoopSection = "" Then
If sSegmentID = "ST" Then ' Transaction Set Header
sSql = "INSERT INTO [TS810_Header] (GroupKey, ST01_TransactionSetIdentifierCode, ST02_TransactionSetControlNumber, " _
& "BIG01_InvoiceDate, BIG02_InvoiceNumber, BIG03_PurchaseOrderDate, BIG04_PurchaseOrderNumber, BIG07_TransactionTypeCode, " _
& "N102_BillToName, N104_BillToIdentificationCode, N301_BillToAddressInformation, N401_BillToCityName, N402_BillToStateOrProvinceCode, N403_BillToPostalCode, " _
& "N102_RemitToName, N104_RemitToIdentificationCode, N301_RemitToAddressInformation, N401_RemitToCityName, N402_RemitToStateOrProvinceCode, N403_RemitToPostalCode, " _
& "N102_ShipToName, N104_ShipToIdentificationCode, N301_ShipToAddressInformation, N401_ShipToCityName, N402_ShipToStateOrProvinceCode, N403_ShipToPostalCode, " _
& "N102_ShipFromName, N104_ShipFromIdentificationCode, N301_ShipFromAddressInformation, N401_ShipFromCityName, N402_ShipFromStateOrProvinceCode, N403_ShipFromPostalCode, " _
& "N102_VendorName, N104_VendorIdentificationCode, N301_VendorAddressInformation, N401_VendorCityName, N402_VendorStateOrProvinceCode, N403_VendorPostalCode, " _
& "REF02_StoreNumber, REF02_VendorOrderNumber, " _
& "ITD01_TermsTypeCode, ITD02_TermsBasisDateCode, ITD03_TermsDiscountPercent, ITD05_TermsDiscountDaysDue, ITD07_TermsNetDays, ITD12_Description, " _
& "DTM02_ShippedDate, FOB01_ShipmentMethodOfPayment, TDS01_TotalAmount, CAD05_CarrierRouting, " _
& "SAC01_AllowanceOrChargeIndicator, SAC02_ServicePromotionAllowanceOrChargeCode, SAC05_Amount, SAC12_AllowanceOrChargeMethodOfHandlingCode, SAC15_Description, " _
& "ISS01_NumberOfUnitsShipped, ISS02_UnitOrBasisForMeasurementCode) " _
& "values " _
& "(@GroupKey, @ST01_TransactionSetIdentifierCode, @ST02_TransactionSetControlNumber, " _
& "@BIG01_InvoiceDate, @BIG02_InvoiceNumber, @BIG03_PurchaseOrderDate, @BIG04_PurchaseOrderNumber, @BIG07_TransactionTypeCode, " _
& "@N102_BillToName, @N104_BillToIdentificationCode, @N301_BillToAddressInformation, @N401_BillToCityName, @N402_BillToStateOrProvinceCode, @N403_BillToPostalCode, " _
& "@N102_RemitToName, @N104_RemitToIdentificationCode, @N301_RemitToAddressInformation, @N401_RemitToCityName, @N402_RemitToStateOrProvinceCode, @N403_RemitToPostalCode, " _
& "@N102_ShipToName, @N104_ShipToIdentificationCode, @N301_ShipToAddressInformation, @N401_ShipToCityName, @N402_ShipToStateOrProvinceCode, @N403_ShipToPostalCode, " _
& "@N102_ShipFromName, @N104_ShipFromIdentificationCode, @N301_ShipFromAddressInformation, @N401_ShipFromCityName, @N402_ShipFromStateOrProvinceCode, @N403_ShipFromPostalCode, " _
& "@N102_VendorName, @N104_VendorIdentificationCode, @N301_VendorAddressInformation, @N401_VendorCityName, @N402_VendorStateOrProvinceCode, @N403_VendorPostalCode, " _
& "@REF02_StoreNumber, @REF02_VendorOrderNumber, " _
& "@ITD01_TermsTypeCode, @ITD02_TermsBasisDateCode, @ITD03_TermsDiscountPercent, @ITD05_TermsDiscountDaysDue, @ITD07_TermsNetDays, @ITD12_Description, " _
& "@DTM02_ShippedDate, @FOB01_ShipmentMethodOfPayment, @TDS01_TotalAmount, @CAD05_CarrierRouting, " _
& "@SAC01_AllowanceOrChargeIndicator, @SAC02_ServicePromotionAllowanceOrChargeCode, @SAC05_Amount, @SAC12_AllowanceOrChargeMethodOfHandlingCode, @SAC15_Description, " _
& "@ISS01_NumberOfUnitsShipped, @ISS02_UnitOrBasisForMeasurementCode); " _
& "SELECT scope_identity()"
oDaHeader.InsertCommand = New SqlCommand(sSql, oConnection)
oDaHeader.InsertCommand.Parameters.AddWithValue("@GroupKey", nGroupKey)
oDaHeader.InsertCommand.Parameters.AddWithValue("@ST01_TransactionSetIdentifierCode", oSegment.DataElementValue(1, 0)) ' Transaction Set Identifier Code (143)
oDaHeader.InsertCommand.Parameters.AddWithValue("@ST02_TransactionSetControlNumber", oSegment.DataElementValue(2, 0)) ' Transaction Set Control Number (329)
oDaHeader.InsertCommand.Parameters.AddWithValue("@BIG01_InvoiceDate", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@BIG02_InvoiceNumber", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@BIG03_PurchaseOrderDate", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@BIG04_PurchaseOrderNumber", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@BIG07_TransactionTypeCode", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N102_BillToName", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N104_BillToIdentificationCode", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N301_BillToAddressInformation", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N401_BillToCityName", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N402_BillToStateOrProvinceCode", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N403_BillToPostalCode", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N102_RemitToName", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N104_RemitToIdentificationCode", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N301_RemitToAddressInformation", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N401_RemitToCityName", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N402_RemitToStateOrProvinceCode", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N403_RemitToPostalCode", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N102_ShipToName", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N104_ShipToIdentificationCode", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N301_ShipToAddressInformation", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N401_ShipToCityName", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N402_ShipToStateOrProvinceCode", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N403_ShipToPostalCode", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N102_ShipFromName", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N104_ShipFromIdentificationCode", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N301_ShipFromAddressInformation", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N401_ShipFromCityName", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N402_ShipFromStateOrProvinceCode", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N403_ShipFromPostalCode", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N102_VendorName", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N104_VendorIdentificationCode", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N301_VendorAddressInformation", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N401_VendorCityName", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N402_VendorStateOrProvinceCode", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@N403_VendorPostalCode", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@REF02_StoreNumber", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@REF02_VendorOrderNumber", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@ITD01_TermsTypeCode", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@ITD02_TermsBasisDateCode", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@ITD03_TermsDiscountPercent", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@ITD05_TermsDiscountDaysDue", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@ITD07_TermsNetDays", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@ITD12_Description", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@DTM02_ShippedDate", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@FOB01_ShipmentMethodOfPayment", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@TDS01_TotalAmount", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@CAD05_CarrierRouting", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@SAC01_AllowanceOrChargeIndicator", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@SAC02_ServicePromotionAllowanceOrChargeCode", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@SAC05_Amount", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@SAC12_AllowanceOrChargeMethodOfHandlingCode", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@SAC15_Description", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@ISS01_NumberOfUnitsShipped", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@ISS02_UnitOrBasisForMeasurementCode", "")
nHeaderKey = oDaHeader.InsertCommand.ExecuteScalar()
ElseIf sSegmentID = "BIG" Then ' Beginning Segment for Invoice
sSql = "UPDATE [TS810_Header] SET BIG01_InvoiceDate = @BIG01_InvoiceDate, BIG02_InvoiceNumber = @BIG02_InvoiceNumber, " _
& "BIG03_PurchaseOrderDate = @BIG03_PurchaseOrderDate, BIG04_PurchaseOrderNumber = @BIG04_PurchaseOrderNumber, " _
& "BIG07_TransactionTypeCode = @BIG07_TransactionTypeCode " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@BIG01_InvoiceDate", oSegment.DataElementValue(1)) ' Date (373)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@BIG02_InvoiceNumber", oSegment.DataElementValue(2)) ' Invoice Number (76)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@BIG03_PurchaseOrderDate", oSegment.DataElementValue(3)) ' Date (373)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@BIG04_PurchaseOrderNumber", oSegment.DataElementValue(4)) ' Purchase Order Number (324)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@BIG07_TransactionTypeCode", oSegment.DataElementValue(7)) ' Transaction Type Code (640)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "ITD" Then ' Terms of Sale/Deferred Terms of Sale
sSql = "UPDATE [TS810_Header] SET ITD01_TermsTypeCode = @ITD01_TermsTypeCode, ITD02_TermsBasisDateCode = @ITD02_TermsBasisDateCode, " _
& "ITD03_TermsDiscountPercent = @ITD03_TermsDiscountPercent, ITD05_TermsDiscountDaysDue = @ITD05_TermsDiscountDaysDue, " _
& "ITD07_TermsNetDays = @ITD07_TermsNetDays, ITD12_Description = @ITD12_Description " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@ITD01_TermsTypeCode", oSegment.DataElementValue(1)) ' Terms Type Code (336)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@ITD02_TermsBasisDateCode", oSegment.DataElementValue(2)) ' Terms Basis Date Code (333)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@ITD03_TermsDiscountPercent", oSegment.DataElementValue(3)) ' Terms Discount Percent (338)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@ITD05_TermsDiscountDaysDue", oSegment.DataElementValue(5)) ' Terms Discount Days Due (351)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@ITD07_TermsNetDays", oSegment.DataElementValue(7)) ' Terms Net Days (386)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@ITD12_Description", oSegment.DataElementValue(12)) ' Description (352)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "DTM" Then ' Date/Time Reference
sSql = "UPDATE [TS810_Header] SET DTM02_ShippedDate = @DTM02_ShippedDate " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@DTM02_ShippedDate", oSegment.DataElementValue(2)) ' Date (373)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "FOB" Then ' F.O.B. Related Instructions
sSql = "UPDATE [TS810_Header] SET FOB01_ShipmentMethodOfPayment = @FOB01_ShipmentMethodOfPayment " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@FOB01_ShipmentMethodOfPayment", oSegment.DataElementValue(1)) ' Shipment Method of Payment (146)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
End If ' sSegmentID
ElseIf sLoopSection = "N1" Then
If sSegmentID = "N1" Then
sN1Entity = oSegment.DataElementValue(1) ' Entity Identifier Code (98)
End If
If sN1Entity = "BT" Then 'BILL TO
If sSegmentID = "N1" Then ' Name
sSql = "UPDATE [TS810_Header] SET N102_BillToName = @N102_BillToName, N104_BillToIdentificationCode = @N104_BillToIdentificationCode " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N102_BillToName", oSegment.DataElementValue(2)) ' Name (93)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N104_BillToIdentificationCode", oSegment.DataElementValue(4)) ' Identification Code (67)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "N3" Then ' Address Information
sSql = "UPDATE [TS810_Header] SET N301_BillToAddressInformation = @N301_BillToAddressInformation " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N301_BillToAddressInformation", oSegment.DataElementValue(1)) ' Address Information (166)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "N4" Then ' Geographic Location
sSql = "UPDATE [TS810_Header] SET N401_BillToCityName = @N401_BillToCityName, N402_BillToStateOrProvinceCode = @N402_BillToStateOrProvinceCode, " _
& "N403_BillToPostalCode = @N403_BillToPostalCode " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N401_BillToCityName", oSegment.DataElementValue(1)) ' City Name (19)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N402_BillToStateOrProvinceCode", oSegment.DataElementValue(2)) ' State or Province Code (156)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N403_BillToPostalCode", oSegment.DataElementValue(3)) ' Postal Code (116)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "REF" Then ' Reference Identification
sQlfr = oSegment.DataElementValue(1)
If sQlfr = "ST" Then
sSql = "UPDATE [TS810_Header] SET REF02_StoreNumber = @REF02_StoreNumber " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@REF02_StoreNumber", oSegment.DataElementValue(2)) ' Reference Identification (127)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sQlfr = "VN" Then
sSql = "UPDATE [TS810_Header] SET REF02_VendorOrderNumber = @REF02_VendorOrderNumber " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@REF02_VendorOrderNumber", oSegment.DataElementValue(2)) ' Reference Identification (127)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
End If
End If ' sSegmentID
ElseIf sN1Entity = "RE" Then 'REMIT TO
If sSegmentID = "N1" Then ' Name
sSql = "UPDATE [TS810_Header] SET N102_RemitToName = @N102_RemitToName, N104_RemitToIdentificationCode = @N104_RemitToIdentificationCode " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N102_RemitToName", oSegment.DataElementValue(2)) ' Name (93)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N104_RemitToIdentificationCode", oSegment.DataElementValue(4)) ' Identification Code (67)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "N3" Then ' Address Information
sSql = "UPDATE [TS810_Header] SET N301_RemitToAddressInformation = @N301_RemitToAddressInformation " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N301_RemitToAddressInformation", oSegment.DataElementValue(1)) ' Address Information (166)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "N4" Then ' Geographic Location
sSql = "UPDATE [TS810_Header] SET N401_RemitToCityName = @N401_RemitToCityName, N402_RemitToStateOrProvinceCode = @N402_RemitToStateOrProvinceCode, " _
& "N403_RemitToPostalCode = @N403_RemitToPostalCode " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N401_RemitToCityName", oSegment.DataElementValue(1)) ' City Name (19)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N402_RemitToStateOrProvinceCode", oSegment.DataElementValue(2)) ' State or Province Code (156)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N403_RemitToPostalCode", oSegment.DataElementValue(3)) ' Postal Code (116)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "REF" Then ' Reference Identification
sQlfr = oSegment.DataElementValue(1)
If sQlfr = "ST" Then
sSql = "UPDATE [TS810_Header] SET REF02_StoreNumber = @REF02_StoreNumber " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@REF02_StoreNumber", oSegment.DataElementValue(2)) ' Reference Identification (127)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sQlfr = "VN" Then
sSql = "UPDATE [TS810_Header] SET REF02_VendorOrderNumber = @REF02_VendorOrderNumber " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@REF02_VendorOrderNumber", oSegment.DataElementValue(2)) ' Reference Identification (127)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
End If
End If ' sSegmentID
ElseIf sN1Entity = "SF" Then 'SHIP FROM
If sSegmentID = "N1" Then ' Name
sSql = "UPDATE [TS810_Header] SET N102_ShipFromName = @N102_ShipFromName, N104_ShipFromIdentificationCode = @N104_ShipFromIdentificationCode " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N102_ShipFromName", oSegment.DataElementValue(2)) ' Name (93)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N104_ShipFromIdentificationCode", oSegment.DataElementValue(4)) ' Identification Code (67)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "N3" Then ' Address Information
sSql = "UPDATE [TS810_Header] SET N301_ShipFromAddressInformation = @N301_ShipFromAddressInformation " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N301_ShipFromAddressInformation", oSegment.DataElementValue(1)) ' Address Information (166)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "N4" Then ' Geographic Location
sSql = "UPDATE [TS810_Header] SET N401_ShipFromCityName = @N401_ShipFromCityName, N402_ShipFromStateOrProvinceCode = @N402_ShipFromStateOrProvinceCode, " _
& "N403_ShipFromPostalCode = @N403_ShipFromPostalCode " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N401_ShipFromCityName", oSegment.DataElementValue(1)) ' City Name (19)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N402_ShipFromStateOrProvinceCode", oSegment.DataElementValue(2)) ' State or Province Code (156)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N403_ShipFromPostalCode", oSegment.DataElementValue(3)) ' Postal Code (116)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "REF" Then ' Reference Identification
sQlfr = oSegment.DataElementValue(1)
If sQlfr = "ST" Then
sSql = "UPDATE [TS810_Header] SET REF02_StoreNumber = @REF02_StoreNumber " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@REF02_StoreNumber", oSegment.DataElementValue(2)) ' Reference Identification (127)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sQlfr = "VN" Then
sSql = "UPDATE [TS810_Header] SET REF02_VendorOrderNumber = @REF02_VendorOrderNumber " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@REF02_VendorOrderNumber", oSegment.DataElementValue(2)) ' Reference Identification (127)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
End If
End If ' sSegmentID
ElseIf sN1Entity = "ST" Then 'SHIP TO
If sSegmentID = "N1" Then ' Name
sSql = "UPDATE [TS810_Header] SET N102_ShipToName = @N102_ShipToName, N104_ShipToIdentificationCode = @N104_ShipToIdentificationCode " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N102_ShipToName", oSegment.DataElementValue(2)) ' Name (93)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N104_ShipToIdentificationCode", oSegment.DataElementValue(4)) ' Identification Code (67)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "N3" Then ' Address Information
sSql = "UPDATE [TS810_Header] SET N301_ShipToAddressInformation = @N301_ShipToAddressInformation " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N301_ShipToAddressInformation", oSegment.DataElementValue(1)) ' Address Information (166)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "N4" Then ' Geographic Location
sSql = "UPDATE [TS810_Header] SET N401_ShipToCityName = @N401_ShipToCityName, N402_ShipToStateOrProvinceCode = @N402_ShipToStateOrProvinceCode, " _
& "N403_ShipToPostalCode = @N403_ShipToPostalCode " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N401_ShipToCityName", oSegment.DataElementValue(1)) ' City Name (19)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N402_ShipToStateOrProvinceCode", oSegment.DataElementValue(2)) ' State or Province Code (156)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N403_ShipToPostalCode", oSegment.DataElementValue(3)) ' Postal Code (116)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "REF" Then ' Reference Identification
sQlfr = oSegment.DataElementValue(1)
If sQlfr = "ST" Then
sSql = "UPDATE [TS810_Header] SET REF02_StoreNumber = @REF02_StoreNumber " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@REF02_StoreNumber", oSegment.DataElementValue(2)) ' Reference Identification (127)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sQlfr = "VN" Then
sSql = "UPDATE [TS810_Header] SET REF02_VendorOrderNumber = @REF02_VendorOrderNumber " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@REF02_VendorOrderNumber", oSegment.DataElementValue(2)) ' Reference Identification (127)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
End If
End If ' sSegmentID
ElseIf sN1Entity = "VN" Then 'VENDOR
If sSegmentID = "N1" Then ' Name
sSql = "UPDATE [TS810_Header] SET N102_VendorName = @N102_VendorName, N104_VendorIdentificationCode = @N104_VendorIdentificationCode " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N102_VendorName", oSegment.DataElementValue(2)) ' Name (93)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N104_VendorIdentificationCode", oSegment.DataElementValue(4)) ' Identification Code (67)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "N3" Then ' Address Information
sSql = "UPDATE [TS810_Header] SET N301_VendorAddressInformation = @N301_VendorAddressInformation " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N301_VendorAddressInformation", oSegment.DataElementValue(1)) ' Address Information (166)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "N4" Then ' Geographic Location
sSql = "UPDATE [TS810_Header] SET N401_VendorCityName = @N401_VendorCityName, N402_VendorStateOrProvinceCode = @N402_VendorStateOrProvinceCode, " _
& "N403_VendorPostalCode = @N403_VendorPostalCode " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N401_VendorCityName", oSegment.DataElementValue(1)) ' City Name (19)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N402_VendorStateOrProvinceCode", oSegment.DataElementValue(2)) ' State or Province Code (156)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N403_VendorPostalCode", oSegment.DataElementValue(3)) ' Postal Code (116)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "REF" Then ' Reference Identification
sQlfr = oSegment.DataElementValue(1)
If sQlfr = "ST" Then
sSql = "UPDATE [TS810_Header] SET REF02_StoreNumber = @REF02_StoreNumber " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@REF02_StoreNumber", oSegment.DataElementValue(2)) ' Reference Identification (127)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sQlfr = "VN" Then
sSql = "UPDATE [TS810_Header] SET REF02_VendorOrderNumber = @REF02_VendorOrderNumber " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@REF02_VendorOrderNumber", oSegment.DataElementValue(2)) ' Reference Identification (127)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
End If
End If ' sSegmentID
End If
End If ' sLoopSection
ElseIf nArea = 2 Then
' Data segments in area 2 are processed here.
If sLoopSection = "" Then
' Nothing to do.
ElseIf sLoopSection = "IT1" Then
If sSegmentID = "IT1" Then ' Baseline Item Data (Invoice)
sSql = "INSERT INTO [TS810_Detail] (HeaderKey, IT102_QuantityInvoiced, IT103_UnitOrBasisForMeasurementCode, " _
& "IT104_UnitPrice, IT106_ProductServiceIdQualifier, IT107_ProductServiceId, PID05_Description, " _
& "SAC01_AllowanceOrChargeIndicator, SAC02_ServicePromotionAllowanceOrChargeCode, SAC05_Amount, " _
& "SAC12_AllowanceOrChargeMethodOfHandlingCode, SAC15_Description) " _
& "values " _
& "(@HeaderKey, @IT102_QuantityInvoiced, @IT103_UnitOrBasisForMeasurementCode, " _
& "@IT104_UnitPrice, @IT106_ProductServiceIdQualifier, @IT107_ProductServiceId, @PID05_Description, " _
& "@SAC01_AllowanceOrChargeIndicator, @SAC02_ServicePromotionAllowanceOrChargeCode, @SAC05_Amount, " _
& "@SAC12_AllowanceOrChargeMethodOfHandlingCode, @SAC15_Description); " _
& "SELECT scope_identity()"
oDaDetail.InsertCommand = New SqlCommand(sSql, oConnection)
oDaDetail.InsertCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaDetail.InsertCommand.Parameters.AddWithValue("@IT102_QuantityInvoiced", oSegment.DataElementValue(2)) ' Quantity Invoiced (358)
oDaDetail.InsertCommand.Parameters.AddWithValue("@IT103_UnitOrBasisForMeasurementCode", oSegment.DataElementValue(3)) ' Unit or Basis for Measurement Code (355)
oDaDetail.InsertCommand.Parameters.AddWithValue("@IT104_UnitPrice", oSegment.DataElementValue(4)) ' Unit Price (212)
oDaDetail.InsertCommand.Parameters.AddWithValue("@IT106_ProductServiceIdQualifier", oSegment.DataElementValue(6)) ' Product/Service ID Qualifier (235)
oDaDetail.InsertCommand.Parameters.AddWithValue("@IT107_ProductServiceId", oSegment.DataElementValue(7)) ' Product/Service ID (234)
oDaDetail.InsertCommand.Parameters.AddWithValue("@PID05_Description", "")
oDaDetail.InsertCommand.Parameters.AddWithValue("@SAC01_AllowanceOrChargeIndicator", "")
oDaDetail.InsertCommand.Parameters.AddWithValue("@SAC02_ServicePromotionAllowanceOrChargeCode", "")
oDaDetail.InsertCommand.Parameters.AddWithValue("@SAC05_Amount", "")
oDaDetail.InsertCommand.Parameters.AddWithValue("@SAC12_AllowanceOrChargeMethodOfHandlingCode", "")
oDaDetail.InsertCommand.Parameters.AddWithValue("@SAC15_Description", "")
nDetailKey = oDaDetail.InsertCommand.ExecuteScalar()
End If ' sSegmentID
ElseIf sLoopSection = "IT1;PID" Then
If sSegmentID = "PID" Then ' Product/Item Description
sSql = "UPDATE [TS810_Detail] SET PID05_Description = @PID05_Description " _
& "where DetailKey = @DetailKey"
oDaDetail.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@PID05_Description", oSegment.DataElementValue(5)) ' Description (352)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@DetailKey", nDetailKey)
oDaDetail.UpdateCommand.ExecuteNonQuery()
End If ' sSegmentID
ElseIf sLoopSection = "IT1;SAC" Then
If sSegmentID = "SAC" Then ' Service, Promotion, Allowance, or Charge Information
sSql = "UPDATE [TS810_Detail] SET SAC01_AllowanceOrChargeIndicator = @SAC01_AllowanceOrChargeIndicator, " _
& "SAC02_ServicePromotionAllowanceOrChargeCode = @SAC02_ServicePromotionAllowanceOrChargeCode, SAC05_Amount = @SAC05_Amount, " _
& "SAC12_AllowanceOrChargeMethodOfHandlingCode = @SAC12_AllowanceOrChargeMethodOfHandlingCode, SAC15_Description = @SAC15_Description " _
& "where DetailKey = @DetailKey"
oDaDetail.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SAC01_AllowanceOrChargeIndicator", oSegment.DataElementValue(1)) ' Allowance or Charge Indicator (248)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SAC02_ServicePromotionAllowanceOrChargeCode", oSegment.DataElementValue(2)) ' Service, Promotion, Allowance, or Charge Code (1300)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SAC05_Amount", oSegment.DataElementValue(5)) ' Amount (610)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SAC12_AllowanceOrChargeMethodOfHandlingCode", oSegment.DataElementValue(12)) ' Allowance or Charge Method of Handling Code (331)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SAC15_Description", oSegment.DataElementValue(15)) ' Description (352)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@DetailKey", nDetailKey)
oDaDetail.UpdateCommand.ExecuteNonQuery()
End If ' sSegmentID
End If ' sLoopSection = "IT1;SAC"
ElseIf nArea = 3 Then
' Data segments in area 3 are processed here.
If sLoopSection = "" Then
If sSegmentID = "TDS" Then ' Total Monetary Value Summary
sSql = "UPDATE [TS810_Header] SET TDS01_TotalAmount = @TDS01_TotalAmount " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@TDS01_TotalAmount", oSegment.DataElementValue(1)) ' Amount (610)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "CAD" Then ' Carrier Detail
sSql = "UPDATE [TS810_Header] SET CAD05_CarrierRouting = @CAD05_CarrierRouting " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@CAD05_CarrierRouting", oSegment.DataElementValue(5)) ' Transportation Method/Type Code (91)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "SE" Then ' Transaction Set Trailer
sValue = oSegment.DataElementValue(1) ' Number of Included Segments (96)
sValue = oSegment.DataElementValue(2) ' Transaction Set Control Number (329)
End If ' sSegmentID
ElseIf sLoopSection = "SAC" Then
If sSegmentID = "SAC" Then ' Service, Promotion, Allowance, or Charge Information
sSql = "UPDATE [TS810_Header] SET SAC01_AllowanceOrChargeIndicator = @SAC01_AllowanceOrChargeIndicator, " _
& "SAC02_ServicePromotionAllowanceOrChargeCode = @SAC02_ServicePromotionAllowanceOrChargeCode, SAC05_Amount = @SAC05_Amount, " _
& "SAC12_AllowanceOrChargeMethodOfHandlingCode = @SAC12_AllowanceOrChargeMethodOfHandlingCode, SAC15_Description = @SAC15_Description " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@SAC01_AllowanceOrChargeIndicator", oSegment.DataElementValue(1)) ' Allowance or Charge Indicator (248)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@SAC02_ServicePromotionAllowanceOrChargeCode", oSegment.DataElementValue(2)) ' Service, Promotion, Allowance, or Charge Code (1300)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@SAC05_Amount", oSegment.DataElementValue(5)) ' Amount (610)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@SAC12_AllowanceOrChargeMethodOfHandlingCode", oSegment.DataElementValue(12)) ' Allowance or Charge Method of Handling Code (331)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@SAC15_Description", oSegment.DataElementValue(15)) ' Description (352)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
End If ' sSegmentID
ElseIf sLoopSection = "ISS" Then
If sSegmentID = "ISS" Then ' Invoice Shipment Summary
sSql = "UPDATE [TS810_Header] SET ISS01_NumberOfUnitsShipped = @ISS01_NumberOfUnitsShipped, " _
& "ISS02_UnitOrBasisForMeasurementCode = @ISS02_UnitOrBasisForMeasurementCode " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@ISS01_NumberOfUnitsShipped", oSegment.DataElementValue(1)) ' Number of Units Shipped (382)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@ISS02_UnitOrBasisForMeasurementCode", oSegment.DataElementValue(2)) ' Unit or Basis for Measurement Code (355)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
End If ' sSegmentID
End If ' sLoopSection
End If ' nArea
' Get the next data segment in the document.
ediDataSegment.Set(oSegment, oSegment.Next)
Loop ' Not oSegment Is Nothing
oDaDetail.Dispose()
oDaHeader.Dispose()
oDaFuncGroup.Dispose()
oDaInterchange.Dispose()
oConnection.Close()
oConnection.Dispose()
End Sub
