Private Sub cmdTranslate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdTranslate.Click
'This is just an example program to show how to translate an EDI 834 X095 EDI file into a database in VB.NET
'with Framework EDI component
Dim oEdiDoc As ediDocument
Dim oSchema As ediSchema
Dim oSchemas As ediSchemas
Dim oSegment As ediDataSegment
Dim oElement As ediDataElement
Dim oConn As ADODB.Connection
'This sample program uses ADODB (not ADO.NET) to access the database
Dim oRsInterchange As ADODB.Recordset
Dim oRsFuncGroup As ADODB.Recordset
Dim oRsX095Header As ADODB.Recordset
Dim oRsX095MemberDetail As ADODB.Recordset
Dim oRsX095HealthCoverage As ADODB.Recordset
Dim sSegmentID As String
Dim sLoopSection As String
Dim nArea As Integer
Dim sQlfr As String
Dim sPath As String
Dim sEntity As String
Dim sSefFile As String
Dim sEdiFile As String
Dim sSql As String
Me.Cursor = Cursors.WaitCursor
sPath = AppDomain.CurrentDomain.BaseDirectory
sSefFile = "834_X095.SEF"
sEdiFile = "834_X095.X12"
'instantiate EDI document object
ediDocument.Set(oEdiDoc, New ediDocument)
'change cursor type to ForwardOnly to improve performance
oEdiDoc.CursorType = DocumentCursorTypeConstants.Cursor_ForwardOnly
'disable internal standard reference library to save RAM and improve performance
ediSchemas.Set(oSchemas, oEdiDoc.GetSchemas)
oSchemas.EnableStandardReference = False
'load sef file
oEdiDoc.ImportSchema(sPath & sSefFile, 0)
'load edi file
oEdiDoc.LoadEdi(sPath & sEdiFile)
'Connection string to access an MS Acess database
Dim sConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & sPath & "db1.mdb"
'OPENS DATABASE CONNECTION
oConn = New ADODB.Connection
oConn.Open(sConnection)
oRsInterchange = New ADODB.Recordset
oRsInterchange.Open("Interchange", oConn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
oRsFuncGroup = New ADODB.Recordset
oRsFuncGroup.Open("FuncGroup", oConn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
oRsX095Header = New ADODB.Recordset
oRsX095Header.Open("X095Header", oConn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
oRsX095MemberDetail = New ADODB.Recordset
oRsX095MemberDetail.Open("X095MemberDetail", oConn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
oRsX095HealthCoverage = New ADODB.Recordset
oRsX095HealthCoverage.Open("X095HealthCoverage", oConn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
'Load EDI file
oEdiDoc.LoadEdi(sPath & sEdiFile)
'Iterate through all segments in the EDI file
'Get the firs data segment in the EDI file
ediDataSegment.Set(oSegment, oEdiDoc.FirstDataSegment)
While Not oSegment Is Nothing
'identify segments by their area, loop section they are in, and their segment id
sSegmentID = oSegment.ID
nArea = oSegment.Area
sLoopSection = oSegment.LoopSection
If nArea = "0" Then
If sSegmentID = "ISA" Then
oRsInterchange.AddNew()
oRsInterchange("SenderQlfr").Value = oSegment.DataElementValue(5) 'Interchange ID Qualifier
oRsInterchange("SenderID").Value = oSegment.DataElementValue(6) 'Interchange Sender ID
oRsInterchange("ReceiverQlfr").Value = oSegment.DataElementValue(7) 'Interchange ID Qualifier
oRsInterchange("ReceiverID").Value = oSegment.DataElementValue(8) 'Interchange Receiver ID
oRsInterchange("InterDate").Value = oSegment.DataElementValue(9) 'Interchange Date
oRsInterchange("ControlNo").Value = oSegment.DataElementValue(13) 'Interchange Control Number
ElseIf sSegmentID = "GS" Then
oRsFuncGroup.AddNew()
oRsFuncGroup("InterchangeKey").Value = oRsInterchange("InterchangeKey").Value
oRsFuncGroup("FuncID").Value = oSegment.DataElementValue(1) 'Functional Identifier Code
oRsFuncGroup("ControlNo").Value = oSegment.DataElementValue(6) 'Group Control Number
ElseIf sSegmentID = "IEA" Then
'update interchange record
oRsInterchange.Update()
ElseIf sSegmentID = "GE" Then
'update functional group record
oRsFuncGroup.Update()
End If
ElseIf nArea = "1" Then
If sLoopSection = "" Then
If sSegmentID = "ST" Then
oRsX095Header.AddNew()
oRsX095Header("GroupKey").Value = oRsFuncGroup("GroupKey").Value
oRsX095Header("MessageId").Value = oSegment.DataElementValue(1) 'Transaction Set Identifier Code
oRsX095Header("ControlNo").Value = oSegment.DataElementValue(2) 'Transaction Set Control Number
ElseIf sSegmentID = "BGN" Then
oRsX095Header("PurposeCode").Value = oSegment.DataElementValue(1) ' Transaction Set Purpose Code (353)
oRsX095Header("TransactionId").Value = oSegment.DataElementValue(2) ' Reference Identification (127)
oRsX095Header("TransactionDate").Value = oSegment.DataElementValue(3) ' Date (373)
oRsX095Header("TransactionTime").Value = oSegment.DataElementValue(4) ' Time (337)
oRsX095Header("ActionCode").Value = oSegment.DataElementValue(8) ' Action Code (306)
End If 'sSegmentID
ElseIf sLoopSection = "N1" Then
If sSegmentID = "N1" Then
sEntity = oSegment.DataElementValue(1) 'identify loop instance by their entity identifier value
sQlfr = oSegment.DataElementValue(3)
If sEntity = "P5" And sQlfr = "FI" Then 'Sponser infomation
oRsX095Header("SponserName").Value = oSegment.DataElementValue(2) ' Name (93)
oRsX095Header("SponserTaxId").Value = oSegment.DataElementValue(4) ' Identification Code (67)
ElseIf sEntity = "IN" And sQlfr = "FI" Then 'Insurer information
oRsX095Header("InsurerName").Value = oSegment.DataElementValue(2)
oRsX095Header("InsurerTaxId").Value = oSegment.DataElementValue(4)
End If
End If 'sSegmentID
End If 'sLoopSection
ElseIf nArea = "2" Then
If sLoopSection = "" Then
If sSegmentID = "SE" Then
'The SE segment is the end of the transaction set so is a good place to make sure all records are updated
oRsX095Header.Update()
oRsX095MemberDetail.Update()
End If
ElseIf sLoopSection = "INS" Then
If sSegmentID = "INS" Then
If Not oRsX095MemberDetail.BOF Then
'update any previous record before creating a new record
oRsX095MemberDetail.Update()
End If
oRsX095MemberDetail.AddNew()
oRsX095MemberDetail("TSetKey").Value = oRsX095Header("TSetKey").Value
oRsX095MemberDetail("Subscriber").Value = oSegment.DataElementValue(1)
oRsX095MemberDetail("Relationship").Value = oSegment.DataElementValue(2)
oRsX095MemberDetail("BenefitStatusCode").Value = oSegment.DataElementValue(5)
ElseIf sSegmentID = "REF" Then
sQlfr = oSegment.DataElementValue(1) 'check qualifier to identify the many instances of the same REF segment
If sQlfr = "0F" Then 'subscriber name
oRsX095MemberDetail("SubscriberNo").Value = oSegment.DataElementValue(2)
ElseIf sQlfr = "1L" Then 'group or policy number
oRsX095MemberDetail("GroupPolicyNo").Value = oSegment.DataElementValue(2)
End If
ElseIf sSegmentID = "DTP" Then
If oSegment.DataElementValue(1) = "356" Then 'Eligibility Begin
oRsX095MemberDetail("EligibilityStartDate").Value = oSegment.DataElementValue(3)
End If
End If
ElseIf sLoopSection = "INS;NM1" Then
If sSegmentID = "NM1" Then
sEntity = oSegment.DataElementValue(1) 'Get entity qualifer to determine loop instances
End If
'LOOP 2100A
If sEntity = "74" Or sEntity = "IL" Then 'Insured
If sSegmentID = "NM1" Then
oRsX095MemberDetail("Firstname").Value = oSegment.DataElementValue(4)
oRsX095MemberDetail("Lastname").Value = oSegment.DataElementValue(3)
oRsX095MemberDetail("SSN").Value = oSegment.DataElementValue(9)
ElseIf sSegmentID = "PER" Then
oRsX095MemberDetail("HomePhone").Value = oSegment.DataElementValue(4)
oRsX095MemberDetail("WorkPhone").Value = oSegment.DataElementValue(6)
ElseIf sSegmentID = "N3" Then
oRsX095MemberDetail("Address").Value = oSegment.DataElementValue(1)
ElseIf sSegmentID = "N4" Then
oRsX095MemberDetail("City").Value = oSegment.DataElementValue(1)
oRsX095MemberDetail("State").Value = oSegment.DataElementValue(2)
oRsX095MemberDetail("Zip").Value = oSegment.DataElementValue(3)
ElseIf sSegmentID = "DMG" Then
oRsX095MemberDetail("BirthDate").Value = oSegment.DataElementValue(2)
oRsX095MemberDetail("GenderCode").Value = oSegment.DataElementValue(3)
End If 'sSegmentID
ElseIf sEntity = "70" Then 'LOOP 2100B - Incorrect insured
If sSegmentID = "NM1" Then
ElseIf sSegmentID = "DMG" Then
End If 'sSegmentID
ElseIf sEntity = "31" Then 'LOOP 2100C - Post mailing address
'If sSegmentID = "NM1" Then
' oRsX095MemberDetail("MailToFirstname").Value = oSegment.DataElementValue(4)
' oRsX095MemberDetail("MailToLastname").Value = oSegment.DataElementValue(3)
' oRsX095MemberDetail("MailToSSN").Value = oSegment.DataElementValue(9)
'ElseIf sSegmentID = "PER" Then
' oRsX095MemberDetail("MailToHomePhone").Value = oSegment.DataElementValue(4)
' oRsX095MemberDetail("MailToWorkPhone").Value = oSegment.DataElementValue(6)
'ElseIf sSegmentID = "N3" Then
' oRsX095MemberDetail("MailToAddress").Value = oSegment.DataElementValue(1)
'ElseIf sSegmentID = "N4" Then
' oRsX095MemberDetail("MailToCity").Value = oSegment.DataElementValue(1)
' oRsX095MemberDetail("MailToState").Value = oSegment.DataElementValue(2)
' oRsX095MemberDetail("MailToZip").Value = oSegment.DataElementValue(3)
'End If
ElseIf sEntity = "ES" Then 'LOOP 2100D - Employer Name
'If sSegmentID = "NM1" Then
' oRsX095MemberDetail("EmployerFirstname").Value = oSegment.DataElementValue(4)
' oRsX095MemberDetail("EmployerLastname").Value = oSegment.DataElementValue(3)
' oRsX095MemberDetail("EmployerSSN").Value = oSegment.DataElementValue(9)
'ElseIf sSegmentID = "PER" Then
' oRsX095MemberDetail("EmployerHomePhone").Value = oSegment.DataElementValue(4)
' oRsX095MemberDetail("EmployerWorkPhone").Value = oSegment.DataElementValue(6)
'ElseIf sSegmentID = "N3" Then
' oRsX095MemberDetail("EmployerAddress").Value = oSegment.DataElementValue(1)
'ElseIf sSegmentID = "N4" Then
' oRsX095MemberDetail("EmployerCity").Value = oSegment.DataElementValue(1)
' oRsX095MemberDetail("EmployerState").Value = oSegment.DataElementValue(2)
' oRsX095MemberDetail("EmployerZip").Value = oSegment.DataElementValue(3)
'End If
ElseIf sEntity = "M8" Then 'LOOP 2100E - Educational Institution
'If sSegmentID = "NM1" Then
'ElseIf sSegmentID = "PER" Then
'ElseIf sSegmentID = "N3" Then
'ElseIf sSegmentID = "N4" Then
'End If
ElseIf sEntity = "S3" Then 'LOOP 2100F - Custodial Parent
'If sSegmentID = "NM1" Then
'ElseIf sSegmentID = "PER" Then
'ElseIf sSegmentID = "N3" Then
'ElseIf sSegmentID = "N4" Then
'End If
ElseIf sEntity = "E1" Or sEntity = "EI" Or sEntity = "GD" Or sEntity = "J6" Then 'LOOP 2100G - Guradian
'If sSegmentID = "NM1" Then
'ElseIf sSegmentID = "PER" Then
'ElseIf sSegmentID = "N3" Then
'ElseIf sSegmentID = "N4" Then
'End If
End If 'sEntity
ElseIf sLoopSection = "INS;HD" Then
If sSegmentID = "HD" Then
If Not oRsX095HealthCoverage.BOF Then
oRsX095HealthCoverage.Update()
End If
oRsX095HealthCoverage.AddNew()
oRsX095HealthCoverage("MemberKey").Value = oRsX095MemberDetail("MemberKey").Value
sEntity = oSegment.DataElementValue(3)
If sEntity = "HLT" Then
oRsX095HealthCoverage("InsuranceCode").Value = "Health"
ElseIf sEntity = "DEN" Then
oRsX095HealthCoverage("InsuranceCode").Value = "Dental"
ElseIf sEntity = "VIS" Then
oRsX095HealthCoverage("InsuranceCode").Value = "Vision"
End If
ElseIf sSegmentID = "DTP" Then
oRsX095HealthCoverage("BenefitBeginDate").Value = oSegment.DataElementValue(3)
End If 'sSegmentID
End If 'sLoopSection
ElseIf nArea = "3" Then
End If 'nArea
'get next segment
ediDataSegment.Set(oSegment, oSegment.Next) 'oSegment = oSegment.Next
End While
oRsX095HealthCoverage.Update()
oRsX095MemberDetail.Update()
oRsX095Header.Update()
oRsFuncGroup.Update()
oRsInterchange.Update()
Me.Cursor = Cursors.Default
MsgBox("Done")
End Sub