Reply to thread

Message

[QUOTE="wjburke2, post: 167626, member: 21091"]
Thanks for your response jmcilhinney. Man I spent hours researching TextFieldParser. Pretty neat stuff. I decided not to go that way because of the complexity of defining fieldwidths and then doing the update they change this thing all the time and maintenance world be a bear. So I decided to continue the direction I was going and just figure out how to fix the error. Turns out I was trying to use .net automation without really going through the full process. But thanks I am sure I will use that at some point and now I know how.  Here's what I ended up with that works.
[CODE]    Public Sub ParseOneCodeFiles(ByVal InFileName As String)

Dim lngTotalACS As Long
        Dim strCreateDate As String
        Dim strFileName As String
        Dim totalread As Integer

Dim daHeader As New ImportACS300DataSetTableAdapters.RecordHeaderTableAdapter
        Dim daSnglSrc As New ImportACS300DataSetTableAdapters.SingleSrc_TempTableAdapter
        daHeader.Connection = New OleDb.OleDbConnection(conString)
        daSnglSrc.Connection = New OleDb.OleDbConnection(conString)

Dim ds As New ImportACS300DataSet

'***********************************************
        '****         Create Work Tables             ***
        '***********************************************
        daHeader.Fill(ds.RecordHeader)
        daSnglSrc.Fill(ds.SingleSrc_Temp)

