Inserting data into database with autonumber

[JL.19]

I'm trying to insert data into a database with an autonumber in MS Access as primary key. I get an error saying "Number of query values and destination fields are not the same. Here's my code:

For Each row As DataGridViewRow In DataGridView1.Rows
            Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Daily Inventory.accdb;"

            Using conn As New OleDbConnection(connString)
                Using cmd As New OleDbCommand("Insert into Table1 Values(@OrderNo, @Product, @Qty, @Price, @TDate)", conn)
                    cmd.Parameters.AddWithValue("@OrderNo", TxtOrder.Text.ToString)
                    cmd.Parameters.AddWithValue("@Product", row.Cells("Product").Value)
                    cmd.Parameters.AddWithValue("@Qty", row.Cells("Qty").Value)
                    cmd.Parameters.AddWithValue("@Price", row.Cells("Price").Value)
                    cmd.Parameters.AddWithValue("@TDate", Date.Now.ToString("MM/dd/yyyy"))

                    If conn.State = ConnectionState.Open Then
                        conn.Close()
                    End If

                    conn.Open()
                    cmd.ExecuteNonQuery()
                    conn.Close()
                End Using
            End Using
        Next

[dbasnett]

Is one of the parameters the auto number field? If so remove it and the insert should work.

[si_the_geek]

You should also specify the names of the fields, eg:

  Insert into Table1 (field1, field2, field3, ...)   Values(value1, value2, value3, ...)

Doing that avoids a wide variety of issues, including ones like the one you are having.

[jmcilhinney]

If you omit the column names in an INSERT statement then you MUST specify values for all column in the correct order. If the database is generating a value for a column then you MUST omit that column value. See how those two things are mutually exclusive? ALWAYS specify the columns regardless, because it makes your code clearer.

[JL.19]

I have resolved the issue. Turns out I didn't set the AllowUserToAddRows to false. Thank you for all the help!

[jmcilhinney]

There are other unrelated problems with your code. If you were going to loop through the rows and save them one by one then you absolutely should not be opening and closing the connection each time. You should be opening the connection once first, saving all the data and then closing the connection.

That said, you shouldn't be loop through the rows anyway. Create a DataTable and bind that to the grid. You can then save all the data changes (inserts, updates and deletes) in one go by calling Update on a data adapter. That method will implicitly open and close the connection for you.