MSSQL UPDATEGRAM and '$'

Here is my updategram:

<?xml version="1.0" ?>
<root xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync>

<updg:before>
<msj_currency_v2 currencyID="1" currencyName="US dollar" currencySymbol="s" currencyStatus="1" />
</updg:before>

<updg:after>
 <msj_currency_v2 currencyID="1" currencyName="US dollar" currencySymbol="$" currencyStatus="1" />
</updg:after>

</updg:sync>
</root>

I am posting it into the following MSSQL table:

currencyID     int     4
currencyName     varchar     100
currencySymbol     varchar     3
currencyStatus     int     4

The updategram errors when currencySymbol="$".

The error via VB updategram test app:

"disallowed implicit conversion from datatype money to datatype varchar .. use the convert function
to run this query."

How do i run the "convert function" inside an updategram???? Or is a schema required .. if so can you please provide.

The problem is posting a "$" into a varchar field via an updategram ... MSSQL tries to convert it to
datatype money and errors.

If i post "A$" or "$u" the updategram works fine.

Thanks
greenrcAsked:
Who is Participating?
 
spcmnspffCommented:
Sorry once again that won't work.  Try this one:

Create Trigger trDollarSign ON CurencyTable Instead of Insert, Update AS

Insert Into CurrencyTable
Select * from Inserted I
WHERE CurrencySymbol <> '~$'

Insert Into CurrencyTable
(currencyID, currencyName, currencysymbol, currencyStatus)
Select  currencyID, currencyName, Char(36), currencyStatus
from Inserted
WHERE CurrencySymbol = '~$'
go
0
 
spcmnspffCommented:
The best way I see to handle this is with a trigger.

Inside your updategram insert this for the '~$' dollar sign.  (This may take a vb line or two to concatenate the tilda if the symble = "$".) Now create a trigger on your table like this:

Create Trigger trDollarSign ON CurencyTable Insert, Update AS

Update CT
Set CurrencySymbol = Char(36)
From CurrencyTable CT, Inserted I
WHERE CT.CurrencyID = I.CurrencyID
 AND I.CurrencySymbol = '~$'

go
0
 
spcmnspffCommented:
Oops, that create trigger statement won't run use this one:

Create Trigger trDollarSign ON CurencyTable for Insert, Update AS

Update CT
Set CurrencySymbol = Char(36)
From CurrencyTable CT, Inserted I
WHERE CT.CurrencyID = I.CurrencyID
AND I.CurrencySymbol = '~$'

go
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
spcmnspffCommented:
You could also use the more sophisticated instead of trigger here:


Create Trigger trDollarSign ON CurencyTable Instead of Insert, Update AS

Insert Into CurrencyTable
Select * from Inserted
WHERE I.CurrencySymbol <> '~$'

Insert Into CurrencyTable
(currencyID, currencyName, currencysymbol, currencyStatus)
Select  currencyID, currencyName, Char(36), currencyStatus
from Inserted
WHERE I.I.CurrencySymbol = '~$'

go
0
 
greenrcAuthor Commented:
going to test it out .. thanks ... get back to you soon.
0
 
spcmnspffCommented:
How did this turn out?
0
 
CleanupPingCommented:
greenrc:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
greenrcAuthor Commented:
worked fine .. thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.