Disallowed implicit conversion from data type nvarchar to data type money

Disallowed implicit conversion from data type nvarchar to data type money

Post by Jeff Gochi » Wed, 12 Jun 2002 11:13:40



Hello everyone,

I have been trying to learn how to use the UpdateGram
feature of XMLSQL, however when I try to update a record
in my database I receive the following error message.

<?MSSQLError HResult="0x80040e07" Source="Microsoft OLE DB
Provider for SQL Server" Description="Disallowed implicit
conversion from data type nvarchar to data type money,
table 'ric.dbo.SSDRecov', column 'GrossAmt'. Use the
CONVERT function to run this query."?>

The values for the parameters are submitted from an ASP
Form using the post method.

The updategram is as follows:
<?xml version="1.0" ?>
<ldtData xmlns:updg="urn:schemas-microsoft-com:xml-
updategram">
  <updg:header>
    <updg:param name="SSDRecovID" />
    <updg:param name="oldGrossAmt" />
    <updg:param name="GrossAmt" />
  </updg:header>
  <updg:sync mapping-schema="schemas/ssdrecov.xml">
    <updg:before>
      <SSDRecov SSDRecovID="$SSDRecovID"
GrossAmt="$oldGrossAmt" />
    </updg:before>
    <updg:after>
      <SSDRecov GrossAmt="$GrossAmt" />
    </updg:after>
  </updg:sync>
 </ldtData>

The Schema file is as follows:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
        <xsd:element name="SSDRecov"
sql:relation="SSDRecov">
                <xsd:complexType>
                        <xsd:attribute name="SSDRecovID"
type="xsd:integer" />
                        <xsd:attribute name="ClaimNumber"
type="xsd:integer" />
                        <xsd:attribute
name="ClaimantNumber" type="xsd:integer" />
                        <xsd:attribute name="AsOfDate"
type="xsd:dateTime" />
                        <xsd:attribute name="GrossAmt"
type="xsd:decimal" />
                        <xsd:attribute name="ResinsAmt"
type="xsd:decimal" />
                        <xsd:attribute name="RoundedNet"
type="xsd:decimal" />
                </xsd:complexType>
        </xsd:element>
</xsd:schema>

When I hard code the values instead of using the
parameters everything works fine.  What am I doing wrong
here?

-- Jeff Gochin

 
 
 

Disallowed implicit conversion from data type nvarchar to data type money

Post by Bryant Like » Wed, 12 Jun 2002 11:35:42



[snip]

Quote:> When I hard code the values instead of using the
> parameters everything works fine.  What am I doing wrong
> here?

Most likely your parameters are not being passed in. Try coding default
values and see if those get entered instead of the parameters that you're
passing in. How are you passing the params in?

--
Bryant

 
 
 

Disallowed implicit conversion from data type nvarchar to data type money

Post by Jeff Gochi » Wed, 12 Jun 2002 11:53:47


I will try your suggestion, in the mean time...

I am passing the parameters using the following HTML form which is
generated via XSLT via XMLSQL:

<html>
<head>
<META http-equiv="Content-Type" content="text/html; charset=utf-8">
<LINK rel="stylesheet" type="text/css"
href="http://bigboy/ric-acct/regis.css">
<script>
  function handleAddNew()
  {
    if( validate() )
    {
      theform.action = 'http://merlin/regis/acct/ssdrecovadd.xml';  
      theform.submit();                                
    }

    return false;
  }

  function handleUpdate()
  {
    if( validate() )
    {
       theform.action = 'http://merlin/regis/acct/ssdrecovupdate.xml';    
       theform.submit();
    }
    return false;
  }

  function handleDelete()
  {
     if(confirm("Are you sure?"))
     {
        theform.action = 'http://merlin/regis/acct/ssdrecovdelete.xml';  
        theform.submit();                                                                                       return true;
     }else{                                                     return false;
     }
  }

  function validate()
  {
        return true;
  }