'***********************************************
        '****         Import from Text               ***
        '***********************************************
        strFileName = Mid(InFileName, 1 + InStrRev(InFileName, "\"))

Dim reader = File.OpenText(InFileName)
        Dim strread As String = Nothing
        Dim lines As Integer = 0

While (reader.Peek() <> -1)
            strread = reader.ReadLine()
            lines = lines + 1

If Not (Trim(strread) = "") Then 'Ignore blank line
                'Test for Header Record
                If "H" = Mid(strread, 1, 1) Then
                    If lines <> 1 Then
                        MsgBox("Invalid header file please corect")
                        GoTo ParseOneCodeFiles_exit
                    End If

Dim dsNewRow As ImportACS300DataSet.RecordHeaderRow
                    dsNewRow = ds.RecordHeader.NewRecordHeaderRow

dsNewRow.MailerID = CStr(Trim(Mid(strread, 2, 6)))
                    dsNewRow.FileName = strFileName
                    dsNewRow.CreateDate = CStr(Mid(strread, 8, 8))
                    strCreateDate = CStr(Mid(strread, 8, 8))
                    dsNewRow.TotalACS = CInt(Trim(Mid(strread, 16, 9)))
                    lngTotalACS = CInt(Trim(Mid(strread, 16, 9)))
                    dsNewRow.TotalCOA = CInt(Trim(Mid(strread, 25, 9)))
                    dsNewRow.TotalNIXIE = CInt(Trim(Mid(strread, 34, 9)))
                    dsNewRow.ShipmentNum = CStr(Trim(Mid(strread, 43, 8)))
                    'dsNewRow.m = CStr(Trim(Mid(strread, 51, 1)))
                    dsNewRow.MediaType = CStr(Trim(Mid(strread, 52, 1)))
                    dsNewRow.EntryType = "S"
                    ds.RecordHeader.AddRecordHeaderRow(dsNewRow)

ElseIf "2" = Mid(strread, 1, 1) Then
                    'This is a COA/NIXIE - Write to COA dB
                    totalread = +1
                    Dim dsNewRow As ImportACS300DataSet.SingleSrc_TempRow
                    dsNewRow = ds.SingleSrc_Temp.NewSingleSrc_TempRow

dsNewRow.RecType = CStr(Trim(Mid(strread, 1, 1)))
                    dsNewRow.FileName = strFileName
                    dsNewRow.SeqNum = CStr(Trim(Mid(strread, 2, 8)))
                    dsNewRow.MailerId6 = CStr(Trim(Mid(strread, 10, 7)))
                    dsNewRow.MailPieceId = CStr(Trim(Mid(strread, 17, 9)))
                    dsNewRow.MoveCCYYMM = CStr(Trim(Mid(strread, 33, 6)))
                    dsNewRow.MoveType = CStr(Trim(Mid(strread, 39, 1)))
                    dsNewRow.DelivbleCode = CStr(Trim(Mid(strread, 40, 1)))
                    dsNewRow.POSiteId = CStr(Trim(Mid(strread, 41, 3)))

dsNewRow.COALName = CStr(Trim(Mid(strread, 44, 20)))
                    dsNewRow.COAFNameMI = CStr(Trim(Mid(strread, 64, 15)))
                    dsNewRow.COAPrefix = CStr(Trim(Mid(strread, 79, 6)))
                    dsNewRow.COASufix = CStr(Trim(Mid(strread, 85, 6)))

dsNewRow.OldAddrType = CStr(Trim(Mid(strread, 91, 1)))
                    dsNewRow.OldUrbName = CStr(Trim(Mid(strread, 92, 28)))
                    dsNewRow.OldPrimNumber = CStr(Trim(Mid(strread, 120, 10)))
                    dsNewRow.OldPreDir = CStr(Trim(Mid(strread, 130, 2)))
                    dsNewRow.OldStreetName = CStr(Trim(Mid(strread, 132, 28)))
                    dsNewRow.OldStreetSfx = CStr(Trim(Mid(strread, 160, 4)))
                    dsNewRow.OldPostDir = CStr(Trim(Mid(strread, 164, 2)))
                    dsNewRow.OldUnitDsgn = CStr(Trim(Mid(strread, 166, 4)))
                    dsNewRow.OldSecNumber = CStr(Trim(Mid(strread, 170, 10)))
                    dsNewRow.OldCity = CStr(Trim(Mid(strread, 180, 28)))
                    dsNewRow.OldState = CStr(Trim(Mid(strread, 208, 2)))
                    dsNewRow.OldZip = CStr(Trim(Mid(strread, 210, 5)))
                    If dsNewRow.OldZip = "00000" Then
                        dsNewRow.OldZip = CStr(Trim(Mid(strread, 448, 5)))
                    End If

dsNewRow.NewAddrType = CStr(Trim(Mid(strread, 215, 1)))
                    dsNewRow.NewUrbName = CStr(Trim(Mid(strread, 216, 28)))
                    dsNewRow.NewPrimNumber = CStr(Trim(Mid(strread, 244, 10)))
                    dsNewRow.NewPreDir = CStr(Trim(Mid(strread, 254, 2)))
                    dsNewRow.NewStreetName = CStr(Trim(Mid(strread, 256, 28)))
                    dsNewRow.NewStreetSfx = CStr(Trim(Mid(strread, 284, 4)))
                    dsNewRow.NewPostDir = CStr(Trim(Mid(strread, 288, 2)))
                    dsNewRow.NewUnitDsgn = CStr(Trim(Mid(strread, 290, 4)))
                    dsNewRow.NewSecNumber = CStr(Trim(Mid(strread, 294, 10)))
                    dsNewRow.NewCity = CStr(Trim(Mid(strread, 304, 28)))
                    dsNewRow.NewState = CStr(Trim(Mid(strread, 332, 2)))
                    dsNewRow.NewZip = CStr(Trim(Mid(strread, 334, 5)))
                    dsNewRow.NewZip4 = CStr(Trim(Mid(strread, 340, 4)))
                    dsNewRow.NewDPBC = CStr(Trim(Mid(strread, 344, 3)))

dsNewRow.LableFmtAddr = CStr(Trim(Mid(strread, 347, 66)))
                    dsNewRow.FeeNoticeType = CStr(Trim(Mid(strread, 413, 1)))
                    dsNewRow.PostageDue = CStr(Trim(Mid(strread, 415, 4)))
                    '               rstTrad!PMB") = CStr(Trim(Mid(strread, 419, 8)))
                    dsNewRow.NoticeType = CStr(Trim(Mid(strread, 427, 1)))
                    dsNewRow.OriginalIMB = CStr(Trim(Mid(strread, 428, 31)))
                    dsNewRow.MailerId9 = CStr(Trim(Mid(strread, 460, 9)))

'dsNewRow.CreateDate") = strCreateDate
                    dsNewRow.AccumCCYYMMDD = Now.ToString("yyyyMMdd")
                    dsNewRow.ProcDate = "00000000"
                    dsNewRow.EntryType = "S"
                    ds.SingleSrc_Temp.AddSingleSrc_TempRow(dsNewRow)

ElseIf "" = Mid(strread, 1, 1) Then
                    'MsgBox("EOF Reached")
                Else
                    MsgBox("This is not a valid record type" & strread)
                    'DeleteWorkFiles()
                    'GoTo ParseOneCodeFiles_exit
                End If
            End If

End While

'update the database with the new rows
        daHeader.Update(ds.RecordHeader)
        daSnglSrc.Update(ds.SingleSrc_Temp)

If totalread <> lngTotalACS Then
            'DeleteWorkFiles()
            MsgBox("Import not sussessful Input file counts do not match Header", vbOKOnly)
        End If

ParseOneCodeFiles_exit:

daHeader.Connection.Close()
        daSnglSrc.Connection.Close()

Exit Sub

End Sub
 [/CODE]
[/QUOTE]

Verification