1 Attachment(s)
[RESOLVED] Update datagridview to ACCESS table
I am getting an error message stating.
Quote:
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.
Attachment 184713
After inputting the data into the cells (actually, only the cells in the 3rd and 4th columns), I save the changes.
Code:
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
Code:
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
Code:
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.
Re: Update datagridview to ACCESS table
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.
Re: Update datagridview to ACCESS table
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?
Re: Update datagridview to ACCESS table
Quote:
Originally Posted by
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?
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.
Quote:
Originally Posted by
gwboolean
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:
Quote:
requires all parameters to have an explicitly set type
where are you creating your parameters?
Code:
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.
Re: Update datagridview to ACCESS table
Quote:
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.
Quote:
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?
Quote:
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?
Quote:
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!
Re: Update datagridview to ACCESS table
Quote:
Originally Posted by
gwboolean
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.
Re: Update datagridview to ACCESS table
Quote:
Originally Posted by
gwboolean
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:
Quote:
Originally Posted by
jmcilhinney
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:
Quote:
Originally Posted by
gwboolean
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.
Quote:
Originally Posted by
gwboolean
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.
Quote:
Originally Posted by
gwboolean
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.
Quote:
Originally Posted by
gwboolean
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.
Re: Update datagridview to ACCESS table
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.
Re: Update datagridview to ACCESS table
Quote:
Originally Posted by
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.
You will continue to be welcome for it I'm sure.
Re: Update datagridview to ACCESS table
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
Code:
MasterBase.ListAdapter.UpdateCommand = New OleDbCommandBuilder(MasterBase.ListAdapter).GetUpdateCommand
However, after some alterations to my query and realizing that
Code:
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.
