records update using visual basic and UPDATE SET statement ACCESS table

salvatore Colaiuda 0 Reputation points
2023-01-15T21:31:17.18+00:00

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

User's image

User's image

After update

User's image

Diferent scenario

User's image

After update

User's image

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
VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
1,843 questions
.NET
.NET
Microsoft Technologies based on the .NET software framework.
638 questions
No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Jiachen Li-MSFT 10,911 Reputation points Microsoft Employee
    2023-01-16T09:16:25.4766667+00:00

    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.