0

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
Perry 59
  • 35
  • 5
  • When you say that the modified update commands didn't work, what exactly does that mean. Are you getting exceptions or unexpected results? – Hursey Jan 21 '21 at 20:02
  • I was getting exceptions and the database was not updated. You can see in my code comments above where I tried to place my variables into a copy of the sql that was generated by the command builder – Perry 59 Jan 21 '21 at 21:06
  • So, what was the exception and on which line exactly are you encountering it? – Hursey Jan 21 '21 at 21:10
  • I get the "no value given for one or more parameters" error. it occurs on the line that invokes the update "count = _partsDataAdapter.Update(_PartsDataTable)" – Perry 59 Jan 21 '21 at 22:01
  • No one should have to read the comments to understand the question. If an exception is thrown then you should have stated that and provided the error message in the question to begin with. Edit your question and provide ALL the relevant information now. – jmcilhinney Jan 21 '21 at 23:50
  • I thought the question was pretty, not for everyone I guess – Perry 59 Jan 22 '21 at 16:36
  • 1
    Set the columns in question to read only in the DataGridView. When the OK button is clicked get a collection of the updated rows and add the user and date data programmatically to the datatable before you call .Update on the dataadapter. No need for custom Update statement. – Mary Jan 24 '21 at 22:11
  • I knew that I could manually change the data table before calling update and it would work. I guess that I will just look for any "dirty" rows in the DGV and update those before calling update. Thanks – Perry 59 Jan 25 '21 at 20:47

0 Answers0