TableAdapter generates bad SQL

[GaryDean]

I'm comparing use of DataView -> ObjectDataSource -> TableAdapter to
DataView -> SqlDataSource using the Customers table on the NorthWind
database.

The SQL given to both wizards is:
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address,
City, Region, PostalCode, Country, Phone, Fax
FROM Customers

But the two wizards generate different UPDATE statements (Pasted below) and
I see the TableAdapter is trying to set the CustomerID Key which looks
wrong. The problem here is that the TableAdapter does not work saying that
it can't fine a generic Update statement with the correct parameters.

Why is the TableAdapter wizard generating bad Update SQL ??

ObjectDataSource:
UPDATE Customers SET CustomerID = @CustomerID, CompanyName =
@CompanyName, ContactName = @ContactName, ContactTitle = @ContactTitle,
Address = @Address, City = @City, Region = @Region, PostalCode =
@PostalCode, Country = @Country, Phone = @Phone, Fax = @Fax
WHERE (CustomerID = @Original_CustomerID)

SqlDataSource:
UPDATE [Customers] SET [CompanyName] = @CompanyName, [ContactName] =
@ContactName, [ContactTitle] = @ContactTitle,
[Address] = @Address, [City] = @City, [Region] = @Region, [PostalCode] =
@PostalCode, [Country] = @Country, [Phone] = @Phone, [Fax] = @Fax
WHERE [CustomerID] = @CustomerID">

 

[Kevin Yu [MSFT]]

Hi Gary,

Could you let me know how you get the TableAdapter wizard in
ObjectDataSource? Because as I know, the ObjectDataSource is connecting to
a business object, which defines SELECT, UPDATE, DELETE and INSERT as its
methods. The object should be a class instance. While SqlDataSource is
connecting directly to database, which will generate SQL Statements
correctly.

Kevin Yu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[GaryDean]

Let me try to clarify....
I created a new asp.net website where I first added a Dataset. When you add
a DataSet the TableAdapter Wizard automatically comes up and you can then
use it to generate Select, Update, Delete, and Insert statements (which is
very similar to the wizard you get when you use a SQLDataSource). I chose
to use a Select statement of each (not *) column in the Customers table in
the NorthWind database. As you can see from my original post, it appears to
have generate a bad Update statement as it is setting the CustomerID field.

Then, I dropped a GridView and an ObjectDataSource onto the form, connected
the ObjectDataSource to my Dataset and connected the GridView to the
ObjectDataSource. The page works except that when you hit Edit and then try
to change a field you get the Error I posted here.

Then I dropped a SQLDataSource onto the form and used the wizard there to
generate Insert, Update, and Delete statements using an identical Select
Statement. I connected the SQLDataSource to the GridView (after
disconnecting the GridView from the ObjectDataSource) and everything ran
fine including the update.

So, This should explain my original post more clearly.

 

[tdavisjr]

Let me first comment on the error you are getting. This is a common error
when using TableAdapters with ObjectDataSource. If you do search on that
error message you will see various number of forum post asking for help
about the same issue.

Now, for the SQL code it generates. My guess is MSFT is assuming that there
are some cases where you might want to update the primary key of a table.
This is very very rare, but this is the why they designed it. Or, this may
be generated for optimistic concurrenty. However, I do agree that they
should be at least consistant on generating SQL the same way using both
methods, ObjectDataSource and SqlDataSource. But, you are not completly
locked in to using this "bad sql". In the Dataset designer, If you view the
properties of the TableAdapter you will see there is a Select, Insert,
Update and Delete command that you can edit. So, I would edit the Update
command to remove the SQL that updates the primary key as well and also make
sure the parameters collection reflects this as well.

I am working with a project as we speak that uses the TableAdapters and I
encounterd the exact same error most people get when they try to use them
with the ObjectDataSource. Here are some tips that may eliminate these
errors:

1) Remove the OldValuesParameterFormatString or set it to {0}. By default
its somethign like original_{0}, from the ObjectDataSource on the web form.

2) Opt not to generate INSERT, UPDATE and DELETE methods automatically using
the tool and instead use the Add Query function on the tableadapter and
write your own TSQL for INSERT, UPDATE and DELETE and give them our own
method names like InsertCustomer(...) instead of the designer generated
method name Insert(...)

There are other issue I encountered; but this was becuase I wrapped calls to
my TableAdapters through a custom business/service layer and then bind the
ObjectDataSource this this layer and not directly to my TableAdapters. But,
this is not what you are doing so listing other tips don't apply.

I hope this was helpful.

 

[GaryDean]

Keven,
It's Setting the primary key, CustomerID, equal to @CustomerID on an UPDATE.
You don't set the primary key to anything on an UPDATE you do that in an
INSERT. Optimistic concurrency comparisons (or not) occur in the WHERE
not in the SET.

It's bad SQL, no?

Unless I'm mising something here, this should be reported as a bug.

 

[Kevin Yu [MSFT]]

Thanks for tdavisjr's reply.

Hi Gary,

Thank you for your clarify. Yes, now I can reproduce this issue on my
machine. Based on my research, this is a known issue in VS2005. Parameters
of the Update method in the DataTableAdapter of a typed DataSet do not
match the signature expected for an Update method by an ObjectDataSource.

Here, we provided a workaround to this issue. I tried it on my machine, and
it's working fine.

1. In your web project, click on the ObjectDataSource, and in the property
grid set its ConflictDetection property to CompareAllValues. The is
required because DataSets use optimistic concurrency for their methods.
2. In the GridView control, open the Smart Tag and click Edit Columns. In
the lower listbox ("Selected fields") select the OrderID column (because it
is the primary key), and reset its ReadOnly property back to False. This is
required because the DataSets are generated with an updatable primary key,
and the GridView doesn't realize that.

You can check the following link for more information about this issue.

http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?Feedbac
kID=111156

If anything is unclear, please feel free to let me know.

Kevin Yu
Microsoft Online Community Support

==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[GaryDean]

Kevin,
thanks for you help. I think the proper action is to always code your own
updates, inserts, etc and use the SQL generators for query only.

 

[Kevin Yu [MSFT]]

You're welcome, Gary. Yes, I agree with you that it's better to use our own
insert/update/delete, especially put them into stored procedures. You'll
gain better security and better performance. Thanks for sharing your
knowledge in the MSDN newsgroup.

Kevin Yu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)