What happened to my TableAdapter update and delete commands?

Question

I am struggling with the unshakable designer of VS2008 DataSet, it seems. I am trying to do what seems like a simple solution for a dataset of two tables, where one table is just a text value for an integer value stored in another table. Basic data design 101.

Table1 CharField1 CharField2 IntForeignKeyField1 etc Table2 IntPrimaryKey1 ValueForKeyField 

This does not affect the problem that I am facing, I do not believe it, because I do not want to do anything except read the values ​​from the second table, so I can select them in the DataGridViewComboBoxColumn in the client - - I will never edit them on the client. But, I was distracted.

Since it lives in a web service, and I wanted a typical typed dataset to be delivered to the clients of this web service, I decided to use the DataSet Designer to create TableAdapters and all the plumbing, thinking it might be less work and easier to maintain .

So, I created nice fields and edited the selection instructions, telling the developer to create all the Insert, Update, and Delete commands for the table adapter used. He was happy telling me that everything was done as I asked.

However, when I tried to use the Update operator, I had an error saying that there was no valid Update operator! After some fruitless search for people with similar problems on the Internet, I burst into XML for a dataset. Of course, there is no Update statement and Delete statement.

I tried to completely remove and recreate the DataSet from the project with the same results. Update or delete statements were not created, even if it was reported as completed.

I ended up creating the Update XML document manually by checking out another constructed dataset from another project, so the web service now works. However, I do not believe that my changes would have lasted through editing initiated by the designer, and I am at a standstill about why it does not work. Any ideas?

Thanks for any feedback, Dave

Answer 1

could it be that there is no unique primary key defined for the table?

Answer 2

I have the same problem. TableAdapter does not work properly when using fields from multiple tables. I was able to recreate the solution from the following article: "Updating the table adapter to use joins."

http://www.asp.net/learn/data-access/tutorial-69-vb.aspx

The solution includes creating your own Select / Update / Insert / Delete procedures. Unfortunately, although I was able to keep track of the solution for the sqlserver database, I still cannot get it to work for my local Access database. All stored procedure parameters are not available.

Good luck

Answer 3

I think I found a solution. 1) Create a TableAdapter for the main table only and copy the UPAdATE TableAdapter procedure (stored in the TableAdapter properties window) 2) Modify the SELECT query in the "Query Builder Wizard Builder" to include the fields from both tables and the join. 3) Insert the old UPDATE procedure into the now empty TableAdapter UPDATE procedure. 4) After creating the DataGridView, you can display the fields from both tables and update the main table. Repeat the steps for the INSERT and DELETE commands.

If your goal is to update both tables, try viewing the update information for the parent / child TableAdapter component on the Internet. Here is a good link: http://blogs.msdn.com/bethmassi/archive/2009/05/14/using-tableadapters-to-insert-related-data-into-an-ms-access-database.aspx

Answer 4

Just ran into the same main problem. I told the data developer to create all insert, update, delete instructions. When I went to update one of the tables, there was no update. I finally went into the .xsd file created by the data constructor (just double-clicking to open it in the IDE). Then I right-clicked on the title bar of the table in which there was a problem and selected the configure option. From there, I clicked the Advanced Options button, then selected the Create Insert, Update, Delete option. After clicking OK, I checked my project and the update was available for the table adapter.

Answer 5

What George suggested is great. We need to enable the option "Create insert, update, delete".

However, in VS2013, using unnecessary table attributes in a selete statement can violate the IDE and only generate SELECT and INSERT statements. Just remove these qualifiers and everything should be in order. Make sure you also have a unique PC.

If you need to use a complex fill, for example, apply a filter to return only certain rows of the table, you can fill the data table using the special select statement in the form load event. Thus, the generated INSERT, UPDATE, and DELETE statements will still work because DELETE and UPDATE work on the PC.