Read from CSV file, write directly to data source

[randman1]

I'm a beginner to VB2008 and trying to "convert" an Access DB to a stand alone application. I have under estimated the differences between VBA and VB. Although I have many questions, I'll focus on this topic first.

The Access DB collects and maintains SNMP traps from a radio station. I import CSV files from a remote Unix server via FTP, parse the text line-by-line, and add to a table as a new record. So far, I can parse the text and add to a dataset but I want to write directly to the data source. I also need error handling that will trap errors for duplicate records. There is a Reconcile function that ensures that all lines in the CSV files are added to the table before the files are permanently deleted. Here's what I have so far:

Public Class clsRnm

    Private msTrapTable As String
    Private moTrap As clsRnmTrap
    Private moAppendStat As clsTrapFileAppendStat
    Private mlSystemID As Long

    Public Sub New(ByVal lSystemID As Long)
        mlSystemID = lSystemID
        moTrap = New clsRnmTrap
        moAppendStat = New clsTrapFileAppendStat
    End Sub


    Public Function AppendTrapFile(ByVal sFullPath As String) As clsTrapFileAppendStat
        Dim oSR As StreamReader
        Dim oTA As New RnmDataSetTableAdapters.tblRnmTrapsTableAdapter
        Dim oStats As New clsTrapFileAppendStat
        Dim iOK, iBad, iDup As Integer

        On Error GoTo ErrProc

        If My.Computer.FileSystem.FileExists(sFullPath) = False Then
            oStats.Message = "Error: File Not Found"
            GoTo ExitProc
        End If

        oStats.FileName = My.Computer.FileSystem.GetName(sFullPath)
        oSR = New StreamReader(sFullPath)

        Do Until oSR.EndOfStream
            moTrap = SplitTrapLine(oSR.ReadLine)

            If moTrap.EventType = "A" Then
                If moTrap.Err = 0 Then
                    With moTrap
                        oTA.Insert( _
                            ArrivalID:=.ArrivalID, _
                            SystemID:=mlSystemID, _
                            TimeStamp:=.TimeStamp, _
                            EventDate:=.EventDate, _
                            ObjectName:=.ObjectName, _
                            LevelID:=.LevelID, _
                            Message:=.Message, _
                            ObjectClass:=.ObjectClass, _
                            RegionID:=.RegionID, _
                            SiteID:=.SiteID)
                    End With
                    iOK = iOK + 1
                Else
                    'An error was encountered in the SplitTrapLine function
                    iBad = iBad + 1
                End If
            End If

DuplicateEntryResume:
        Loop

        oStats.ErrNum = 0
        oStats.Message = "Complete: " & oStats.FileName & " " & iOK & "/" & iDup & "/" & iBad
        oStats.EventsAdded = iOK
        AppendStat.InvalidLines = iBad
        oStats.DuplicateEvents = iDup

ExitProc:
        AppendTrapFile = oStats
        MsgBox(AppendTrapFile.Message)
        Exit Function

ErrProc:
        Select Case Err.Number
            Case 5
                ' Error triggered on .ExecuteNonQuery when ArrivalID is a duplicate
                iDup = iDup + 1
                Resume DuplicateEntryResume
            Case Else
                oStats.ErrNum = Err.Number
                oStats.Message = "Error: " & Err.Number & " - " & Err.Description
                GoTo ExitProc
        End Select

    End Function



    Private Function SplitTrapLine(ByVal sTrapLine As String) As clsRnmTrap
        Dim sTrapElement() As String

        On Error GoTo ErrProc

        sTrapElement = Split(sTrapLine, ",")
        With moTrap
            .EventType = Trim(sTrapElement(0))
            .TimeStamp = Trim(sTrapElement(1))
            .EventDate = Trim(sTrapElement(2))
            .ObjectName = Trim(sTrapElement(3))
            .LevelID = Trim(sTrapElement(4))
            .Message = Trim(sTrapElement(5))
            .ObjectClass = Trim(sTrapElement(6))
            .RegionID = Trim(sTrapElement(7))
            .SiteID = Trim(sTrapElement(8))
            .AocID = Trim(sTrapElement(9))
            .TrapID = Trim(sTrapElement(10))
            .SequenceNum = Trim(sTrapElement(11))
            .Host = Trim(sTrapElement(12))
            .User = Trim(sTrapElement(13))
            .ArrivalTime = Trim(sTrapElement(14))
            .ArrivalID = Trim(sTrapElement(15))
            .Err = 0
        End With

        SplitTrapLine = moTrap
        Exit Function

