Wednesday, May 02, 2007

Puzzling TableAdapter problem

Today I was asked to look at a puzzling problem with TableAdapters at another project at my current customer. The problem they had was that they could not make the TableAdapter read back the generated SQL Server identity column values when saving a dataset (ta.Update) to insert new rows.

The first thing I checked was the 'Advanced options' of the TableAdapter to see if the 'Refresh the data table' setting was on. It wasn't. "But, we checked that option when we added the adapter". I checked the option and finished the wizard, then checked the setting again. It wasn't set...

I had noticed that details in the wizard summary page was rather short, so I did the wizard steps and inspected the summary again: "Generated SELECT statement" and "Generated INSERT statement", but no "Generated UPDATE statement" and no "Generated DELETE statement". I suspected that this was the cause of the problem, but what could cause the missing SQL commands ? The UpdateCommand and DeleteCommand of the TableAdapter's property window was empty. According to the online help, these statements "will be generated if there is enough information", but no hints to which information is needed. Really helpful.

Looking at the table definition in SQL Server, I then noticed that the table had no defined primary key. I selected the identity column and applied the "Set Primary Key" action and saved the table definition in SQL Server. After reconfiguring the TableAdapter in Visual Studio, the data table now has a defined primary key and - lo and behold - update/delete commands and refresh data on save.

Problem solved.

No comments: