VS2003 versus VS2005 data adapters: Auto-generated update commands generate parameter errors?

[DDon]

Gents,

Been using 2003 for 4 years, and have several large Windows Forms based
project that I need to continue to develop with VS2005. I have imported one
of these projects, fixed some newly detected errors, and it appears to work
great in VS2005.

The problem I am having is updating Dataadapters (there are almost 100 in
this project.) Note that I do understand the "new"
datasource/dataset/tableadapter scheme in VS2005, and I see the benefits of
it. However, I am not in a situation where I can justify re-coding my
entire data access code to use this "upgraded" functionality on my existing
projects.

The problems I am having are as follows:

1) I have an existing SQL Datadapter and Dataset. They were originally
generated in VS2003, with select/insert/update/delete and concurrency
checking. It has worked for years, and continues to work after the 2005
upgrade.

2) I need to add a couple more fields from the SQL server table to my
dataadapter. I do this using the "Configure Data Adapater" wizard in
VS2005. I also modify the table in the dataset for the new fields.

3) The first issue I encounter is that my SQL Table has a computed column,
and the new Update Command generated by the wizard apparently has no clue
that it is computed and should not be updated. I dont understand this, as
VS2003 dealt with this easily. The only way I could get around this was to
manually edit the update statement to remove refrences to this column.

4) After removing references to the computed column, I get "parameter
missing" errors when trying to update. These errors are all related to
"nullable" columns in the table. Apparently, the 2005 wizard uses a
different syntax for concurrency checking on null columns, by created a
parameter called "@Is Null_ColumnName," which is somehow is not supplied
during a dataadapter.update? I have been searching the web and these
forums for more info on this, but cant find it anywhere.

Here are examples of the 2 Where clauses:

VS2003: Carrier = @Original_Carrier OR @Original_Carrier IS NULL AND
Carrier IS NULL)

VS2005: @IsNull_Carrier = 1 AND Carrier IS NULL OR Carrier =
@Original_Carrier

I dont understand this, and am hoping that there is something basic that I
am doing wrong. I do have the datasource configured, and it is the right
type. But appears that the VS2005 wizard does not properly check the SQL
server schema for computed columns, and it generates new parameters that
somehow do not get supplied on a dataadapter.update?

If this is true, I am going to have to manually create every update
statement going forward - I will not be able to use any of the data wizards
on any existing adapters in my project?

Help - thanks!

 

[WenYuan Wang]

Hi DDon,

We need to perform more research on this issue . We will reply here as soon
as posible.
If you have any more concerns on it, please feel free to post here.

Thanks for your understanding.
Best regards,
Wen Yuan

 

[WenYuan Wang]

Hi DDon,
Thanks for your waiting.

@IsNull_Carrier is used to check whether the Original_Carrier in your
dataset is null by VS 2005. I have tried to reproduce your issue on my
machine. I created an application in VS 2003 and upgraded the project by VS
2005 wizard, added computed columns into select query by "Configure Data
Adapater" wizard, generate the insert, update and delete command by
default, and then run the application. But I haven't met any issue. Some
suggestion: Have you installed SP1 on your machine? I think maybe this
issue has been addressed by SP1. For this reason, I haven't met this issue
on my machine.
You can get VS 2005 SP1 from the following website.
http://www.microsoft.com/downloads/details.aspx?familyid=BB4A75AB-E2D4-4C96-
B39D-37BAF6B5B1DC&displaylang=en
[Microsoft? Visual Studio? 2005 Team Suite Service Pack 1]
Brief Description
This download installs Service Pack 1 for Microsoft Visual Studio? 2005
Standard, Professional, Team Editions.

Please try this server packet and let us know whether this work for you. We
are glad to work with you.
Sincerely,
Wen Yuan

 

[DDon]

VS SP1 is already installed.

Not sure what you actually did to test, but this happens every time there is
a computed column in the SQL table. I tried it with 3 different tables, 2
with a computed column, 1 without.

If there is no computed column, everything works fine, even the issue with
Null columns. However, if there is a computed column in the SQL table, the
update will fail with one or the other of the "computed column" error or the
@IsNull_ColumnName, depending on the postion of the computed column in the
table (if the first null column is before the computerd column, it will
genereate that error first.

When you say you "added computed columns into select query" how do you do
this? The computed column has to already be in the SQL server database
table., not create as part of the query.

By the way, my SQL server is 2005 standard, with SP2

 

[William \(Bill\) Vaughn]

Ah, an SQL query can contain a column whose value is the result of an
expression as GetCurrentInventoryCount().
Let's see the code generated by Visual Studio. We're looking for the
UpdateCommand buried in the TableAdapter or DataAdapter generated code. This
should tell the story.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[WenYuan Wang]

Thanks DDon and Bill.
I great appreciated for your reply.
I noticed there was something wrong in my first reply. I tested with a
query command rather than the computed column in Table. Now, I have
reproduced this issue on my machine. I'm afraid this is a VS 2005 product
issue. The wizard uses commandbuilder to generate the update command, but
commandbuilder cannot recognize a computed field. Just now, I can think the
best way to work around this issue is using Stored Procedure to update
database. Additionally, I will contact our product team and check if they
have any better idea. I will come back as soon as I can get anything
information or suggestion.

Thanks again for your understanding.
Sincerely,
Wen Yuan

 

[DDon]

I appreciate the replies.

Now that it is isolated down to just the SQL computed column issue, it's not
as bad as I first thought. The workaround is to not include the computed
column in the Query when running the Configure Data Adapter wizard. After
it builds the statements, I can then manually add the column to just the
Select command afterwards. Takes a lot less time than writing everything
from scratch.

Also, although the Command Builder really should handle this (it did in
VS2003), there are not that many computed columns in my project.

Thanks again, and I would appreciate any info about a fix.

 

[WenYuan Wang]

Hi DDon,

Really thanks for your understanding. I'm so glad to hear there are not
many computed columns in my project. VS 2005 haven't brought you a terrible
experience so far. I have submitted the bug for our product team and waited
for the confirmation from them. If I can get any information about a fix, I
will come back here and pass information for you.
If you have any more concerns on it, please feel free to let me know.

Have a great day.
Sincerely,
Wen Yuan