ErrProc:
        moTrap.Err = Err.Number

    End Function

End Class

Other suggestions that may help with technique are welcome.

[randman1]

I think I figured it out.

    Public Function AppendTrapFile(ByVal sFullPath As String) As clsTrapFileAppendStat
        Dim sConnStr As String
        Dim sSql As String
        Dim sDataSource As String
        Dim oSR As StreamReader
        Dim iOK, iBad, iDup As Integer

        If My.Computer.FileSystem.FileExists(sFullPath) = False Then
            moAppendStat.Message = "Error: File Not Found"
            AppendTrapFile = moAppendStat
        End If

        'Test for valid SystemID here

        sDataSource = "C:\Users\Randy\Documents\MS Access\RNM\rnm_Data_2011-08-12.mdb"
        sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDataSource
        sSql = "INSERT INTO tblRnmTraps (ArrivalID, SystemID, [TimeStamp], EventDate, ObjectName, " & _
        "LevelID, Message, ObjectClass, RegionID, SiteID) " & _
        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

        Try
            Dim cn As New OleDbConnection(sConnStr)
            Dim cmd As New OleDbCommand(sSql, cn)

            moAppendStat.FileName = My.Computer.FileSystem.GetName(sFullPath)
            oSR = New StreamReader(sFullPath)

            Do Until oSR.EndOfStream
                moTrap = SplitTrapLine(oSR.ReadLine)

                If moTrap.EventType = "A" Then
                    If moTrap.Err = 0 Then
                        cmd.Parameters.Add("ArrivalID", OleDbType.Char).Value = moTrap.ArrivalID
                        cmd.Parameters.Add("SystemID", OleDbType.Integer).Value = mlSystemID
                        cmd.Parameters.Add("TimeStamp", OleDbType.Integer).Value = moTrap.TimeStamp
                        cmd.Parameters.Add("EventDate", OleDbType.Date).Value = moTrap.EventDate
                        cmd.Parameters.Add("ObjectName", OleDbType.Char).Value = moTrap.ObjectName
                        cmd.Parameters.Add("LevelID", OleDbType.Integer).Value = moTrap.LevelID
                        cmd.Parameters.Add("Message", OleDbType.Char).Value = moTrap.Message
                        cmd.Parameters.Add("ObjectClass", OleDbType.Char).Value = moTrap.ObjectClass
                        cmd.Parameters.Add("RegionID", OleDbType.Integer).Value = moTrap.RegionID
                        cmd.Parameters.Add("SiteID", OleDbType.Integer).Value = moTrap.SiteID

                        cn.Open()
                        Try
                            'Test for duplicate record in table
                            cmd.ExecuteNonQuery()
                            iOK = iOK + 1
                        Catch ex As OleDbException When ex.TargetSite.Name = "ExecuteCommandTextErrorHandling"
                            'Duplicate record exists
                            iDup = iDup + 1
                        Finally
                            cn.Close()
                        End Try
                    Else
                        'An error was encountered in the SplitTrapLine function
                        iBad = iBad + 1
                    End If
                End If

            Loop
            moAppendStat.Message = "Complete: " & moAppendStat.FileName & " " & iOK & "/" & iDup & "/" & iBad
            moAppendStat.ErrNum = 0
        Catch ex As OleDbException
            MsgBox(ex.TargetSite.Name)
        Catch ex As InvalidOperationException
            MsgBox(ex.TargetSite.Name)
        Finally
            moAppendStat.EventsAdded = iOK
            moAppendStat.InvalidLines = iBad
            moAppendStat.DuplicateEvents = iDup
            AppendTrapFile = moAppendStat
            MsgBox(AppendTrapFile.Message)
        End Try

    End Function