question

JamesBuss avatar image
0 Votes"
JamesBuss asked TianyuSun-MSFT edited

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

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?

dotnet-visual-basicdotnet-adonet
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx avatar image
0 Votes"
cooldadtx answered JamesBuss commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The solution has two projects that both use the same dataset. I realized I was looking at the wrong one when I was going through the mind-numbing PeopleDataSet.Designer.vb code and realized that there really was no Insert command. Confused as to why the backend code would not have it but the Properties window would, I closed it all down and re-opened it, and that's when I realized I had been looking at the Designer for project #2 when I was supposed be looking at project #1 to see why the error was occurring.

So in a round about way, you were on the right track.

0 Votes 0 ·
JamesBuss avatar image
0 Votes"
JamesBuss answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.