Results 1 to 6 of 6

Thread: Inserting data into database with autonumber

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Oct 2021
    Posts
    19

    Inserting data into database with autonumber

    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:

    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

  2. #2
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Pointless Forest 38.517,-92.023
    Posts
    9,470

    Re: Inserting data into database with autonumber

    Is one of the parameters the auto number field? If so remove it and the insert should work.
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,823

    Re: Inserting data into database with autonumber

    You should also specify the names of the fields, eg:
    Code:
      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.

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    108,509

    Re: Inserting data into database with autonumber

    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.

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Oct 2021
    Posts
    19

    Re: Inserting data into database with autonumber

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

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    108,509

    Re: Inserting data into database with autonumber

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width