"DDon" <DDon@nospam.nospam> wrote in message
news:ejenPbWWHHA.5092@TK2MSFTNGP03.phx.gbl...
> 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!
>
>

"DDon" <DDon@nospam.nospam> wrote in message
news:ueVMx13WHHA.5108@TK2MSFTNGP06.phx.gbl...
> 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
>
> "DDon" <DDon@nospam.nospam> wrote in message
> news:ejenPbWWHHA.5092@TK2MSFTNGP03.phx.gbl...
>> 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!
>>
>>
>
>

news:Ia$Vd5$WHHA.4032@TK2MSFTNGHUB02.phx.gbl...
> 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
>