Data Access and ObjectDataSource Control

Re: Tableadapter configure wizard not generating update insert commands

swaino — Wed, 20 Feb 2008 11:36:49 GMT

Just re-visited this problem and this quote from the above tutorial highlights the work-around...

When working with relational databases, it is common for queries to pull their data from multiple, related tables. Correlated subqueries and JOINs provide two different techniques for accessing data from related tables in a query. In previous tutorials we most commonly made use of correlated subqueries because the TableAdapter cannot auto-generate INSERT, UPDATE, and DELETE statements for queries involving JOINs. While these values can be provided manually, when using ad-hoc SQL statements any customizations will be overwritten when the TableAdapter Configuration wizard is completed.

Fortunately, TableAdapters created using stored procedures do not suffer from the same brittleness as those created using ad-hoc SQL statements. Therefore, it is feasible to create a TableAdapter whose main query uses a JOIN when using stored procedures. In this tutorial we saw how to create such a TableAdapter. We started by using a JOIN-less SELECT query for the TableAdapter’s main query so that the corresponding insert, update, and delete stored procedures would be auto-created. With the TableAdapter’s initial configuration complete, we augmented the SelectCommand stored procedure to use a JOIN and re-ran the TableAdapter Configuration wizard to update the EmployeesDataTable’s columns.

Re-running the TableAdapter Configuration wizard automatically updated the EmployeesDataTable columns to reflect the data fields returned by the Employees_Select stored procedure. Alternatively, we could have added these columns manually to the DataTable. We will explore manually adding columns to the DataTable in the next tutorial.

Re: Tableadapter configure wizard not generating update insert commands

swaino — Wed, 30 Jan 2008 13:57:36 GMT

Ok thanks, I thought this might be the case but just wanted to confirm.

I was sure I'd created table adapters in the past with JOINs and generated INSERT/UPDATE commands ok?!?

What I might do is create Sprocs just for the insert/delete/update commands.

Re: Tableadapter configure wizard not generating update insert commands

ecbruck — Wed, 30 Jan 2008 13:35:45 GMT

This most likely has to do with the fact that you're using joins rather than subqueries. Check out the following tutorial.

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

Tableadapter configure wizard not generating update insert commands

swaino — Wed, 30 Jan 2008 11:06:28 GMT

I have this as my select statement when generating my tableadapter in the configure wizard:

SELECT     PurchaseItems.*, Stock.*
FROM         PurchaseItems INNER JOIN
                      Stock ON PurchaseItems.StockItemId = Stock.stkId

The update/insert/delete statements aren't being generated even though I've ticked the option to auto-generate them on the 'advanced options...' box in the wizard.

I've done this before where I have had columns from another table in my tableadapter.

So I'm not sure why this isn't working??