Generated update obviously fails on readonly field

[Guest]

Hi,

With VS 2005/NET 2.0, I add a datasource in my project, and the Dataset
Designer creates automaticaly the SELECT, INSERT, UPDATE and DELETE
functions/clauses.

BUT the UPDATE include all the fields of the table, included some read-only
or calculated fields. And thus, obviously, any attempt to update FAILS!!!

1 - Does anybody know a way to inform the Designer that some fields are RO,
so that it won't include them in the UPDATE statement? I precise that I need
to get those fields in the SELECT, so I can't simply delete them from the
Designer...
2 - If I have to make a customization of the code to bypass the default
UPDATE clause and functions, can anybody give me a hand on how to do this?
3 - Any other idea to solve this, or any workaroud?

Thanks to you all, guys.
Pascal

 

[W.G. Ryan - MVP]

Chaprot:

All you need to do is run through the wizard and then go to the generated
code and remove the column names from the update queries. If you are using
Stored Procedures, you can just delete them from the proc, otherwise just
change the generated code. Another approach is to remove them from the
Select query and Add a column for each to your datatable, setting the
expression to the same formula that you use in the database. This is
preferable for a few reasons. 1- You don't have to modify the update
commands 2-You don't have to pull over unnecessary data 3-If you update any
of the columns that the computed ones are based on, right now the changes
won't be reflected - however if you use an Expression column, they will be
visible as soon as you make the change locally. This goes in to it in a
little more depth

http://www.knowdotnet.com/articles/expressions.html

HTH,

Bill

 

[Guest]

Hi Bill,
Thanks for your response. I will see what to do with the calculated fields,
and read carefuly the article you propose.

If I change the generated code (in MyDataset.Designer.cs) it may be
overwritten when I make a modification in the DataSource Designer, right? So
I suppose I have to create some overriden functions in a
MyDataset.modified.cs or similar?

The pity is that I'm losing all the advantage of the Designer, and of the
automaticaly generated classes - as I will be obliged to manually monitor any
change in the dataset on the table I've modified the code (in fact, I can
only mantain the SELECT: UPDATE, INSERT and DELETE clauses and functions have
to be modified).

But why the hell does the designer NOT take into account a field marked as
Read-Only? It's like a bug, as the generated code will NEVER work (except the
SELECT) if there is a RO field - like an autonumber (except for the primary
key, of course).

Best regards,
Pascal

 

[Guest]

I HAVE IT !

In such a case, you have to create (with the Designer) the first SELECT
clause WITHOUT the RO/calculated fields. Then the UPDATE, INSERT and DELETE
clauses will be generated based on the SELECT fields.
And - abracadabra ! - you create another (or others) SELECT clause with the
Designer, to retrieve all the fields ! That's all !

Ouf !! No need to bypass the Designer or to customize th generated code.
Great !

Pascal