Private Sub OpenFileDialog1_FileOk(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk
Try
Dim reader As New TextFieldParser(Me.OpenFileDialog1.FileName)
'header row fixed field lengths
Dim stdFormat As Integer() = {2, 23, 9, 15, 30, 20, 15, 8, 8, 11}
'detail row fixed field lengths
Dim stdSecondFormat As Integer() = {1, 14, 17}
'holds a row's values for PM10000EXT
Dim pm100values(11) As String
'holds a row's values for PM10100EXT
Dim pm101values(3) As String
Dim vchrnmbr As String
Dim docdate As Date
Dim pordnmbr As String
Dim vendorID As String
Dim rmitoaddressID As String
Dim facility As String
Dim glaccount As String
Dim DocAmount As Double
Dim Amount As Double
Dim dtString As String
reader.TextFieldType = FieldType.FixedWidth
reader.SetFieldWidths(stdFormat)
Dim currentrow As String()
While Not reader.EndOfData
Dim intIndex As Integer = 0
Try
Dim RowType As String = reader.PeekChars(1)
If String.Compare(RowType, "D") = 0 Then
' If this line describes a line that starts with D, the format of the row will be different.
reader.SetFieldWidths(stdSecondFormat)
currentrow = reader.ReadFields
Dim currentField As String
For Each currentField In currentrow
'MsgBox(currentField)
pm101values.SetValue(currentField, intIndex)
intIndex += 1
Next
'create insert procedure for pm10100EXT for these values, run here
'setting doc vchrnmbr to the header number
vchrnmbr = docVchrNmbr
glaccount = pm101values(1)
Amount = pm101values(2)
facPM10000EXT.PM10100EXT_INS(vchrnmbr, glaccount, Amount)
reader.SetFieldWidths(stdFormat)
Else
currentrow = reader.ReadFields()
Dim currentField As String
For Each currentField In currentrow
'MsgBox(currentField)
pm100values.SetValue(currentField, intIndex)
intIndex += 1
Next
'create insert procedure for pm10000EXT for these values, run here
vchrnmbr = pm100values(1)
'convert didnt like the date string format - work around
Dim iYear As Integer = System.Convert.ToInt32(pm100values(7).Substring(0, 4))
Dim iMonth As Integer = System.Convert.ToInt32(pm100values(7).Substring(4, 2))
Dim iDay As Integer = System.Convert.ToInt32(pm100values(7).Substring(6, 2))
docdate = New DateTime(iYear, iMonth, iDay)
pordnmbr = pm100values(2)
rmitoaddressID = vendorarray(1)
facility = pm100values(6)
DocAmount = pm100values(9)
facPM10000EXT.PM10000EXT_INS(vchrnmbr, docdate, pordnmbr, vendorID, rmitoaddressID, facility, DocAmount)
'have to set this variable for details insert
End If
Catch ex As Exception
MsgBox("Line " & ex.Message & _
"is not valid and will be skipped.")
End Try
End While
MsgBox("Import Complete")
Catch Ex As Exception
End Try
End Sub