Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
I have looked at multiple posts regarding this issue, but can't seem to find a solution. To keep it simple I have an Access database with a single table. It does have a field as a primary key. When I click the save icon on the Binding Navigator I get this error having to do with the following code:
Me.Validate()Me
.TitlesBindingSource.EndEdit()Me
.TitlesTableAdapter.Update(Me
.VFCurvesDataSet.Titles)I cannot set the Refresh the Data Table check box in Advanced Options of the TableAdapter Configuration Wizard as it is grayed out. Once again, I have a primary key.
The posts I have reviewed indicate that an UpdateCommand needs to be created, but I can't understand how it should be done. Would anyone be able to shed some light on this?
RW
Hi,
1 Please see how to use the TableAdapter Configuration Wizard creates and edits TableAdapters. We can use this wizard to generate TableAdapter methods for fetching and updating data.For more information, visit here.
2 The exact procedure to update a data source can vary depending on your business needs, but your application should include the following steps:
(1) Call the adapter's Update method within a try/catch block.
(2) If an exception is caught, locate the data row that caused the error. For more information, see here.
(3) Reconcile the problem in the data row (programmatically if possible, or by presenting the invalid row to the user for modification), and then reattempt the update.
Hope this can help you
Thank you.
The answer did indeed turn out to be related to the TableAdapter. It did not auto-generate an UpdateCommand. I was able to correct the issue by modifying the select statement and then the UpdateCommand was generated. If anyone would like me to give a detailed description of my issue and how I resolved it, please let me know.
can you please give details about how you resolved this issue?
I am having the same prblem, my DB is Db2
did you ahve to change the select query to update query in tableadapter configuration?
thanks
rupali
Hi Rupalik. I will try. If you look at your xsd file in the designer, you will see your database tables and your tableadapters (assuming you are using them). If you click on a tableadapter and look at the properties you will see entries for a DeleteCommand, InsertCommand, SelectCommand and UpdateCommand. If the UpdateCommand entry is none, then you need to either change the Select Query so that the system auto-generates the UpdateCommand or click the UpdateCommand property, choose New and build the command using the dialog.
I have spent many hours finally coming to understand why these types of problems occur. They all have to do with the presence or lack thereof of the various SQL commands. If you can be more specific about your table structure, perhaps I can help more. Otherwise, I hope this helps.
Randy
Hi, Thanks for your response. I was actually able to solve the problem by just creating a new tableadapter with the table that has primary key. The table did not have a PK before and even though it was added to the table, my dataset did not recoganize it untill I created a new form with new table adapter.
-Rupali
 randywheeler wrote: |
| Thank you. The answer did indeed turn out to be related to the TableAdapter. It did not auto-generate an UpdateCommand. I was able to correct the issue by modifying the select statement and then the UpdateCommand was generated. If anyone would like me to give a detailed description of my issue and how I resolved it, please let me know.
| |
How did you modify the select statement? I am having a heck of a time even trying out all the links to samples provided. My current SQL is:
SELECT [SECTION], FOLDER, ENTRYID, STOREID FROM tblSections
The ENTRYID column is the primary key in the Access DB and is showing as the PK in dataset designer. Still get the erorr when clicking the SAVE button on my form. The update etc. commands are not being created for some reason. I can see the updated/added/deleted data in the form but the Access DB is not getting updated.
If someone could provide an example of code it would be priceless!
This issue is a real time consumer. What I would suggest you do is create a new Access database with a primary key and two or three string fields or whatever. Then see if you can attach that database and see if the program automatically generates the update command. If so, look at the structure to see how the update command is formatted and apply the structure to your DB. The big problem seems to be in the understanding of how to develop the update command manually. I can tell you I spent days figuring it out for myself. I can't tell you how to do it because it depends on your database structure. Hopefully some Visual Basic Guru (not me) can help you out more.
I'm experiencing the same problem. If you could explain the changes you made to your "SELECT" statement, I would appreciate it. Thanks in advance.
NJ2
NJ2 at 2007-10-3 >
My original select statement was like the following:
SELECT [Field1], [Field2], [Field3], [Field4], [Field5] FROM [MyTable]
all I did was change it to
SELECT * FROM [MyTable] since I was accessing all fields
and the Update command was generated. However, later I had another table with the same issue and the change did not generate the Update command. As best I can tell your table needs a primary key for sure. As I said in a previous post, try generating a database with a very simple test table and primary key and see if you can get a generated update command. If you can, look at the structure of the update command and apply it to your actual table. This is what I had to do. A good source explaining the structure of an update command would be invaluable.
