Display One Column Update Another

  • Thread starter Thread starter Kelly Harrison
  • Start date Start date
K

Kelly Harrison

I've got an ADO.NET app that uses SQL Server 2000. I've defined a
table that has a decimal field called 'Markup'. That field value
should be the EITHER the result of a UDF called GetMarkup() or a
user-entered value. Basically, if the user doesn't supply a value for
the Markup field, the value should be provided by GetMarkup().

Unfortunately, GetMarkup() takes the table's primary key value as a
parameter, so I can't use it as the Default value for the Markup
field. I'm looking for an elegant way to deal with this situation.

The first solution that comes to mind is to add another decimal field
called 'CustomMarkup'. Then set the Formula for 'Markup' to
ISNULL(CustomMarkup, GetMarkup(SpecInstanceID)) where SpecInstanceID
is the PK field for this table. In my ADO.NET application, it would
make sense to SELECT the Markup value into my dataset from the
'Markup' field, but write any changes back to the 'CustomMarkup'
field. In order for this to work correctly, I would need to define a
trigger on the table in question. That trigger would test the value
of the 'CustomMarkup' field against the value returned by GetMarkup().
If the two are the same, then the 'CustomMarkup' field would be
replaced by NULL.

This seems like an awfully complicated solution, so my first question
is simply this - is there a better way?

Secondly, I'm not sure how to handle the ADO.NET end of things. How
can I SELECT and UPDATE different fields? Is it as simple as
modifying the SELECT and UPDATE commands? Since the 'Markup' field is
now a calculated field, it is marked read-only. Can I simply change
the dataset to undo the read-only attribute? Being fairly new to
ADO.NET (Java background), I'd like some other opinions.

Thanks in advance,
-k
 
It sounds like you could use a business layer in your application. The
business object for that table should contain a single field Markup. You
can have any other methods that do any calculations that you want. When the
user enters a new value into the markup property your set validation code
can do whatever validation is required.

You can databind more to DataGrid's that DataSets. You can bind anything
implementing IList.

More info about a layered application:
http://msdn.microsoft.com/practices...se/EspPatternsForBuildingEnterpriseSolutions/
(layered pattern half way down page)

To see an example of a layered application see the code generator link in my
signature, and use the latest template to generate code for your database.
 
Back
Top