Reply to thread

Message

[QUOTE="wjburke2, post: 167612, member: 21091"]
I am new to VB.Net and am trying to migrate a Access application. I have text file(s) I need to read and use to updated an access database. I did some research and came up with the code below. I am connected to the database and cycle through the records I assume updateing the dataset in my application but when I get to da.Update(ds, "rstHeader") it blows with "Update requires a valid Insert Command when passed DataRow collection with new rows." Everything I read about this error points to using a Insert command I am not thrilled about writing that command it going to be three pages long and terrible to maintain. Can I use this code or do I need a total rewrite?

[CODE]    
Public Sub ParseOneCodeFiles(ByVal InFileName As String)

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

Dim da As New OleDb.OleDbDataAdapter
        Dim ds As New DataSet
        Dim dt As New DataTable

'   ***********************************************
        '   ****         Create Work Tables             ***
        '   ***********************************************
        strSQL = "SELECT * FROM RecordHeader"
        da.SelectCommand = New OleDb.OleDbCommand(strSQL, conn)
        da.Fill(ds, "rstHeader")
        dt = ds.Tables(0)

strSQL = "SELECT * FROM SingleSrc_Temp"
        da.SelectCommand = New OleDb.OleDbCommand(strSQL, conn)
        da.Fill(ds, "rstSSrc")
        dt = ds.Tables(0)

'   ***********************************************
        '   ****         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 DataRow = ds.Tables("rstHeader").NewRow()
                    dsNewRow.Item("MailerID") = CStr(Trim(Mid(strread, 2, 6)))
                    dsNewRow.Item("FileName") = strFileName
                    dsNewRow.Item("CreateDate") = CStr(Mid(strread, 8, 8))
                    strCreateDate = CStr(Mid(strread, 8, 8))
                    dsNewRow.Item("TotalACS") = CInt(Trim(Mid(strread, 16, 9)))
                    lngTotalACS = CInt(Trim(Mid(strread, 16, 9)))
                    dsNewRow.Item("TotalCOA") = CInt(Trim(Mid(strread, 25, 9)))
                    dsNewRow.Item("TotalNIXIE") = CInt(Trim(Mid(strread, 34, 9)))
                    dsNewRow.Item("ShipmentNum") = CStr(Trim(Mid(strread, 43, 8)))
                    dsNewRow.Item("Class") = CStr(Trim(Mid(strread, 51, 1)))
                    dsNewRow.Item("MediaType") = CStr(Trim(Mid(strread, 52, 1)))
                    dsNewRow.Item("EntryType") = "S"
                    ds.Tables("rstHeader").Rows.Add(dsNewRow)

ElseIf "2" = Mid(strread, 1, 1) Then
                    totalread = totalread + 1
                    Dim dsNewRow As DataRow = ds.Tables("rstSSrc").NewRow()

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

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

dsNewRow.Item("AccumCCYYMMDD") = Now.ToString("yyyyMMdd")
                    dsNewRow.Item("ProcDate") = "00000000"
                    dsNewRow.Item("EntryType") = "S"
                    ds.Tables("rstSSrc").Rows.Add(dsNewRow)

Else
                    MsgBox("This is not a valid record type" & strread)
                End If
            End If

End While

'update the database with the new rows
        da.Update(ds, "rstHeader")
        da.Update(ds, "rstSSrc")

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

ParseOneCodeFiles_exit:

Exit Sub

End Sub
[/CODE]
[/QUOTE]

Verification