On a update (There is already a parameter with the name '@xxxxxxx' in this collection.)

Question

I am using VB.NET, Visual Studio 2022, Win 10, MySQLConnector.dll (2.2.6.0) I am trying to integrate MySQL into a framework that is a wrapper for ADO.net. Let me try and create some context.

This framework started out as a wrapper for AccessDB, then SQL Server was added. Next came SQLite, CSV, XLS, AS400, etc. There are a series of case statements that build a connect string for the connection. Then the needed 'DbProviderFactories' is created. Then a 'DataTable'(dt) is built 'DbCommand'(cmd)->'ExecuteReader'(reader as cmd.ExecteReader) ->dt.Load(reader).

Main code

    Dim tmp As clsDataTable = DataAccessIris.GetDT2("select * from ONLINEINFOAUX", "tmp", "MYSqlConnector",,, eErrReturn.Exception)
    DispThis("Tmp: " & tmp.RowCount.ToString & NewLine)
    tmp.LocalWalkFindRow(New Dictionary(Of String, Object) From {{"common_item_number", 50}})
    tmp.Rows(tmp.LocalWalkFindRecordNum)("ONLINE_DESCRIPTION") = "test"
    tmp.UpdateData()

clsDataTable is based off .NET DataTable '.LocalWalkFindRow' Is a function that loops through rows looking for a match and sets the property 'LocalWalkFindRecordNum'.

All these work and the table is loaded. I can add and delete records. I can access the rows and fields. I can update a Row/Field. If I display the Row/Field I can see the change. But when I try and update the table, I get what is stated in the subject "error where @xxxxx" is the name of the field I was trying to change.

The update is something like ... _da as Common.DbDataAdapter from what was created via DbProviderFactories._da.Update(dt)

Here is the update function. The "DataAccessIris" class has an open statement. Once open, it is stored in a data dictionary of a type (String, DbDataAdapter). The variables passed create a key so if the DbDataAdapter existed, it is just returned, if not then it will be built, stored, then returned.

        Public Sub UpdateData(Optional pSource As String = Nothing, Optional pDBnum As String = Nothing, Optional pStnum As String = Nothing, Optional DoUpdateEvent As Boolean = False)
            If pSource Is Nothing Then pSource = Me._Source
            If pDBnum Is Nothing Then pDBnum = Me._dbName
            If pStnum Is Nothing Then pStnum = Me._stNum


            Using _da As Common.DbDataAdapter = DataAccessIris.DBAdapter(pSource, pDBnum, pStnum, Me.Parms)
                If TypeOf _da Is SqlDataAdapter Then
                    AddHandler CType(_da, SqlDataAdapter).RowUpdating, AddressOf DA_RowUpdating
                    If DoUpdateEvent Then AddHandler CType(_da, SqlDataAdapter).RowUpdated, AddressOf DA_RowUpdated
                    'ElseIf TypeOf _da Is SQLiteDataAdapter Then
                    '    AddHandler CType(_da, SQLiteDataAdapter).RowUpdating, AddressOf DA_RowUpdating
                    '    If DoUpdateEvent Then AddHandler CType(_da, SQLiteDataAdapter).RowUpdated, AddressOf DA_RowUpdated
                ElseIf TypeOf _da Is Data.Odbc.OdbcDataAdapter Then
                    AddHandler CType(_da, Odbc.OdbcDataAdapter).RowUpdating, AddressOf DA_RowUpdating
                    If DoUpdateEvent Then AddHandler CType(_da, Odbc.OdbcDataAdapter).RowUpdated, AddressOf DA_RowUpdated
                ElseIf TypeOf _da Is Data.OleDb.OleDbDataAdapter Then
                    AddHandler CType(_da, OleDb.OleDbDataAdapter).RowUpdating, AddressOf DA_RowUpdating
                    If DoUpdateEvent Then AddHandler CType(_da, OleDb.OleDbDataAdapter).RowUpdated, AddressOf DA_RowUpdated
                End If
                If DoUpdateEvent Then
                    _rowsToUpdate = 0
                    _rowsUpdated = 0
                    For xloop As Integer = 0 To Me.RowCount - 1
                        If Me.Rows(xloop).RowState <> DataRowState.Unchanged Then _rowsToUpdate += 1
                    Next
                End If
                Dim _trans As DbTransaction = Nothing
                If _da.ToString = "System.Data.SQLite.SQLiteDataAdapter" Then
                    _trans = CType(DataAccessIris.OpenDb(pDBnum, pStnum).BeginTransaction, DbTransaction)
                End If
                _da.Update(Me)
                If _trans IsNot Nothing Then
                    _trans.Commit()
                    _trans.Dispose()
                End If
                If Me.Parms IsNot Nothing Then
                    For x As Integer = 0 To Me.Parms.Count - 1
                        _da.SelectCommand.Parameters.Remove(Me.Parms(x))
                    Next
                End If
            End Using

            blnSkipRowChange = True
            Me.AcceptChanges()
            MyBase.AcceptChanges()
            blnSkipRowChange = False
            SetInit()
        End Sub

