Update requires a valid InsertCommand when passed DataRow collection with new rows

Question

I have read lots of posts about this error message, but none of them seem to address my problem. I've done this lots of times, but this time it's not working.

In my app I have a DataGridView to edit a data table in a MS Access database. I added the data table in the Dataset Designer. It added all the Select, Insert, Delete, and Update commands. I've inspected the properties and they are all there, properly constructed. The ID field is set as the PrimaryKey in the Access database. There are no other indexes set. In the Designer, I verified that the ID field has AutoIncrement = True, AutoIncrementSeed = 0, AutoIncrementStep = 1.

On my form I added the DataGridView and I dragged the data table to the DataGridView to connect the data table as the DataGridView's DataSource. I verified in the TableAdapterManager properties that the data table was connected. The relevant code looks like this:

Private Sub SelectTab()  
    LstAttendanceEventsTableAdapter.Fill(PeopleDataSet.lstAttendanceEvents)  
    LstAttendanceEventsBindingSource.Sort = "ID"  
End Sub  
  
Private Function SavePeople() As Boolean  
    Cursor = Cursors.WaitCursor  
    Dim Success As Boolean = False  
    Try  
        Validate()  
        LstAttendanceEventsBindingSource.EndEdit()  
        LstMaritalStatusesBindingSource.EndEdit()  
        LstMemberTypesBindingSource.EndEdit()  
        LstRelationshipsBindingSource.EndEdit()  
        PeopleTableAdapterManager.UpdateAll(PeopleDataSet)  
        Success = True  
    Catch ex As Exception  
        MessageBox.Show(ex.Message, My.Application.Info.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error)  
        Success = False  
    End Try  
    SavePeople = Success  
    Cursor = Cursors.Default  
End Function  

When the UpdateAll command is called, that's when I get the error message "Update requires a valid InsertCommand when passed DataRow collection with new rows."

I put a breakpoint on the UpdateAll command line and used a watch to look at LstAttendanceEventsTableAdapter. There is a Select, Update, and Delete command but the InsertCommand is null.

This makes no sense to me. If the Designer has an Insert command, then why is there no InsertCommand in the form?

I've tried deleting and re-adding the TableAdapter and BindingSource on the form. I've tried deleting and re-adding the data table in the Designer. I've tried a brand new solution and it works perfectly, so it's not the Access database.

What is going on and how do I fix it?

Answer 1

In my experience the issue is that the designer couldn't figure out a valid insert command. It is also possible you accidentally deleted it. A table adapter is auto-generated code to wrap the underlying data adapter that is used around your strongly typed dataset. Go to the definition of your table adapter and you should see in that code where it is configuring the commands. This should help shed light on why the insert command isn't set.

Another possibility might be that the table itself is fine but you're dataset is causing additional data to be inserted into other tables and there are no insert commands for them. This is most likely a bad value in your data table that you might not have realized was there and the adapter cannot generate an insert for it so it'll work.

If none of that helps then please share with us the relevant table adapter logic and your table structure in code.

Answer 2

UPDATE: User error.

My solution has two projects in it. Both projects use the same dataset (but for different reasons, hence two projects). The project I was testing actually doesn't have an Insert command for that table adapter. The second project does. When I was trying to track down the error, I got confused and kept looking at the Dataset Designer for project #2 instead of project #1.

I discovered my mistake when I closed down everything and reopened the Dataset Designer for project #1 and inspected the properties. I had the Designer configure the table adapter again and it generated an Insert statement this time. And now all works as expected.

Well, that was a waste of an afternoon.