Private Sub Proc_Translate_Orders_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 sNadQlfr 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 Message.
If sSegmentID = "UNB" Then
'insert a record into interchange table
sSql = "INSERT INTO [Interchange] (UNB01_01_SyntaxIdentifier, UNB01_02_SyntaxVersionNumber, UNB02_01_InterchangeSenderIdentification, " _
& "UNB02_02_PartnerIdentificationCodeQualifier, UNB02_03_InterchangeSenderInternalIdentification, UNB03_01_RecipientIdentification, " _
& "UNB03_02_PartnerIdentificationCodeQualifier, UNB03_03_RoutingAddress, UNB04_01_Date, UNB04_02_Time, " _
& "UNB05_InterchangeControlReference, UNB09_AcknowledgementRequest, UNB11_TestIndicator) " _
& "values " _
& "(@UNB01_01_SyntaxIdentifier, @UNB01_02_SyntaxVersionNumber, @UNB02_01_InterchangeSenderIdentification, " _
& "@UNB02_02_PartnerIdentificationCodeQualifier, @UNB02_03_InterchangeSenderInternalIdentification, @UNB03_01_RecipientIdentification, " _
& "@UNB03_02_PartnerIdentificationCodeQualifier, @UNB03_03_RoutingAddress, @UNB04_01_Date, @UNB04_02_Time, " _
& "@UNB05_InterchangeControlReference, @UNB09_AcknowledgementRequest, @UNB11_TestIndicator); " _
& "SELECT scope_identity()"
oDaInterchange.InsertCommand = New SqlCommand(sSql, oConnection)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@UNB01_01_SyntaxIdentifier", oSegment.DataElementValue(1, 1)) ' Syntax identifier (0001)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@UNB01_02_SyntaxVersionNumber", oSegment.DataElementValue(1, 2)) ' Syntax version number (0002)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@UNB02_01_InterchangeSenderIdentification", oSegment.DataElementValue(2, 1)) ' Interchange sender identification (0004)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@UNB02_02_PartnerIdentificationCodeQualifier", oSegment.DataElementValue(2, 2)) ' Partner identification code qualifier (0007)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@UNB02_03_InterchangeSenderInternalIdentification", oSegment.DataElementValue(2, 3)) ' Interchange sender internal identification (0008)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@UNB03_01_RecipientIdentification", oSegment.DataElementValue(3, 1)) ' Recipient identification (0010)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@UNB03_02_PartnerIdentificationCodeQualifier", oSegment.DataElementValue(3, 2)) ' Partner identification code qualifier (0007)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@UNB03_03_RoutingAddress", oSegment.DataElementValue(3, 3)) ' Routing address (0014)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@UNB04_01_Date", oSegment.DataElementValue(4, 1)) ' Date (0017)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@UNB04_02_Time", oSegment.DataElementValue(4, 2)) ' Time (0019)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@UNB05_InterchangeControlReference", oSegment.DataElementValue(5)) ' Interchange control reference (0020)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@UNB09_AcknowledgementRequest", oSegment.DataElementValue(9)) ' Acknowledgement request (0031)
oDaInterchange.InsertCommand.Parameters.AddWithValue("@UNB11_TestIndicator", oSegment.DataElementValue(11)) ' Test indicator (0035)
nInterchangeKey = oDaInterchange.InsertCommand.ExecuteScalar()
ElseIf sSegmentID = "UNZ" Then
sValue = oSegment.DataElementValue(1) ' Interchange control count (0036)
sValue = oSegment.DataElementValue(2) ' Interchange control reference (0020)
ElseIf sSegmentID = "UNG" Then
ElseIf sSegmentID = "UNE" Then
sValue = oSegment.DataElementValue(1) ' Group control count (0060)
sValue = oSegment.DataElementValue(2) ' Group reference number (0048)
End If ' sSegmentID = "UNE"
ElseIf nArea = 1 Then
' Data segments in area 1 are processed here.
If sLoopSection = "" Then
If sSegmentID = "UNH" Then ' Message header
sSql = "INSERT INTO [Orders_Header] (InterchangeKey, UNH01_MessageReferenceNumber, UNH02_01_MessageTypeIdentifier, UNH02_02_MessageVersionNumber, " _
& "UNH02_03_MessageReleaseNumber, UNH02_04_ControllingAgency, UNH02_05_AssociationAssignedCode, BGM01_01_DocumentMessageNameCoded, " _
& "BGM02_01_DocumentMessageNumber, BGM03_MessageFunctionCoded, DTM01_02_DeliveryDateTime, DTM01_02_DocumentDateTime, " _
& "DTM01_02_CollectionDateTime, FTX01_TextSubjectQualifier, FTX02_TextFunctionCoded, FTX04_01_FreeText, RFF01_02_DeliveryScheduleNumber, " _
& "NAD02_01_BuyerIdentification, NAD02_03_BuyerAgencyCoded, LOC02_01_BuyerLocationIdentification, CTA02_01_BuyerPurchasingContactId, " _
& "CTA02_02_BuyerPurchasingContactName, COM01_01_BuyerEmail, COM01_01_BuyerFax, COM01_01_BuyerTelephone, " _
& "NAD02_01_ShipToIdentification, NAD02_03_ShipToAgencyCoded, LOC02_01_ShipToLocationIdentification, CTA02_01_ShipToPurchasingContactId, " _
& "CTA02_02_ShipToPurchasingContactName, COM01_01_ShipToEmail, COM01_01_ShipToFax, COM01_01_ShipToTelephone, " _
& "NAD02_01_SupplierIdentification, NAD02_03_SupplierAgencyCoded, LOC02_01_SupplierLocationIdentification, CTA02_01_SupplierPurchasingContactId, " _
& "CTA02_02_SupplierPurchasingContactName, COM01_01_SupplierEmail, COM01_01_SupplierFax, COM01_01_SupplierTelephone, " _
& "MOA01_02_TotalMonetaryAmount, CNT01_01_ControlQualifier, CNT01_02_ControlValue) " _
& "values " _
& "(@InterchangeKey, @UNH01_MessageReferenceNumber, @UNH02_01_MessageTypeIdentifier, @UNH02_02_MessageVersionNumber, " _
& "@UNH02_03_MessageReleaseNumber, @UNH02_04_ControllingAgency, @UNH02_05_AssociationAssignedCode, @BGM01_01_DocumentMessageNameCoded, " _
& "@BGM02_01_DocumentMessageNumber, @BGM03_MessageFunctionCoded, @DTM01_02_DeliveryDateTime, @DTM01_02_DocumentDateTime, " _
& "@DTM01_02_CollectionDateTime, @FTX01_TextSubjectQualifier, @FTX02_TextFunctionCoded, @FTX04_01_FreeText, @RFF01_02_DeliveryScheduleNumber, " _
& "@NAD02_01_BuyerIdentification, @NAD02_03_BuyerAgencyCoded, @LOC02_01_BuyerLocationIdentification, @CTA02_01_BuyerPurchasingContactId, " _
& "@CTA02_02_BuyerPurchasingContactName, @COM01_01_BuyerEmail, @COM01_01_BuyerFax, @COM01_01_BuyerTelephone, " _
& "@NAD02_01_ShipToIdentification, @NAD02_03_ShipToAgencyCoded, @LOC02_01_ShipToLocationIdentification, @CTA02_01_ShipToPurchasingContactId, " _
& "@CTA02_02_ShipToPurchasingContactName, @COM01_01_ShipToEmail, @COM01_01_ShipToFax, @COM01_01_ShipToTelephone, " _
& "@NAD02_01_SupplierIdentification, @NAD02_03_SupplierAgencyCoded, @LOC02_01_SupplierLocationIdentification, @CTA02_01_SupplierPurchasingContactId, " _
& "@CTA02_02_SupplierPurchasingContactName, @COM01_01_SupplierEmail, @COM01_01_SupplierFax, @COM01_01_SupplierTelephone, " _
& "@MOA01_02_TotalMonetaryAmount, @CNT01_01_ControlQualifier, @CNT01_02_ControlValue); " _
& "SELECT scope_identity()"
oDaHeader.InsertCommand = New SqlCommand(sSql, oConnection)
oDaHeader.InsertCommand.Parameters.AddWithValue("@InterchangeKey", nInterchangeKey)
oDaHeader.InsertCommand.Parameters.AddWithValue("@UNH01_MessageReferenceNumber", oSegment.DataElementValue(1)) ' Message reference number (0062)
oDaHeader.InsertCommand.Parameters.AddWithValue("@UNH02_01_MessageTypeIdentifier", oSegment.DataElementValue(2, 1)) ' Message type identifier (0065)
oDaHeader.InsertCommand.Parameters.AddWithValue("@UNH02_02_MessageVersionNumber", oSegment.DataElementValue(2, 2)) ' Message version number (0052)
oDaHeader.InsertCommand.Parameters.AddWithValue("@UNH02_03_MessageReleaseNumber", oSegment.DataElementValue(2, 3)) ' Message release number (0054)
oDaHeader.InsertCommand.Parameters.AddWithValue("@UNH02_04_ControllingAgency", oSegment.DataElementValue(2, 4)) ' Controlling agency (0051)
oDaHeader.InsertCommand.Parameters.AddWithValue("@UNH02_05_AssociationAssignedCode", oSegment.DataElementValue(2, 5)) ' Association assigned code (0057)
oDaHeader.InsertCommand.Parameters.AddWithValue("@BGM01_01_DocumentMessageNameCoded", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@BGM02_01_DocumentMessageNumber", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@BGM03_MessageFunctionCoded", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@DTM01_02_DeliveryDateTime", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@DTM01_02_DocumentDateTime", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@DTM01_02_CollectionDateTime", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@FTX01_TextSubjectQualifier", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@FTX02_TextFunctionCoded", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@FTX04_01_FreeText", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@RFF01_02_DeliveryScheduleNumber", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@NAD02_01_BuyerIdentification", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@NAD02_03_BuyerAgencyCoded", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@LOC02_01_BuyerLocationIdentification", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@CTA02_01_BuyerPurchasingContactId", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@CTA02_02_BuyerPurchasingContactName", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@COM01_01_BuyerEmail", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@COM01_01_BuyerFax", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@COM01_01_BuyerTelephone", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@NAD02_01_ShipToIdentification", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@NAD02_03_ShipToAgencyCoded", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@LOC02_01_ShipToLocationIdentification", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@CTA02_01_ShipToPurchasingContactId", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@CTA02_02_ShipToPurchasingContactName", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@COM01_01_ShipToEmail", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@COM01_01_ShipToFax", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@COM01_01_ShipToTelephone", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@NAD02_01_SupplierIdentification", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@NAD02_03_SupplierAgencyCoded", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@LOC02_01_SupplierLocationIdentification", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@CTA02_01_SupplierPurchasingContactId", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@CTA02_02_SupplierPurchasingContactName", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@COM01_01_SupplierEmail", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@COM01_01_SupplierFax", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@COM01_01_SupplierTelephone", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@MOA01_02_TotalMonetaryAmount", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@CNT01_01_ControlQualifier", "")
oDaHeader.InsertCommand.Parameters.AddWithValue("@CNT01_02_ControlValue", "")
nHeaderKey = oDaHeader.InsertCommand.ExecuteScalar()
ElseIf sSegmentID = "BGM" Then ' Beginning of message
sSql = "UPDATE [Orders_Header] SET BGM01_01_DocumentMessageNameCoded = @BGM01_01_DocumentMessageNameCoded, " _
& "BGM02_01_DocumentMessageNumber = @BGM02_01_DocumentMessageNumber, BGM03_MessageFunctionCoded = @BGM03_MessageFunctionCoded " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@BGM01_01_DocumentMessageNameCoded", oSegment.DataElementValue(1, 1)) ' Document/message name, coded (1001)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@BGM02_01_DocumentMessageNumber", oSegment.DataElementValue(2, 1)) ' Document/message number (1004)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@BGM03_MessageFunctionCoded", oSegment.DataElementValue(3)) ' Message function, coded (1225)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "DTM" Then ' Date/time/period
sQlfr = oSegment.DataElementValue(1, 1) ' Date/time/period qualifier (2005)
If sQlfr = "2" Then
sSql = "UPDATE [Orders_Header] SET DTM01_02_DeliveryDateTime = @DTM01_02_DeliveryDateTime " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@DTM01_02_DeliveryDateTime", oSegment.DataElementValue(1, 2)) ' Date/time/period (2380)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sQlfr = "137" Then
sSql = "UPDATE [Orders_Header] SET DTM01_02_DocumentDateTime = @DTM01_02_DocumentDateTime " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@DTM01_02_DocumentDateTime", oSegment.DataElementValue(1, 2)) ' Date/time/period (2380)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sQlfr = "235" Then
sSql = "UPDATE [Orders_Header] SET DTM01_02_CollectionDateTime = @DTM01_02_CollectionDateTime " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@DTM01_02_CollectionDateTime", oSegment.DataElementValue(1, 2)) ' Date/time/period (2380)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
End If
ElseIf sSegmentID = "FTX" Then ' Free text
sSql = "UPDATE [Orders_Header] SET FTX01_TextSubjectQualifier = @FTX01_TextSubjectQualifier, " _
& "FTX02_TextFunctionCoded = @FTX02_TextFunctionCoded, FTX04_01_FreeText = @FTX04_01_FreeText " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@FTX01_TextSubjectQualifier", oSegment.DataElementValue(1)) ' Text subject qualifier (4451)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@FTX02_TextFunctionCoded", oSegment.DataElementValue(2)) ' Text function, coded (4453)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@FTX04_01_FreeText", oSegment.DataElementValue(4, 1)) ' Free text (4440)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
End If ' sSegmentID
ElseIf sLoopSection = "RFF" Then
If sSegmentID = "RFF" Then ' Reference
sSql = "UPDATE [Orders_Header] SET RFF01_02_DeliveryScheduleNumber = @RFF01_02_DeliveryScheduleNumber " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@RFF01_02_DeliveryScheduleNumber", oSegment.DataElementValue(1, 2)) ' Reference number (1154)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
End If ' sSegmentID = "DTM"
ElseIf sLoopSection.StartsWith("NAD") Then
If sSegmentID = "NAD" Then ' Name and address
sNadQlfr = oSegment.DataElementValue(1)
End If
If sNadQlfr = "BY" Then 'BUYER
If sSegmentID = "NAD" Then ' Name and address
sSql = "UPDATE [Orders_Header] SET NAD02_01_BuyerIdentification = @NAD02_01_BuyerIdentification, " _
& "NAD02_03_BuyerAgencyCoded = @NAD02_03_BuyerAgencyCoded " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@NAD02_01_BuyerIdentification", oSegment.DataElementValue(2, 1)) ' Party identification (3039)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@NAD02_03_BuyerAgencyCoded", oSegment.DataElementValue(2, 3)) ' Code list responsible agency, coded (3055)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "LOC" Then ' Place/location identification
sSql = "UPDATE [Orders_Header] SET LOC02_01_BuyerLocationIdentification = @LOC02_01_BuyerLocationIdentification " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@LOC02_01_BuyerLocationIdentification", oSegment.DataElementValue(2, 1)) ' Place/location identification (3225)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
End If ' sSegmentID
If sLoopSection = "NAD;CTA" Then
If sSegmentID = "CTA" Then ' Contact information
sSql = "UPDATE [Orders_Header] SET CTA02_01_BuyerPurchasingContactId = @CTA02_01_BuyerPurchasingContactId, " _
& "CTA02_02_BuyerPurchasingContactName = @CTA02_02_BuyerPurchasingContactName " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@CTA02_01_BuyerPurchasingContactId", oSegment.DataElementValue(2, 1)) ' Department or employee identification (3413)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@CTA02_02_BuyerPurchasingContactName", oSegment.DataElementValue(2, 2)) ' Department or employee (3412)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "COM" Then ' Communication contact
sQlfr = oSegment.DataElementValue(1, 2)
If sQlfr = "EM" Then
sSql = "UPDATE [Orders_Header] SET COM01_01_BuyerEmail = @COM01_01_BuyerEmail " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@COM01_01_BuyerEmail", oSegment.DataElementValue(1, 1)) ' Communication number (3148)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sQlfr = "FX" Then
sSql = "UPDATE [Orders_Header] SET COM01_01_BuyerFax = @COM01_01_BuyerFax " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@COM01_01_BuyerFax", oSegment.DataElementValue(1, 1)) ' Communication number (3148)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sQlfr = "TE" Then
sSql = "UPDATE [Orders_Header] SET COM01_01_BuyerTelephone = @COM01_01_BuyerTelephone " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@COM01_01_BuyerTelephone", oSegment.DataElementValue(1, 1)) ' Communication number (3148)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
End If ' sQlfr
End If ' sSegmentID = "COM"
End If ' sLoopSection
ElseIf sNadQlfr = "ST" Then 'SHIP TO
If sSegmentID = "NAD" Then ' Name and address
sSql = "UPDATE [Orders_Header] SET NAD02_01_ShipToIdentification = @NAD02_01_ShipToIdentification, " _
& "NAD02_03_ShipToAgencyCoded = @NAD02_03_ShipToAgencyCoded " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@NAD02_01_ShipToIdentification", oSegment.DataElementValue(2, 1)) ' Party identification (3039)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@NAD02_03_ShipToAgencyCoded", oSegment.DataElementValue(2, 3)) ' Code list responsible agency, coded (3055)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "LOC" Then ' Place/location identification
sSql = "UPDATE [Orders_Header] SET LOC02_01_ShipToLocationIdentification = @LOC02_01_ShipToLocationIdentification " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@LOC02_01_ShipToLocationIdentification", oSegment.DataElementValue(2, 1)) ' Place/location identification (3225)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
End If ' sSegmentID
If sLoopSection = "NAD;CTA" Then
If sSegmentID = "CTA" Then ' Contact information
sSql = "UPDATE [Orders_Header] SET CTA02_01_ShipToPurchasingContactId = @CTA02_01_ShipToPurchasingContactId, " _
& "CTA02_02_ShipToPurchasingContactName = @CTA02_02_ShipToPurchasingContactName " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@CTA02_01_ShipToPurchasingContactId", oSegment.DataElementValue(2, 1)) ' Department or employee identification (3413)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@CTA02_02_ShipToPurchasingContactName", oSegment.DataElementValue(2, 2)) ' Department or employee (3412)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "COM" Then ' Communication contact
sQlfr = oSegment.DataElementValue(1, 2)
If sQlfr = "EM" Then
sSql = "UPDATE [Orders_Header] SET COM01_01_ShipToEmail = @COM01_01_ShipToEmail " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@COM01_01_ShipToEmail", oSegment.DataElementValue(1, 1)) ' Communication number (3148)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sQlfr = "FX" Then
sSql = "UPDATE [Orders_Header] SET COM01_01_ShipToFax = @COM01_01_ShipToFax " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@COM01_01_ShipToFax", oSegment.DataElementValue(1, 1)) ' Communication number (3148)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sQlfr = "TE" Then
sSql = "UPDATE [Orders_Header] SET COM01_01_ShipToTelephone = @COM01_01_ShipToTelephone " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@COM01_01_ShipToTelephone", oSegment.DataElementValue(1, 1)) ' Communication number (3148)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
End If ' sQlfr
End If ' sSegmentID = "COM"
End If ' sLoopSection
ElseIf sNadQlfr = "SU" Then ' SUPPLIER
If sSegmentID = "NAD" Then ' Name and address
sSql = "UPDATE [Orders_Header] SET NAD02_01_SupplierIdentification = @NAD02_01_SupplierIdentification, " _
& "NAD02_03_SupplierAgencyCoded = @NAD02_03_SupplierAgencyCoded " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@NAD02_01_SupplierIdentification", oSegment.DataElementValue(2, 1)) ' Party identification (3039)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@NAD02_03_SupplierAgencyCoded", oSegment.DataElementValue(2, 3)) ' Code list responsible agency, coded (3055)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "LOC" Then ' Place/location identification
sSql = "UPDATE [Orders_Header] SET LOC02_01_SupplierLocationIdentification = @LOC02_01_SupplierLocationIdentification " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@LOC02_01_SupplierLocationIdentification", oSegment.DataElementValue(2, 1)) ' Place/location identification (3225)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
End If ' sSegmentID
If sLoopSection = "NAD;CTA" Then
If sSegmentID = "CTA" Then ' Contact information
sSql = "UPDATE [Orders_Header] SET CTA02_01_SupplierPurchasingContactId = @CTA02_01_SupplierPurchasingContactId, " _
& "CTA02_02_SupplierPurchasingContactName = @CTA02_02_SupplierPurchasingContactName " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@CTA02_01_SupplierPurchasingContactId", oSegment.DataElementValue(2, 1)) ' Department or employee identification (3413)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@CTA02_02_SupplierPurchasingContactName", oSegment.DataElementValue(2, 2)) ' Department or employee (3412)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "COM" Then ' Communication contact
sQlfr = oSegment.DataElementValue(1, 2)
If sQlfr = "EM" Then
sSql = "UPDATE [Orders_Header] SET COM01_01_SupplierEmail = @COM01_01_SupplierEmail " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@COM01_01_SupplierEmail", oSegment.DataElementValue(1, 1)) ' Communication number (3148)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sQlfr = "FX" Then
sSql = "UPDATE [Orders_Header] SET COM01_01_SupplierFax = @COM01_01_SupplierFax " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@COM01_01_SupplierFax", oSegment.DataElementValue(1, 1)) ' Communication number (3148)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sQlfr = "TE" Then
sSql = "UPDATE [Orders_Header] SET COM01_01_SupplierTelephone = @COM01_01_SupplierTelephone " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@COM01_01_SupplierTelephone", oSegment.DataElementValue(1, 1)) ' Communication number (3148)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
End If ' sQlfr
End If ' sSegmentID = "COM"
End If ' sLoopSection
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 = "LIN" Then
If sSegmentID = "LIN" Then ' Line item
sSql = "INSERT INTO [Orders_Detail] (HeaderKey, LIN01_LineItemNumber, LIN03_01_EAN_GlobalTradeItemNumber, PIA01_ProductIdFunctionQualifier, " _
& "PIA02_01_ItemNumber, PIA02_02_ItemNumberTypeCoded, PIA03_01_ItemNumber, PIA03_02_ItemNumberTypeCoded, IMD03_04_ItemDescription, " _
& "MEA02_01_PropertyMeasuredCoded, MEA03_02_MeasurementValue, QTY01_01_QuantityQualifier, QTY01_02_QuantityInKilograms, " _
& "DTM01_02_MinimumShelfLife, DTM01_03_DateTimePeriodFormatQualifier , PRI01_02_NetPrice) " _
& "values " _
& "(@HeaderKey, @LIN01_LineItemNumber, @LIN03_01_EAN_GlobalTradeItemNumber, @PIA01_ProductIdFunctionQualifier, " _
& "@PIA02_01_ItemNumber, @PIA02_02_ItemNumberTypeCoded, @PIA03_01_ItemNumber, @PIA03_02_ItemNumberTypeCoded, @IMD03_04_ItemDescription, " _
& "@MEA02_01_PropertyMeasuredCoded, @MEA03_02_MeasurementValue, @QTY01_01_QuantityQualifier, @QTY01_02_QuantityInKilograms, " _
& "@DTM01_02_MinimumShelfLife, @DTM01_03_DateTimePeriodFormatQualifier , @PRI01_02_NetPrice); " _
& "SELECT scope_identity()"
oDaDetail.InsertCommand = New SqlCommand(sSql, oConnection)
oDaDetail.InsertCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaDetail.InsertCommand.Parameters.AddWithValue("@LIN01_LineItemNumber", oSegment.DataElementValue(1)) ' Line item number (1082)
oDaDetail.InsertCommand.Parameters.AddWithValue("@LIN03_01_EAN_GlobalTradeItemNumber", oSegment.DataElementValue(3, 1)) ' Item number (7140)
oDaDetail.InsertCommand.Parameters.AddWithValue("@PIA01_ProductIdFunctionQualifier", "")
oDaDetail.InsertCommand.Parameters.AddWithValue("@PIA02_01_ItemNumber", "")
oDaDetail.InsertCommand.Parameters.AddWithValue("@PIA02_02_ItemNumberTypeCoded", "")
oDaDetail.InsertCommand.Parameters.AddWithValue("@PIA03_01_ItemNumber", "")
oDaDetail.InsertCommand.Parameters.AddWithValue("@PIA03_02_ItemNumberTypeCoded", "")
oDaDetail.InsertCommand.Parameters.AddWithValue("@IMD03_04_ItemDescription", "")
oDaDetail.InsertCommand.Parameters.AddWithValue("@MEA02_01_PropertyMeasuredCoded", "")
oDaDetail.InsertCommand.Parameters.AddWithValue("@MEA03_02_MeasurementValue", "")
oDaDetail.InsertCommand.Parameters.AddWithValue("@QTY01_01_QuantityQualifier", "")
oDaDetail.InsertCommand.Parameters.AddWithValue("@QTY01_02_QuantityInKilograms", "")
oDaDetail.InsertCommand.Parameters.AddWithValue("@DTM01_02_MinimumShelfLife", "")
oDaDetail.InsertCommand.Parameters.AddWithValue("@DTM01_03_DateTimePeriodFormatQualifier", "")
oDaDetail.InsertCommand.Parameters.AddWithValue("@PRI01_02_NetPrice", "")
nDetailKey = oDaDetail.InsertCommand.ExecuteScalar()
ElseIf sSegmentID = "PIA" Then ' Additional product id
sSql = "UPDATE [Orders_Detail] SET PIA01_ProductIdFunctionQualifier = @PIA01_ProductIdFunctionQualifier, " _
& "PIA02_01_ItemNumber = @PIA02_01_ItemNumber, PIA02_02_ItemNumberTypeCoded = @PIA02_02_ItemNumberTypeCoded, " _
& "PIA03_01_ItemNumber = @PIA03_01_ItemNumber, PIA03_02_ItemNumberTypeCoded = @PIA03_02_ItemNumberTypeCoded " _
& "where DetailKey = @DetailKey"
oDaDetail.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@PIA01_ProductIdFunctionQualifier", oSegment.DataElementValue(1)) ' Product id. function qualifier (4347)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@PIA02_01_ItemNumber", oSegment.DataElementValue(2, 1)) ' Item number (7140)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@PIA02_02_ItemNumberTypeCoded", oSegment.DataElementValue(2, 2)) ' Item number type, coded (7143)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@PIA03_01_ItemNumber", oSegment.DataElementValue(3, 1)) ' Item number (7140)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@PIA03_02_ItemNumberTypeCoded", oSegment.DataElementValue(3, 2)) ' Item number type, coded (7143)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@DetailKey", nDetailKey)
oDaDetail.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "IMD" Then ' Item description
sSql = "UPDATE [Orders_Detail] SET IMD03_04_ItemDescription = @IMD03_04_ItemDescription " _
& "where DetailKey = @DetailKey"
oDaDetail.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@IMD03_04_ItemDescription", oSegment.DataElementValue(3, 4)) ' Item description (7008)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@DetailKey", nDetailKey)
oDaDetail.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "MEA" Then ' Measurements
sSql = "UPDATE [Orders_Detail] SET MEA02_01_PropertyMeasuredCoded = @MEA02_01_PropertyMeasuredCoded, " _
& "MEA03_02_MeasurementValue = @MEA03_02_MeasurementValue " _
& "where DetailKey = @DetailKey"
oDaDetail.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@MEA02_01_PropertyMeasuredCoded", oSegment.DataElementValue(2, 1)) ' Property measured, coded (6313)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@MEA03_02_MeasurementValue", oSegment.DataElementValue(3, 2)) ' Measurement value (6314)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@DetailKey", nDetailKey)
oDaDetail.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "QTY" Then ' Quantity
sSql = "UPDATE [Orders_Detail] SET QTY01_01_QuantityQualifier = @QTY01_01_QuantityQualifier, " _
& "QTY01_02_QuantityInKilograms = @QTY01_02_QuantityInKilograms " _
& "where DetailKey = @DetailKey"
oDaDetail.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@QTY01_01_QuantityQualifier", oSegment.DataElementValue(1, 1)) ' Quantity qualifier (6063)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@QTY01_02_QuantityInKilograms", oSegment.DataElementValue(1, 2)) ' Quantity (6060)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@DetailKey", nDetailKey)
oDaDetail.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "DTM" Then ' Date/time/period
sSql = "UPDATE [Orders_Detail] SET DTM01_02_MinimumShelfLife = @DTM01_02_MinimumShelfLife, " _
& "DTM01_03_DateTimePeriodFormatQualifier = @DTM01_03_DateTimePeriodFormatQualifier " _
& "where DetailKey = @DetailKey"
oDaDetail.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@DTM01_02_MinimumShelfLife", oSegment.DataElementValue(1, 2)) ' Date/time/period (2380)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@DTM01_03_DateTimePeriodFormatQualifier", oSegment.DataElementValue(1, 3)) ' Date/time/period format qualifier (2379)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@DetailKey", nDetailKey)
oDaDetail.UpdateCommand.ExecuteNonQuery()
End If 'sSegmentID
ElseIf sLoopSection = "LIN;PRI" Then
If sSegmentID = "PRI" Then ' Price details
sSql = "UPDATE [Orders_Detail] SET PRI01_02_NetPrice = @PRI01_02_NetPrice " _
& "where DetailKey = @DetailKey"
oDaDetail.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@PRI01_02_NetPrice", oSegment.DataElementValue(1, 2)) ' Price (5118)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@DetailKey", nDetailKey)
oDaDetail.UpdateCommand.ExecuteNonQuery()
End If ' sSegmentID
End If ' sLoopSection
ElseIf nArea = 3 Then
' Data segments in area 3 are processed here.
If sLoopSection = "" Then
If sSegmentID = "UNS" Then ' Section control
sValue = oSegment.DataElementValue(1) ' Section identification (0081)
ElseIf sSegmentID = "MOA" Then ' Monetary amount
sSql = "UPDATE [Orders_Header] SET MOA01_02_TotalMonetaryAmount = @MOA01_02_TotalMonetaryAmount " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@MOA01_02_TotalMonetaryAmount", oSegment.DataElementValue(1, 2)) ' Monetary amount (5004)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "CNT" Then ' Control total
sSql = "UPDATE [Orders_Header] SET CNT01_01_ControlQualifier = @CNT01_01_ControlQualifier, " _
& "CNT01_02_ControlValue = @CNT01_02_ControlValue " _
& "where HeaderKey = @HeaderKey"
oDaHeader.UpdateCommand = New SqlCommand(sSql, oConnection)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@CNT01_01_ControlQualifier", oSegment.DataElementValue(1, 1)) ' Control qualifier (6069)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@CNT01_02_ControlValue", oSegment.DataElementValue(1, 2)) ' Control value (6066)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@HeaderKey", nHeaderKey)
oDaHeader.UpdateCommand.ExecuteNonQuery()
ElseIf sSegmentID = "UNT" Then ' Message trailer
sValue = oSegment.DataElementValue(1) ' Number of segments in a message (0074)
sValue = oSegment.DataElementValue(2) ' Message reference number (0062)
End If ' sSegmentID = "UNT"
End If ' sLoopSection = ""
End If ' nArea = 3
' 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