This Pastebin link is to the update query that was built https://pastebin.com/Mh1ESe0f

I can give more detail, but I did not want to make the post any more confusing than I already did. :-)

Just let me know what might help make it more clear.

EDIT 7/31/2023 to clear up where parameters are being built. Also adding the System.Data and ADO.Net tags for more context. DBAdapter is a wrapper for the System.DataCommon.DbDataAdapter. Here is the code ...

       Public Shared Function DBAdapter(ByVal CommandText As String,
                      Optional ByVal DataBaseName As String = "",
                      Optional ByVal StoreNumber As String = "",
                      Optional ByVal Prams() As DbParameter = Nothing) As DbDataAdapter
        Dim da As DbDataAdapter = Nothing
        Try
            DataBaseName = DataBaseName.ToUpper
            Dim conName As String = DataBaseName & StoreNumber
            Dim cmd As DbCommand = Nothing
            If Prams Is Nothing Then
                cmd = DBCommand(CommandText, DataBaseName, StoreNumber)
            Else
                cmd = DBCommand(CommandText, Prams, DataBaseName, StoreNumber)
            End If

            'If DataBaseName.ToUpper.EndsWith(".SQLITE") Then
            '    da = New SQLiteDataAdapter
            'Else
            da = DbProviderFactories.GetFactory(dProviderTypes(conName)).CreateDataAdapter
            'End If

            da.SelectCommand = cmd
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey
            Dim cmdBuild As DbCommandBuilder
            'If DataBaseName.ToUpper.EndsWith(".SQLITE") Then
            '    cmdBuild = New SQLiteCommandBuilder
            'Else
            cmdBuild = DbProviderFactories.GetFactory(dProviderTypes(conName)).CreateCommandBuilder
            'End If
            cmdBuild.DataAdapter = da
            Try
                da.InsertCommand = cmdBuild.GetInsertCommand(True)
                da.UpdateCommand = cmdBuild.GetUpdateCommand(True)
                da.DeleteCommand = cmdBuild.GetDeleteCommand(True)
            Catch
            End Try
            Return da
        Catch ex As Exception
            Throw New Exception(ex.Message & " Command Text='" & CommandText & "'", ex)
        Finally
            da = Nothing
        End Try
    End Function

The Pastebin link above https://pastebin.com/Mh1ESe0f is what the CommandBuilder.GetUpdateCommand(True) did.

Just for comparison here are the inserts and deletes.

CommandBuilder.GetInsertCommand(True) https://pastebin.com/pJUhYCiY

CommandBuilder.GetDeleteCommand(True) https://pastebin.com/Sg32Snmx

This same wrapper is used for SQLite and SQL Server. Either I am fighting a bug, which would surprise me since I expect there to be a lot of people using ADO.Net and MySQLConnector. There must be some nuance that I am not expecting. For example, to get SQLite to work, I had to 'Start' and 'Commit' a transaction.