</script>
</head>
<body>
<form name="theform" id="theform" action="" method="post"><input
name="SSDRecovID" id="SSDRecovID" type="hidden" value="3"><input
name="oldClaimNumber" id="oldClaimNumber" type="hidden"
value="17009"><input name="oldClaimantNumber" id="oldClaimantNumber"
type="hidden" value="1"><input name="oldAsOfDate" id="oldAsOfDate"
type="hidden" value="12/31/2001"><input name="oldGrossAmt"
id="oldGrossAmt" type="hidden" value="0"><input name="oldReinsAmt"
id="oldReinsAmt" type="hidden" value="0"><input name="oldRoundedNet"
id="oldRoundedNet" type="hidden" value="55000"><table>
<tr>
<td>
<table class="editor" width="400">
<tr>
<th width="100">Claim #:</th>
<td><input name="ClaimNumber" id="ClaimNumber" type="text" value="17009"
size="2"></td>
<td></td>
<th>Claimant #:</th>
<td><input name="ClaimantNumber" id="ClaimantNumber" type="text"
value="1" size="2"></td>
</tr>
<tr>
<th>Name:</th>
<td colspan="4">BARTH, EDWARD J.</td>
</tr>
<tr>
<th>As Of Date:</th>
<td colspan="4"><input name="AsOfDate" id="AsOfDate" type="text"
value="12/31/2001" size="10"></td>
</tr>
<tr>
<th>Gross $$:</th>
<td colspan="4"><input name="GrossAmt" id="GrossAmt" type="text"
value="0" size="10"></td>
</tr>
<tr>
<th>Reins $$:</th>
<td colspan="4"><input name="ReinsAmt" id="ReinsAmt" type="text"
value="0" size="10"></td>
</tr>
<tr>
<th>Rounded Net:</th>
<td colspan="4"><input name="RoundedNet" id="RoundedNet" type="text"
value="55000" size="10"></td>
</tr>
</table>
</td>
<td valign="top" width="125" align="right">
<table>
<tr>
<td><input class="btn" name="undo" id="undo" type="reset"
value="UNDO"></td>
</tr>
<tr>
<td><input class="btn" name="addnew" id="addnew" type="button"
value="ADD NEW" onclick="handleAddNew();"></td>
</tr>
<tr>
<td><input class="btn" name="update" id="update" type="button"
value="UPDATE" onclick="handleUpdate();"></td>
</tr>
<tr>
<td><input class="btn" name="delete" id="delete" type="button"
value="DELETE" onclick="handleDelete();"></td>
</tr>
</table>
</td>
</tr>
</table>
</form>
</body>
</html>

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Disallowed implicit conversion from data type nvarchar to data type money

Post by Jeff Gochi » Wed, 12 Jun 2002 12:01:50


I tried suppling default values for the params and I get the same
result.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Disallowed implicit conversion from data type nvarchar to data type money

Post by Jeff Gochi » Wed, 12 Jun 2002 13:01:52


More information.  I been purusing this newsgroup and I have noticed
that my problem is quite common.  I have also noticed  that no one have
solved this one yet.

It seems that there are  people out there that have made this work.  If
you have successfully executed an UPDATEGRAM please post your complete
system configuration.

In the mean time I decided to take a look at what is being posted to the
SQL server via the Profiler. Here is what I got:

exec sp_executesql N' SET XACT_ABORT ON
BEGIN TRAN








update, unique identifier required  Transaction aborted '', 16, 1)

update, no updatable rows found  Transaction aborted '', 16, 1)


SET XACT_ABORT OFF

nvarchar(5)', N'1', N'20000', N'20000'

Notice the explicit declaration of the parameters as nvarchar.  I am
wondering if there is a way to declare the type for an updg:param?
(i.e. <updg:param type='sql:int'\> )

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Disallowed implicit conversion from data type nvarchar to data type money

Post by Deane Landret » Fri, 14 Jun 2002 07:40:02


Seems as though the only solution is to make the sql datatype decimal
rather than money in the database.
The nvarchar-decimal implicity conversion works ok when defined like this.

A  sql column defined as money is functionally equivalent to decimal(19,4)
apart from the fact that you can't use the currency symbol in front of the
value.


> More information.  I been purusing this newsgroup and I have noticed
> that my problem is quite common.  I have also noticed  that no one have
> solved this one yet.

