[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.

[jmcilhinney]

There is no point calling GetUpdateCommand or the like on a command builder unless you intend to modify the command it returns, e.g. enrol it in a transaction. Otherwise, just create the command builder and call Update on the data adapter and it will work.

[gwboolean]

My understanding is that the GetUpdateCommand line dynamically updates the whole table with whatever has been modified in the DGV. Is that not correct?

I have observed this used, and it works to update a table with everything in a dgv. Whether modified or not. I like this approach for what I am trying to do, but I am either incorrectly applying it, which I no longer believe I am, or something I am unable to understand is occurring. I do admit that I obtained this method from an application using sqlCE, but I do not believe this is a problem caused by the database I am using.

Now I read through all the material I could find about the error, but was unable to turn that into a workable solution.

So do you know what this exception means, with regard to what I am doing, and what I might do to remediate it?

[jmcilhinney]

My understanding is that the GetUpdateCommand line dynamically updates the whole table with whatever has been modified in the DGV. Is that not correct?

No, that is not correct. As is ALWAYS the case, you should read the documentation. That will tell you what it actually does. All it does is get the UPDATE command generated by the command builder. Doing so is pointless unless you actually want to examine or modify that command. It's calling Update on the data adapter that saves changes and it saves them from a DataTable, not a DataGridView. That you may have bound the DataTable to the DataGridView isn't relevant to that saving. Doing that will execute the InsertCommand for each row with a RowState of Added, the UpdateCommand for each Modified row and the DeleteCommand for each Deleted row. You don't have to do anything with or to the command builder to make that happen, assuming all the required conditions are met or auto-generation of SQL code from your SelectCommand.

So do you know what this exception means, with regard to what I am doing, and what I might do to remediate it?

Ii really does seem that a lot of people ask about exceptions without ever actually investigating for themselves. I imagine that searching the web for that error message would have produced relevant information but you can even work it out for yourself. Look at the words:

requires all parameters to have an explicitly set type

where are you creating your parameters?

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

Are you explicitly setting the type? No, you're only setting the name and the value. I'm not sure why Prepare is being called - maybe the use of a command builder requires it for SQL generation - but it's happening so you need to specify the types for your parameters.

[gwboolean]

No, that is not correct. As is ALWAYS the case, you should read the documentation.

Sigh. Just once, could you offer suggestions/help that are useful and have content that contains something more than snide remarks and the obvious fact that you didn't even bother reading what I was asking? Let's begin with the fact that I read all of the documentation available to me and that what I believed to be the resolution was based on my understanding of what I read.

All it does is get the UPDATE command generated by the command builder. Doing so is pointless unless you actually want to examine or modify that command.

Running an update is NOT pointless under any circumstances. Are you that obtuse? Certainly one does not have to run an update unless there is something to update. But the idea is to just have an automated system that will update should data be changed. Are you suggesting that running an update without have modified data will somehow harm the data or the code?

Ii really does seem that a lot of people ask about exceptions without ever actually investigating for themselves.

I asked about the exception because I had indeed been investigating the documentation (Microsoft to be precise) and I did not fully understand it, as I previously stated. Again, are you intentionally obtuse?

Look at the words: requires all parameters to have an explicitly set type. You are not explicitly setting type.

No kidding? I was even able to get that out of the documentation I read. It even provided an example of how those parameters might be set, which I did not understand nor was I able to make the method work for me.

Your condescending scree suggest that you know even less than I do. Thanks for nothing!

[OptionBase1]

and the obvious fact that you didn't even bother reading what I was asking?

Yeah, jmc can be abrasive, but it is somewhat ironic that you think jmc didn't read what you were asking (which he obviously did, and he responded relevantly to everything you posted), and in your responses above you make it abundantly clear that you didn't bother reading his answers.

For example, he never said running an update was pointless. Good luck, though.

[jmcilhinney]

Just once, could you offer suggestions/help that are useful and have content that contains something more than snide remarks and the obvious fact that you didn't even bother reading what I was asking?

Yeah, and I did:

All it does is get the UPDATE command generated by the command builder. Doing so is pointless unless you actually want to examine or modify that command. It's calling Update on the data adapter that saves changes and it saves them from a DataTable

So I tell you that what you thought was wrong and why it was wrong, but you ignore that and repeat the same wrong thing:

Running an update is NOT pointless under any circumstances. Are you that obtuse?

I never said it was. Are you that obtuse? Calling GetUpdateCommand on the command builder DOES NOT update anything. It simply gets the UpdateCommand that will be executed when you actually save changes. It is calling Update on the data adapter that actually saves the changes, executing the InsertCommand, UpdateCommand and DeleteCommand generated by the command builder as required. You don't need to call GetUpdateCommand for that to happen. Calling GetUpdateCommand is pointless unless you intend to modify the UpdateCommand that you get before doing the actual save. Do you finally understand this time? I've told you twice and it's all in the documentation that you claim to have read, so I would hope so.

I asked about the exception because I had indeed been investigating the documentation (Microsoft to be precise) and I did not fully understand it, as I previously stated.

You provide no evidence that you even read the exception message, never mind did anything about it. We only know what you tell us so if you know that the problem is where you're creating the parameters then why didn't you tell us that, instead of expecting us to figure it out. We should have to figure out the stuff that you already know as well as the stuff you don't.

I was even able to get that out of the documentation I read. It even provided an example of how those parameters might be set, which I did not understand nor was I able to make the method work for me.

Again, where's the evidence? Where's the example code in your question? Where's your attempt to use it? Where's even an indication that you know where the root cause of the issue is? Nowhere. You could have told us that you know that you need to provide the parameter type in a specific location in your code and you have an example of how to do so but you're not sure how to apply it. You could have done that, but you thought that it would be better to give us less information and work out stuff you already knew for ourselves. Thanks for nothing.

Your condescending scree suggest that you know even less than I do.

And topping it off with the ultimate irony! Maybe you meant "screed". Then again, given that I know so little, maybe you're saying something profound about loose rocks and I'm just too obtuse to see it.

[gwboolean]

No, I meant skree. I made the effort to stay away from anything profound and was attempting to speak at your level. You would be too obtuse to have any comprehension of anything even marginally profound.

Again, thanks for nothing.

[OptionBase1]

No, I meant skree. I made the effort to stay away from anything profound and was attempting to speak at your level. You would be too obtuse to have any comprehension of anything even marginally profound.

Again, thanks for nothing.

You will continue to be welcome for it I'm sure.

[gwboolean]

I was able to finally resolve some of the issues and get a working method. I was unable to find a way to get the line below to execute without error

MasterBase.ListAdapter.UpdateCommand = New OleDbCommandBuilder(MasterBase.ListAdapter).GetUpdateCommand

However, after some alterations to my query and realizing that

ListCommand.ExecuteNonQuery()

will screw up any query that is NOT for a DGV, I was able to successfully update any modifications to the DGV. I still do not understand what this code is supposed to do, only that a DGV will not update correctly without it.