Question Update requires a valid Insert Command

wjburke2

Active member
Joined
Feb 3, 2009
Messages
29
Programming Experience
Beginner
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
 
Firstly, is there actually any reason to be calling Fill? Do you actually need to retrieve data from the database into your app? If not then don't call Fill. Just build the DataTable schema yourself or else call FillSchema to build the schema automatically based on a query without actually retrieving any data.

Secondly, why would an INSERT statement be three pages long? It's going to be something as simple as:
VB.NET:
INSERT INTO MyTable (Column1, Column2, Column3) VALUES (@Column1, @Column2, @Column3)
You simply have to modify the column list appropriately. You don't even have to create that command yourself if you use a command builder.

Is it maybe the fact that you're inserting into two related tables that is worrying you? If so then rest easy. If you do things right then that will be taken care of mostly automatically.

Here's what I think you should do:

1. Learn how to use a TextFieldParser to read the data in. It's going to be far cleaner than what you've got. The MSDN Library documentation for the class includes code examples.

2. Learn how to insert data from a DataTable into a database in bulk. This thread of mine provides an example, as well as other useful ADO.NET examples:

Retrieving and Saving Data in Databases

3. Learn how to create DataRelations in a DataSet. That's going to help you with inserting foreign keys into the database. This thread of mine provides an example, although the use of the relation in this case is different to yours:

Master/Detail (Parent/Child) Data-binding

4. Learn how to update foreign keys in a child DataTable when inserting records from a parent DataTable. This thread of mine shows how to do that for an Access database specifically:

Retrieve Access AutoNumber Value After Insert

Put all that together and you can create a DataSet contain related parent and child DataTables, populate it from files and save the new data to the database.
 
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.
VB.NET:
    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
 
Last edited:
If you post an example of the input file format and the corresponding rows you want inserted, I am pretty sure I can show you a much better, shorter, and easier way to accomplish what you need...
 
Thank you that would be great. I am only concerned about the notification file on pages 26-28 at this time. Whatever I come up with it has to be easy to maintain because they change the layout every few years and I have to be able to identify the fields and make the proper modifications. I have included a link to the technical guide and sample data.The latest changes will add about 50 new fields to this layout.


https://ribbs.usps.gov/acs/documents/tech_guides/OneCodeACSTechnicalGuide.pdf

https://ribbs.usps.gov/acs/documents/tech_guides/ONECTST4.ZIP
 
Last edited by a moderator:
Neither of the links work, but in any case all I need is an example of a couple of lines off the input file, and the table and field names for the insert.
 
Sorry, Dont know why that didn't work but this time I am attempting to attach a sample zip with the database, Tech Guide, and sample data. You have my code above. Thanks Again
 

Attachments

  • ACSSample.zip
    753.9 KB · Views: 33
Back
Top