> It seems that there are  people out there that have made this work.  If
> you have successfully executed an UPDATEGRAM please post your complete
> system configuration.

> In the mean time I decided to take a look at what is being posted to the
> SQL server via the Profiler. Here is what I got:

> exec sp_executesql N' SET XACT_ABORT ON
> BEGIN TRAN








> update, unique identifier required  Transaction aborted '', 16, 1)

> update, no updatable rows found  Transaction aborted '', 16, 1)


> SET XACT_ABORT OFF

> nvarchar(5)', N'1', N'20000', N'20000'

> Notice the explicit declaration of the parameters as nvarchar.  I am
> wondering if there is a way to declare the type for an updg:param?
> (i.e. <updg:param type='sql:int'\> )

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

Disallowed implicit conversion from data type nvarchar to data type money

Post by Deane Landret » Fri, 14 Jun 2002 07:41:13


Seems as though the only solution is to make the sql datatype decimal
rather than money in the database.
The nvarchar-decimal implicity conversion works ok when defined like this.

A  sql column defined as money is functionally equivalent to decimal(19,4)
apart from the fact that you can't use the currency symbol in front of the
value.


> More information.  I been purusing this newsgroup and I have noticed
> that my problem is quite common.  I have also noticed  that no one have
> solved this one yet.

> It seems that there are  people out there that have made this work.  If
> you have successfully executed an UPDATEGRAM please post your complete
> system configuration.

> In the mean time I decided to take a look at what is being posted to the
> SQL server via the Profiler. Here is what I got:

> exec sp_executesql N' SET XACT_ABORT ON
> BEGIN TRAN








> update, unique identifier required  Transaction aborted '', 16, 1)

> update, no updatable rows found  Transaction aborted '', 16, 1)


> SET XACT_ABORT OFF

> nvarchar(5)', N'1', N'20000', N'20000'

> Notice the explicit declaration of the parameters as nvarchar.  I am
> wondering if there is a way to declare the type for an updg:param?
> (i.e. <updg:param type='sql:int'\> )

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

Disallowed implicit conversion from data type nvarchar to data type money

Post by Jeff Gochi » Fri, 14 Jun 2002 08:17:33


Outstanding!!!

That worked like a charm.

My only question is how did you determine that decimal is
implicitly converted and money is not.  (Other than money
wasn't working for me).  I asking so that if I run into a
similar situation again, I will have something to refer to.

Quote:>-----Original Message-----
>Seems as though the only solution is to make the sql
datatype decimal
>rather than money in the database.
>The nvarchar-decimal implicity conversion works ok when
defined like this.

>A  sql column defined as money is functionally equivalent
to decimal(19,4)
>apart from the fact that you can't use the currency

symbol in front of the
>value.



>> More information.  I been purusing this newsgroup and I
have noticed
>> that my problem is quite common.  I have also noticed  
that no one have
>> solved this one yet.

>> It seems that there are  people out there that have
made this work.  If
>> you have successfully executed an UPDATEGRAM please
post your complete
>> system configuration.

>> In the mean time I decided to take a look at what is
being posted to the
>> SQL server via the Profiler. Here is what I got:

>> exec sp_executesql N' SET XACT_ABORT ON
>> BEGIN TRAN






AND  (



Description: Ambiguous

- Show quoted text -

>> update, unique identifier required  Transaction
aborted '', 16, 1)

Description: Empty
>> update, no updatable rows found  Transaction
aborted '', 16, 1)


>> SET XACT_ABORT OFF


>> nvarchar(5)', N'1', N'20000', N'20000'

>> Notice the explicit declaration of the parameters as
nvarchar.  I am
>> wondering if there is a way to declare the type for an
updg:param?
>> (i.e. <updg:param type='sql:int'\> )

>> *** Sent via Developersdex http://www.developersdex.com
***
>> Don't just participate in USENET...get rewarded for it!

>.

 
 
 

Disallowed implicit conversion from data type nvarchar to data type money

Post by Deane Landret » Fri, 14 Jun 2002 09:46:25


Have a look at  sql server books online. search for 'implicit data type
conversions'
or CAST and CONVERT  in the index
There is a table describing what can and can't be converted implictitly

