sql - modify oledb data adapter update command

Question

I am using an oledb data adapter to update an access database with information displayed in (and possibly edited) a datagridview on a form. The stock update command (from commandbuilder) does work and update the data that has been modified. However, there are two columns in the data table called "updatedBy" and "updatedDate" which I want to always be filled in with the current user and current date. I do not want the user to enter this info and possibly put in bogus values like "me" and "now". So my question is, how can I modify the update command to do this on any rows that changed? I tried making a copy of the sql generated by the command builder and simply inserting my variables for these items but that did not work. Below is the code for my "ok" button, you can see some of the things I tried. Sorry for the horrendously long lines of sql that I copied from the command builder, I hope it is legible and makes sense. Thanks for any pointers.

Private Sub OkButton_Click(sender As Object, e As EventArgs) Handles OkButton.Click

        Dim count As Integer = 0
        Dim sqlcommand As String = String.Empty

        'if there are "dirty" rows, manually update the "updated" fields in the DGV before calling update command

        If _DGVchanged Then
            Using con As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & VE_docPath & VE_currentProject & ".accdb;Persist Security Info=True")
                Using cmd As New OleDb.OleDbCommand("", con)
                    Try
                        cmd.CommandText = "(SELECT * FROM PartDefinitions)"
                        cmd.CommandType = CommandType.Text
                        cmd.Connection = con

                        con.Open()
                        _partsDataAdapter.SelectCommand = cmd
                        'command text extracted update command, works ok
                        sqlcommand = "UPDATE PartDefinitions SET PartClass = ?, PartNumber = ?, PartNumberAlt = ?, Description = ?, ShellType = ?, ElementCount = ?, TerminalCount = ?, KitNumber = ?, StockNumber = ?, WiringPartName = ?, ArticleName = ?, Manufacturer = ?, ManufacturerID = ?, Supplier = ?, FamilyCode = ?, UseVoltage = ?, CoVoltage = ?, UseFrequency = ?, CoFrequency = ?, IsObsolete = ?, Series = ?, SymbolName = ?, PartType = ?, Weight = ?, WeightUnits = ?, CostEach = ?, ConnectionType = ?, MaxSection = ?, MaxGuage = ?, MinSection = ?, MinGauge = ?, InsertedBy = ?, InsertedDate = ?, UpdatedBy = ?, UpdatedDate = ? WHERE ((PartDefID = ?) And ((? = 1 And PartClass Is NULL) Or (PartClass = ?)) And ((? = 1 And ElementCount Is NULL) Or (ElementCount = ?)) And ((? = 1 And TerminalCount Is NULL) Or (TerminalCount = ?)) And ((? = 1 And ManufacturerID Is NULL) Or (ManufacturerID = ?)) And ((? = 1 And IsObsolete Is NULL) Or (IsObsolete = ?)) And ((? = 1 And PartType Is NULL) Or (PartType = ?)) And ((? = 1 And Weight Is NULL) Or (Weight = ?)) And ((? = 1 And CostEach Is NULL) Or (CostEach = ?)) And ((? = 1 And MaxSection Is NULL) Or (MaxSection = ?)) And ((? = 1 And MaxGuage Is NULL) Or (MaxGuage = ?)) And ((? = 1 And MinSection Is NULL) Or (MinSection = ?)) And ((? = 1 And MinGauge Is NULL) Or (MinGauge = ?)) And ((? = 1 And InsertedDate Is NULL) Or (InsertedDate = ?)) And ((? = 1 And UpdatedDate Is NULL) Or (UpdatedDate = ?)))"
                        'tried to insert my own value, does NOT work
                        sqlcommand = "UPDATE PartDefinitions SET PartClass = ?, PartNumber = ?, PartNumberAlt = ?, Description = ?, ShellType = ?, ElementCount = ?, TerminalCount = ?, KitNumber = ?, StockNumber = ?, WiringPartName = ?, ArticleName = ?, Manufacturer = ?, ManufacturerID = ?, Supplier = ?, FamilyCode = ?, UseVoltage = ?, CoVoltage = ?, UseFrequency = ?, CoFrequency = ?, IsObsolete = ?, Series = ?, SymbolName = ?, PartType = ?, Weight = ?, WeightUnits = ?, CostEach = ?, ConnectionType = ?, MaxSection = ?, MaxGuage = ?, MinSection = ?, MinGauge = ?, InsertedBy = ?, InsertedDate = ?, UpdatedBy = '" & VE_currentUser & "', UpdatedDate = '" & DateTime.Now & "' WHERE ((PartDefID = ?) And ((? = 1 And PartClass Is NULL) Or (PartClass = ?)) And ((? = 1 And ElementCount Is NULL) Or (ElementCount = ?)) And ((? = 1 And TerminalCount Is NULL) Or (TerminalCount = ?)) And ((? = 1 And ManufacturerID Is NULL) Or (ManufacturerID = ?)) And ((? = 1 And IsObsolete Is NULL) Or (IsObsolete = ?)) And ((? = 1 And PartType Is NULL) Or (PartType = ?)) And ((? = 1 And Weight Is NULL) Or (Weight = ?)) And ((? = 1 And CostEach Is NULL) Or (CostEach = ?)) And ((? = 1 And MaxSection Is NULL) Or (MaxSection = ?)) And ((? = 1 And MaxGuage Is NULL) Or (MaxGuage = ?)) And ((? = 1 And MinSection Is NULL) Or (MinSection = ?)) And ((? = 1 And MinGauge Is NULL) Or (MinGauge = ?)) And ((? = 1 And InsertedDate Is NULL) Or (InsertedDate = ?)) And ((? = 1 And UpdatedDate Is NULL) Or (UpdatedDate = ?)))"
                        'this simplified version didnt work either
                        sqlcommand = "UPDATE PartDefinitions Set PartClass = ?, PartNumber = ?, PartNumberAlt = ?, Description = ?, ShellType = ?, ElementCount = ?, TerminalCount = ?, KitNumber = ?, StockNumber = ?, WiringPartName = ?, ArticleName = ?, Manufacturer = ?, ManufacturerID = ?, Supplier = ?, FamilyCode = ?, UseVoltage = ?, CoVoltage = ?, UseFrequency = ?, CoFrequency = ?, IsObsolete = ?, Series = ?, SymbolName = ?, PartType = ?, Weight = ?, WeightUnits = ?, CostEach = ?, ConnectionType = ?, MaxSection = ?, MaxGuage = ?, MinSection = ?, MinGauge = ?, InsertedBy = ?, InsertedDate = ?, UpdatedBy = '" & VE_currentUser & "', **UpdatedDate = '" & DateTime.Now & "'**  WHERE ((PartDefID = ?) And (PartClass = ?) And (ElementCount = ?) And (TerminalCount = ?) And (ManufacturerID = ?) And (IsObsolete = ?)) And (PartType = ?) And (Weight = ?) And (CostEach = ?) And (MaxSection = ?) And (MaxGuage = ?) And (MinSection = ?) And (MinGauge = ?) And (InsertedDate = NULL) And (UpdatedDate = NULL))"

                        '_partsDataAdapter.UpdateCommand = New OleDb.OleDbCommand(sqlcommand, con)
                        _partsDataAdapter.UpdateCommand = New OleDbCommandBuilder(_partsDataAdapter).GetUpdateCommand()

                        'debugging tool to see what the command text is
                        Dim test As String = _partsDataAdapter.UpdateCommand.CommandText


                        'I can manually edit rows in the data table and it will work, but first have to determine which rows have been edited in the DGV.
                        '_PartsDataTable.Rows(0).Item("UpdatedBy") = "joe kidd"


                        Me.Validate()
                        Me.BindingSource1.EndEdit()

                        count = _partsDataAdapter.Update(_PartsDataTable) 'get number of affected rows
                        MessageBox.Show("Updated " & count & "rows")

                    Catch SqlError As System.Data.SqlTypes.SqlTypeException
                        MsgBox(SqlError.Message, MsgBoxStyle.OkOnly, "Error")

                    Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Error")
                    End Try
                End Using
            End Using
        End If

        Me.Close()
        Me.Dispose()
    End Sub

question from:https://stackoverflow.com/questions/65831685/modify-oledb-data-adapter-update-command

Answer 1

Waitting for answers