Inserting data into database with autonumber in VB.net-CodePudding

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. The data types in MS Access are Autonumber (I didn't include it in the INSERT statement), String (@OrderNo), String (@Product), Number (@Qty), and Date (@TDate). Here's the image:

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, @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("@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

CodePudding user response：

You need to change your sql to "Insert into Table1 (OrderNo,Product,Qty,TDate) Values(@OrderNo, @Product, @Qty, @TDate)".

The following code works for me.

    DataGridView1.AllowUserToAddRows = False
    For Each row As DataGridViewRow In DataGridView1.Rows
        Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=...;"
        Using conn As New OleDbConnection(connString)
            Using cmd As New OleDbCommand("Insert into Table1 (OrderNo,Product,Qty,TDate) Values(@OrderNo, @Product, @Qty, @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("@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

CodePudding user response：

Well, I think we dealing with a bit of a chicken and a egg problem here?

I mean, how did the grid get created?

How and where did you setup the columns in the grid?

lets drop a data grid view into a form. Drop in a button.

We have this code to load up the grid:

Private Sub HotelGridEdit_Load(sender As Object, e As EventArgs) Handles Me.Load

    LoadGrid()

End Sub

Sub LoadGrid()

    Using conn As New OleDbConnection(My.Settings.AccessDB)

        Dim strSQL As String =
            "SELECT ID, FirstName, LastName, City, HotelName, Description, Active FROM tblHotelsA 
            ORDER BY HotelName"
        Using cmdSQL As New OleDbCommand(strSQL, conn)

            conn.Open()
            Dim rstData As New DataTable
            rstData.Load(cmdSQL.ExecuteReader)
            DataGridView1.DataSource = rstData

        End Using
    End Using


End Sub

So, now we have this:

Now, in a above, I can cursor around - make any edits I want.

I can also type in on the bottom blank row to add new rows.

I can click on the left side recordselector, and hit delete key.

Now, to save my edits, save my addtitions, and save my deletes?

I have the one button at the top, and this code works to do all updates, all addtitions, and all deletes.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    ' save all edits, and new rows, and deletes
    Using conn As New OleDbConnection(My.Settings.AccessDB)

        Dim strSQL As String =
            "SELECT ID, FirstName, LastName, City, HotelName, Description, Active FROM tblHotelsA"
        Using cmdSQL As New OleDbCommand(strSQL, conn)
            conn.Open()
            Dim da As New OleDbDataAdapter(cmdSQL)
            Dim daC As New OleDbCommandBuilder(da)
            da.Update(DataGridView1.DataSource)

        End Using
    End Using
End Sub

So, not a lot of code.

In fact, often using a data table is a lot less work, since you don't have to mess with all those parameters anyway - even if your data grid was somehow not feed from the database. But, if you feeding the "grid" from the database, then really, you can just add rows - and then write some code to send the changes back to the database with the above code. And if you want to start out with a blank grid - not show previous rows, then just add to your sql like this:

SELECT * from tblHotels WHERE ID = 0 Order by Hotelname

Use above to create a data table without any rows but STILL bind that to the grid, and thus once again, to add, or delete rows, you just edit and add them, and again the SAME code I had above will work to save edits, deletes and additions.