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?
VB.NET:
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