records update using visual basic and UPDATE SET statement ACCESS table

Question

Hello There

I am trying to update ACCESS table records using the statement UPDATE SET WHERE and a datagridview as source, the code that I put together seems to be running fine (syntax talking), I say syntax because there is not error or break during execution.

However the issues are:

1.- All the cells are updated with the first row value

2.- Ff the updated cell is for example ROW(2),Column(1) the new value is ignored, it always take the top ROW

Look like the parameters are not updated with the new value when the FOW NEXT iteration runs across the datagridview. I tried different methods to define and update parameters but I always get the same result.

The table key word is ID but when using WHERE ID = @ID the COMMAND.EXECUTENONQUERY return no affected rows.

But when using another field like WHERE quotenumber = '" & Quotenumber_CB.SelectedItem & "' the COMMAND.EXECUTENONQUERY returns 666666.

below is a portion of the ACCESS table and the form with the objects.

Thank you in advance any help.

Form

After update

Diferent scenario

After update

Code:

    Private Sub Up_Existing_quote_BTN_Click(sender As Object, e As EventArgs) Handles Up_Existing_quote_BTN.Click
        Dim dc As New OleDbConnection("provider = microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Salvatore Colaiuda\OneDrive\Documents\SALCO\projects\Visual Studio DB\testing combobox\salco_parts.mdb")
        Dim cm As New OleDbCommand

        With cm
            Try
                .Connection = dc
                .CommandType = CommandType.Text
                ' .CommandText = "UPDATE quotation SET itemnumber = @itemnumber, itemcode = @itemcode,itemdescription = @itemdescription,unit = @unit,matquantity = @matquantity, itemcost = @itemcost, laborcost = @laborcost, totalmatcost = @totalmatcost, discount = @discount WHERE quotenumber = '" & Quotenumber_CB.SelectedItem.ToString & "' "
                For Each itemrow As DataGridViewRow In Quote_view_DG.Rows
                    'For i As Integer = 0 To Quote_view_DG.Rows.Count - 1
                    '.Parameters.AddWithValue("@ID", 172)
                    .Parameters.AddWithValue("@itemnumber", itemrow.Cells(0).Value)
                    .Parameters.AddWithValue("@itemcode", itemrow.Cells(1).Value)
                    .Parameters.AddWithValue("@itemdescription", itemrow.Cells(2).Value)
                    .Parameters.AddWithValue("@unit", itemrow.Cells(3).Value)
                    .Parameters.AddWithValue("@matquantity", itemrow.Cells(4).Value)
                    .Parameters.AddWithValue("@itemcost", itemrow.Cells(5).Value)
                    .Parameters.AddWithValue("@laborcost", itemrow.Cells(6).Value)
                    .Parameters.AddWithValue("@totalmatcost", Val(itemrow.Cells(5).Value) * Val(itemrow.Cells(6).Value))
                    .Parameters.AddWithValue("@discount", itemrow.Cells(8).Value)
                    .CommandText = "UPDATE quotation SET itemnumber = @itemnumber, itemcode = @itemcode" &
                        "itemdescription = @itemdescription,unit = @nit,matquantity = @matquantity" &
                        "itemcost = @itemcost, laborcost = @laborcost, totalmatcost = @totalmatcost" &
                        "discount = @discount WHERE quotenumber = '" & Quotenumber_CB.SelectedItem & "'"
                    dc.Open()
                    '.ExecuteNonQuery()
                    Dim rowaffected As Integer = .ExecuteNonQuery()
                    dc.Close()
                    RichTextBox1.AppendText(rowaffected)
                Next
            Catch ex As System.Exception
                MsgBox(ex.Message, MsgBoxResult.Ok)
                dc.Close()

            End Try
        End With
    End Sub

Answer 1

Hi @salvatore Colaiuda ,

You should use . Parameters.clear() to clear the value of the parameter in the previous loop.

Best Regards.
Jiachen Li
----------
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.