Private Sub cmdGenerate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdGenerate.Click
'This is just an example program to show how to generate an EDI 834 X095 file from a database in VB.NET
'with Framework EDI component
Dim oEdiDoc As ediDocument
Dim oInterchange As ediInterchange
Dim oGroup As ediGroup
Dim oTransactionset As ediTransactionSet
Dim oSegment As ediDataSegment
Dim oSchema As ediSchema
Dim oSchemas As ediSchemas
Dim sSefFile As String
Dim sEdiFile As String
Dim i As Integer
Dim nCount As Integer
Dim sEntity As String
Dim nIndex As Integer
Dim iItemCount As Integer
Dim sInstance As String
Dim sSql As String
Dim nHlCount As Integer
Dim nHlProvParent As Integer
Dim nHlSubscriberParent As Integer
Dim oClaimsDs As DataSet
Dim oClaimsRow As DataRow
Dim oServiceDs As DataSet
Dim oServiceRow As DataRow
Dim sPath As String = AppDomain.CurrentDomain.BaseDirectory
'connection string to an access database
Dim sConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & sPath & "db1.mdb"
'create connection to database
Dim oConnection As New OleDb.OleDbConnection(sConnection)
oConnection.Open()
'Prepare a dataset from the Interchange table
sSql = "select * from Interchange"
Dim oAdapter As New OleDb.OleDbDataAdapter(sSql, oConnection)
Dim oInterchangeDS As New DataSet("dsInterchange")
Dim oInterchangeRow As DataRow
oAdapter.Fill(oInterchangeDS, "dsInterchange")
'create interchange loop
For Each oInterchangeRow In oInterchangeDS.Tables("dsInterchange").Rows
Me.Cursor = Cursors.WaitCursor
sPath = AppDomain.CurrentDomain.BaseDirectory
sSefFile = sPath & "834_X095.SEF"
sEdiFile = sPath & "834_X095_output.X12"
ediDocument.Set(oEdiDoc, New ediDocument)
'Changing the cursor type to forward write is memory efficient and improves performance.
'Also, the loop instances do not have to be specified in the heirarchy string syntax
oEdiDoc.CursorType = DocumentCursorTypeConstants.Cursor_ForwardWrite
'disabling internal standard reference library
oSchemas = oEdiDoc.GetSchemas
oSchemas.EnableStandardReference = False
'load SEF file
oSchema = oEdiDoc.ImportSchema(sSefFile, 0)
'set terminators
oEdiDoc.SegmentTerminator = "~" & vbCrLf
oEdiDoc.ElementTerminator = "*"
oEdiDoc.CompositeTerminator = ":"
'CREATE INTERCHANGE
ediInterchange.Set(oInterchange, oEdiDoc.CreateInterchange("X", "004010"))
ediDataSegment.Set(oSegment, oInterchange.GetDataSegmentHeader)
oSegment.DataElementValue(1) = "00" 'Authorization Information Qualifier
oSegment.DataElementValue(2) = "" 'Authorization Information
oSegment.DataElementValue(3) = "00" 'Security Information Qualifier
oSegment.DataElementValue(4) = "" 'Security Information
oSegment.DataElementValue(5) = oInterchangeRow("SenderQlfr") 'Interchange ID Qualifier
oSegment.DataElementValue(6) = oInterchangeRow("SenderID") 'Interchange Sender ID
oSegment.DataElementValue(7) = oInterchangeRow("ReceiverQlfr") 'Interchange ID Qualifier
oSegment.DataElementValue(8) = oInterchangeRow("ReceiverID") 'Interchange Receiver ID
oSegment.DataElementValue(9) = "010821" 'Interchange Date
oSegment.DataElementValue(10) = "1548" 'Interchange Time
oSegment.DataElementValue(11) = "U" 'Interchange Control Standards Identifier
oSegment.DataElementValue(12) = "00401" 'Interchange Control Version Number
oSegment.DataElementValue(13) = oInterchangeRow("ControlNo") 'Interchange Control Number
oSegment.DataElementValue(14) = "0" 'Acknowledgment Requested
oSegment.DataElementValue(15) = "T" 'Usage Indicator
oSegment.DataElementValue(16) = ":" 'Component Element Separator
'prepare dataset from the FuncGroup table
sSql = "select * from FuncGroup where InterchangeKey = " & Trim(Str(oInterchangeRow("InterchangeKey")))
oAdapter = New OleDb.OleDbDataAdapter(sSql, oConnection)
Dim oGroupDs As New DataSet("dsGroup")
Dim oGroupRow As DataRow
oAdapter.Fill(oGroupDs, "dsGroup")
'create the functional group loop
For Each oGroupRow In oGroupDs.Tables("dsGroup").Rows
'CREATE FUNCTIONAL GROUP
ediGroup.Set(oGroup, oInterchange.CreateGroup("004010X095"))
ediDataSegment.Set(oSegment, oGroup.GetDataSegmentHeader)
oSegment.DataElementValue(1) = oGroupRow("FuncID") 'Functional Identifier Code
oSegment.DataElementValue(2) = oInterchangeRow("SenderID") 'Application Sender's Code
oSegment.DataElementValue(3) = oInterchangeRow("ReceiverID") 'Application Receiver's Code
oSegment.DataElementValue(4) = "20010821" 'Date
oSegment.DataElementValue(5) = "1548" 'Time
oSegment.DataElementValue(6) = oGroupRow("ControlNo") 'Group Control Number
oSegment.DataElementValue(7) = "X" 'Responsible Agency Code
oSegment.DataElementValue(8) = "004010X095" 'Version / Release / Industry Identifier Code
'prepare dataset from X098Header table
sSql = "select * from X095Header where Groupkey = " & Trim(Str(oGroupRow("Groupkey")))
oAdapter = New OleDb.OleDbDataAdapter(sSql, oConnection)
Dim oSetDs As New DataSet("dsSet")
Dim oSetRow As DataRow
oAdapter.Fill(oSetDs, "dsSet")
'create the transaction set loop
For Each oSetRow In oSetDs.Tables("dsSet").Rows
nHlCount = 0
'HEADER
'ST TRANSACTION SET HEADER
ediTransactionSet.Set(oTransactionset, oGroup.CreateTransactionSet("834"))
ediDataSegment.Set(oSegment, oTransactionset.GetDataSegmentHeader)
oSegment.DataElementValue(1) = oSetRow("MessageId") 'Transaction Set Identifier Code
oSegment.DataElementValue(2) = oSetRow("ControlNo") 'Transaction Set Control Number
'Beginning Segment
'create BGN segment
ediDataSegment.Set(oSegment, oTransactionset.CreateDataSegment("BGN"))
oSegment.DataElementValue(1) = oSetRow("PurposeCode")
oSegment.DataElementValue(2) = oSetRow("TransactionId")
oSegment.DataElementValue(3) = oSetRow("TransactionDate")
oSegment.DataElementValue(4) = oSetRow("TransactionTime")
oSegment.DataElementValue(8) = oSetRow("ActionCode")
'Plan Sponsor
'create N1 segment in N1 loop
ediDataSegment.Set(oSegment, oTransactionset.CreateDataSegment("N1\N1"))
oSegment.DataElementValue(1) = "P5"
oSegment.DataElementValue(2) = oSetRow("SponserName")
oSegment.DataElementValue(3) = "FI"
oSegment.DataElementValue(4) = oSetRow("SponserTaxId")
'Payer
'create N1 segment in second instance of N1 loop
ediDataSegment.Set(oSegment, oTransactionset.CreateDataSegment("N1(2)\N1"))
oSegment.DataElementValue(1) = "IN"
oSegment.DataElementValue(2) = oSetRow("InsurerName")
oSegment.DataElementValue(3) = "FI"
oSegment.DataElementValue(4) = oSetRow("InsurerTaxId")
'Member Detail
sSql = "select * from X095MemberDetail where TSetKey = " & Trim(Str(oSetRow("TSetKey")))
oAdapter = New OleDb.OleDbDataAdapter(sSql, oConnection)
Dim oMemberDetailDs As New DataSet("dsMemberDetail")
Dim oMemberDetailRow As DataRow
oAdapter.Fill(oMemberDetailDs, "dsMemberDetail")
For Each oMemberDetailRow In oMemberDetailDs.Tables("dsMemberDetail").Rows
'Member Level Detail
'create INS segment in INS loop
ediDataSegment.Set(oSegment, oTransactionset.CreateDataSegment("INS\INS"))
oSegment.DataElementValue(1) = oMemberDetailRow("Subscriber")
oSegment.DataElementValue(2) = oMemberDetailRow("Relationship")
oSegment.DataElementValue(3) = "021"
oSegment.DataElementValue(4) = "20"
oSegment.DataElementValue(5) = oMemberDetailRow("BenefitStatusCode")
oSegment.DataElementValue(8) = "FT"
'Subscriber Number
'create REF segment in INS loop
ediDataSegment.Set(oSegment, oTransactionset.CreateDataSegment("INS\REF"))
oSegment.DataElementValue(1) = "0F"
oSegment.DataElementValue(2) = oMemberDetailRow("SubscriberNo")
'Member Policy Number
'create second instance of REF segment in INS loop
ediDataSegment.Set(oSegment, oTransactionset.CreateDataSegment("INS\REF(2)"))
oSegment.DataElementValue(1) = "1L"
oSegment.DataElementValue(2) = oMemberDetailRow("GroupPolicyNo")
'Member Level Dates
'create DTP segment in INS loop
ediDataSegment.Set(oSegment, oTransactionset.CreateDataSegment("INS\DTP"))
oSegment.DataElementValue(1) = "356"
oSegment.DataElementValue(2) = "D8"
oSegment.DataElementValue(3) = oMemberDetailRow("EligibilityStartDate")
'Member Name
'create NM1 segment in NM1 loop nested in INS loop
ediDataSegment.Set(oSegment, oTransactionset.CreateDataSegment("INS\NM1\NM1"))
oSegment.DataElementValue(1) = "IL"
oSegment.DataElementValue(2) = "1"
oSegment.DataElementValue(3) = oMemberDetailRow("Lastname")
oSegment.DataElementValue(4) = oMemberDetailRow("Firstname")
oSegment.DataElementValue(8) = "34"
oSegment.DataElementValue(9) = oMemberDetailRow("SSN")
'Member Communications Numbers
'create PER segment in NM1 loop nested in INS loop
ediDataSegment.Set(oSegment, oTransactionset.CreateDataSegment("INS\NM1\PER"))
oSegment.DataElementValue(1) = "IP"
oSegment.DataElementValue(3) = "HP"
oSegment.DataElementValue(4) = oMemberDetailRow("HomePhone")
oSegment.DataElementValue(5) = "WP"
oSegment.DataElementValue(6) = oMemberDetailRow("WorkPhone")
'Member Residence Street Address
'create N3 segment in NM1 loop nested in INS loop
ediDataSegment.Set(oSegment, oTransactionset.CreateDataSegment("INS\NM1\N3"))
oSegment.DataElementValue(1) = oMemberDetailRow("Address")
'Member Residence City, State, ZIP Code
'create N4 segment in NM1 loop nested in INS loop
ediDataSegment.Set(oSegment, oTransactionset.CreateDataSegment("INS\NM1\N4"))
oSegment.DataElementValue(1) = oMemberDetailRow("City")
oSegment.DataElementValue(2) = oMemberDetailRow("State")
oSegment.DataElementValue(3) = oMemberDetailRow("Zip")
'Member Demographics
'create DMG segment in NM1 loop nested in INS loop
ediDataSegment.Set(oSegment, oTransactionset.CreateDataSegment("INS\NM1\DMG"))
oSegment.DataElementValue(1) = "D8"
oSegment.DataElementValue(2) = oMemberDetailRow("BirthDate")
oSegment.DataElementValue(3) = oMemberDetailRow("GenderCode")
'Member Detail
sSql = "select * from X095HealthCoverage where MemberKey = " & Trim(Str(oMemberDetailRow("MemberKey")))
oAdapter = New OleDb.OleDbDataAdapter(sSql, oConnection)
Dim oHealthCoverageDs As New DataSet("dsHealthCoverage")
Dim oHealthCoverageRow As DataRow
oAdapter.Fill(oHealthCoverageDs, "dsHealthCoverage")
For Each oHealthCoverageRow In oHealthCoverageDs.Tables("dsHealthCoverage").Rows
'Health Coverage - Health
'create HD segment in HD loop nested in INS loop
ediDataSegment.Set(oSegment, oTransactionset.CreateDataSegment("INS\HD\HD"))
oSegment.DataElementValue(1) = "021"
oSegment.DataElementValue(3) = oHealthCoverageRow("InsuranceCode")
'Health Coverage Dates
'create DTP segment in HD loop nested in INS loop
ediDataSegment.Set(oSegment, oTransactionset.CreateDataSegment("INS\HD\DTP"))
oSegment.DataElementValue(1) = "348"
oSegment.DataElementValue(2) = "D8"
oSegment.DataElementValue(3) = oHealthCoverageRow("BenefitBeginDate")
Next 'oHealthCoverageRow
Next 'oMemberDetailRow
Next 'SetRow
Next 'GroupRow
Next 'oInterchangeRow
oEdiDoc.Save(sEdiFile) 'save to a file
MsgBox(oEdiDoc.GetEdiString) 'view edi file
Me.Cursor = Cursors.Default
MsgBox("Done")
cmdGenerate.Enabled = False
End Sub