[RESOLVED] Update datagridview to ACCESS table

[gwboolean]

I am getting an error message stating.

System.InvalidOperationException: 'OleDbCommand.Prepare method requires all parameters to have an explicitly set type.

I would first like to thank JM for his assistance/suggestions on a previous thread that I had on this same subject. While his information was helpful, and indeed, resolved the issue, things eventually became unglued when I attempted to take his suggestions and make them fit what I am doing.

So what I am doing is taking the datagridview (DGV) that is in the form and modifying it then updating the underlying database, using the Save button.



After inputting the data into the cells (actually, only the cells in the 3rd and 4th columns), I save the changes.

    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        UpdateRecord()
        MyName = txtName.Text
        chgRecord.txtName.Text = MyName
        'UpdatePart()
        LoadGrid()
        MessageBox.Show("Record saved.", "Save", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Me.Close()
        If MyState = "View" Then fabList.Show() Else chgRecord.Show()
    End Sub

    Private Sub LoadGrid()
        'setAssemblyQuery()
#Region "Establish Connection and execute Query"
        MasterBase.AddParam("@recno", CStr(AssyID))
        Try
            RecordCount = 0
            MasterBase.Exception = ""
            MasterBase.MasterBaseQuery("SELECT colMasterID,colName,colAssyID,colAmount,colUnit " &
                                       "FROM adjAssembly " &
                                       "WHERE colAssyID = @recno " &
                                       "ORDER BY colMasterID ASC")
            MsgBox(RecordCount)
        Catch ex As Exception
            MasterBase.Exception = ex.Message
            MsgBox(ex.Message + vbLf + "adjAssembly query failed.")
            Me.Close()
        End Try
#End Region
        If RecordCount > 0 Then
            dgvComponent.DataSource = MasterBase.ListDataSet.Tables(0) 'populate DGV and update table
            dgvComponent.Rows(0).Selected = True


            MasterBase.ListAdapter.UpdateCommand = New OleDbCommandBuilder(MasterBase.ListAdapter).GetUpdateCommand
        End If
    End Sub

Everything works well until I get to the last line if the If branch, the bold line above.

My best understanding is that I somehow need to specify the datatype for those columns when creating the parameters. However, all of the guidance lost me at that point. My guess is that it has something to do with AddParam().

Here is my connection information

        Public MasterBaseConnection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=MasterBase5.0.accdb;") 'Database Connection
        Public ListCommand As New OleDbCommand
        Public ListAdapter As OleDbDataAdapter
        Public ListTable As DataTable
        Public ListDataSet As DataSet
        Public Params As New List(Of OleDbParameter)
        Public Exception As String


        Public Sub MasterBaseQuery(SetQuery As String)
            RecordCount = 0
            Exception = ""
            Try
                MasterBaseConnection.Open() 'Open connection
                ListCommand = New OleDbCommand(SetQuery, MasterBaseConnection) 'Database Command
                Params.ForEach(Sub(p) ListCommand.Parameters.Add(p)) 'Load params into command
                Params.Clear() 'Clear params list
                ListDataSet = New DataSet
                ListTable = New DataTable
                ListAdapter = New OleDbDataAdapter(ListCommand)
                RecordCount = ListAdapter.Fill(ListTable)
                ListDataSet.Tables.Add(ListTable) 'This Dataset is used for setting comboboxes
            Catch ex As Exception
                Exception = ex.Message
                MsgBox(ex.Message + vbLf + vbCrLf + MyError)
            End Try
            MyError = ""
            If MasterBaseConnection.State = ConnectionState.Open Then MasterBaseConnection.Close()
        End Sub

        Public Sub AddParam(Name As String, Value As Object)
            Dim NewParam As New OleDbParameter(Name, Value)
            Params.Add(NewParam)
        End Sub

I am extremely disappointed, because I believed that I had this one, and that I had successfully interpreted JM's information to suit my needs.