What I can't  understand is the why  the SQL XML generates a query using an
nvarchar for the parameter and  ignores  what is defined in sql:datatype  I
have the same problem with the timestamp column and  can't devise a
workaround for it.


> Outstanding!!!

> That worked like a charm.

> My only question is how did you determine that decimal is
> implicitly converted and money is not.  (Other than money
> wasn't working for me).  I asking so that if I run into a
> similar situation again, I will have something to refer to.

> >-----Original Message-----
> >Seems as though the only solution is to make the sql
> datatype decimal
> >rather than money in the database.
> >The nvarchar-decimal implicity conversion works ok when
> defined like this.

> >A  sql column defined as money is functionally equivalent
> to decimal(19,4)
> >apart from the fact that you can't use the currency
> symbol in front of the
> >value.



> >> More information.  I been purusing this newsgroup and I
> have noticed
> >> that my problem is quite common.  I have also noticed
> that no one have
> >> solved this one yet.

> >> It seems that there are  people out there that have
> made this work.  If
> >> you have successfully executed an UPDATEGRAM please
> post your complete
> >> system configuration.

> >> In the mean time I decided to take a look at what is
> being posted to the
> >> SQL server via the Profiler. Here is what I got:

> >> exec sp_executesql N' SET XACT_ABORT ON
> >> BEGIN TRAN





> AND  (





> Description: Ambiguous
> >> update, unique identifier required  Transaction
> aborted '', 16, 1)

> Description: Empty
> >> update, no updatable rows found  Transaction
> aborted '', 16, 1)


> >> SET XACT_ABORT OFF


> >> nvarchar(5)', N'1', N'20000', N'20000'

> >> Notice the explicit declaration of the parameters as
> nvarchar.  I am
> >> wondering if there is a way to declare the type for an
> updg:param?
> >> (i.e. <updg:param type='sql:int'\> )

> >> *** Sent via Developersdex http://www.developersdex.com
> ***
> >> Don't just participate in USENET...get rewarded for it!

> >.

 
 
 

Disallowed implicit conversion from data type nvarchar to data type money

Post by Jeff Gochi » Sat, 15 Jun 2002 11:51:16


Thanks I will review that secion of the BOL.

Let me make sure I understand; are you saying that
<updg:param name="SomeParam" type="sql:int"> should work?  
Personally I feel it should, but there doesn't seem to be
any documetation that supports the theory. Oh well, maybe
in SQLXML 4.0 :)

Quote:>-----Original Message-----
>Have a look at  sql server books online. search

for 'implicit data type
Quote:>conversions'
>or CAST and CONVERT  in the index
>There is a table describing what can and can't be

converted implictitly
Quote:

>What I can't  understand is the why  the SQL XML

generates a query using an
Quote:>nvarchar for the parameter and  ignores  what is defined
in sql:datatype  I
>have the same problem with the timestamp column and  
can't devise a
>workaround for it.

 
 
 

1. Data type conversion error when saving numeric data types

I have a textbox which is bound to an Access database field (numeric data
type).
If I type a digit into the box, then the record can be saved ok.  If I
remove the digit again and try to save the record, then I get a data type
conversion error.
Does anyone know a way around this, so that if a user blanks the box, then
the record can be saved, and the box remains blank?

I know I can do this in an Access form, but can not seem to just blank the
box in a VB form.

Even 'text1 = val(text1)' just puts a zero in the box, but I do not want a
setting of zero if the user wants no setting to be printed.

Any help would be very much appreciated, as this has been bugging be for a
while now.

Thanks

Ed

2. How to do the SELECT?

3. conversion of ODBC SQL data types to ODBC C data types

4. How to build up an adress-file?

5. UPDATEGRAM datatype problem (nvarchar to data type money)

6. weird tnslsnr ports?

7. Detecting Implicit data type conversions

8. Regarding Replication

9. Implicit data type conversion

10. Money field type same as Currency data type??

11. Money data type conversion problem?

12. Data Transfer from Long data type to SQL Server Text data type column

13. Converting OLEDB data types to VC++ data types