It's all about money! 1

[jon24422531]

I have added another column to an existing table and defined it as MONEY. Default Value 0.00

Now when I either use QA to update the field in an existing record OR us EM and type directly into the column it stays at 0

update worklog..worklog Set EstimatedCost = '£12.00' where faultno = '303'
Select estimatedCost, * from worklog..worklog where faultno = '303'
/*This doesn't Work either */
update worklog..worklog Set EstimatedCost = £12.00 where faultno = '303'

Where am I going wrong?

Jonathan

http://www.telecom-stuff.com

 

[jbenson001]

Try removeing the '£' sign before the value

 

[jon24422531]

jbenson001

Thanks, I have tried that and also many other combinations including 4 decimal places, the dollar sign etc, but all it does is stay at 0. Now if I do an insert with a new record it works, but I can't update.

Jonathan

http://www.telecom-stuff.com

 

[SkipVought]

Why are you assigning a STRING value to your MONEY column?

 Set EstimatedCost = 12.00

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jon24422531]

Hi SkipVought

Tried that too. I have googled it and everywhere else I can think. I am unsure why something is so obscure as money in SQL

Jonathan

http://www.telecom-stuff.com

 

[gmmastros]

better to use decimal datatypes to begin with since money data types are all screwed up and proprietary

http://morethanlessthandot.com/viewthread.cfm?qid=1316716

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

Take the default off as a test and see if you can update.

 

[jon24422531]

Now I know that madness is all in the mind!

Taking George's advice I've abandoned Money and turned to Decimal: 5(9,4).

Now when I run

update worklog..worklog Set EstimatedCost = '12.00' where faultno = '303'
Select EstimatedCost from worklog..worklog where faultno = '303'

The result is the same! .000

Maybe I should abandon SQL and turn to drugs!

Jonathan

http://www.telecom-stuff.com

 

[SajidAttar]

Generate the script for the table (including default and other constraints) and post it here.. we will create the table at our end and see the behaviour.

Do you have any trigger on the table?

Regards,


Regards,


"Dream not what makes your sleep a pleasure, but what makes you work
hard to achieve it and forget your sleep (untill you achieve it)." -- SJD

 

[jon24422531]

SajidAttar

Thank you for mentioning triggers, there is an Instead of Update trigger..........

I have mentioned elsewhere in Tek-Tips about my stupidity, but for those that missed it, here it is again.

Jonathan

http://www.telecom-stuff.com

 

[markros]

You forgot to help him to figure this out