greenrc
asked on
MSSQL UPDATEGRAM and '$'
Here is my updategram:
<?xml version="1.0" ?>
<root xmlns:updg="urn:schemas-mi crosoft-co m:xml-upda tegram">
<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
<?xml version="1.0" ?>
<root xmlns:updg="urn:schemas-mi
<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
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
going to test it out .. thanks ... get back to you soon.
How did this turn out?
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.
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.
ASKER
worked fine .. thanks
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