Error converting numeric to decimal?

  • Thread starter Thread starter STom
  • Start date Start date
S

STom

I have a component that adds a few decimal numbers together to arrive at a
total but when I use my .Update function for my data adapter, I get the
following exception:

{System.Data.SqlClient.SqlException}
[System.Data.SqlClient.SqlException]:
{System.Data.SqlClient.SqlException}
HelpLink: Nothing
InnerException: Nothing
Message: "Error converting data type numeric to decimal."
Source: ".Net SqlClient Data Provider"
StackTrace: " at System.Data.Common.DbDataAdapter.Update(DataRow[]
dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)
at BrightCompass.XALibrary.XADataAccess.UpdateCaseData(DataSet ds,
SqlConnection conn) in C:\Projects 2\Executive Advantage\DotNet
Version\EXE\ExecutiveAdvantage\XALibrary\XALibrary\XADataAccess.vb:line 441"
TargetSite: {System.Reflection.RuntimeMethodInfo}


Here is the code that calculates the number:
Me.BaseXACase.TotalAssets = CDec(Me.BaseXACase.CashNearCash) +
CDec(Me.BaseXACase.AccountsReceivable) + _
CDec(Me.BaseXACase.Inventory) +
CDec(Me.BaseXACase.OtherCurrentAssets)

In my update stored procedure,my parameters are declared as:

CREATE PROCEDURE dbo.UpdateLocalData
(
@intCaseDataID int,
@intModelID int,
@decNetIncome decimal (18,8),
@decTotalAssets decimal,
@intMonth int,
@intYear int,
@dtModifiedDate datetime
)

In my table design, the TotalAssets field is a decimal and it is also set to
(18,8).

So what I don't understand is why it thinks this is a numeric and not a
decimal. I have looked at this thing until I'm color blind. Can someone tell
me what I'm not seeing here?

Thanks.

STom
 
Miha,

Here is my update command:
----------------------------------------------------------------------------
------
Try
With adp
.UpdateCommand = New SqlCommand
With .UpdateCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "UpdateLocalData"
.Connection = conn

.Parameters.Add(New SqlParameter("@intCaseDataID",
SqlDbType.Int))
.Parameters("@intCaseDataID").Direction =
ParameterDirection.Input
.Parameters("@intCaseDataID").SourceColumn =
"CaseDataID"
.Parameters("@intCaseDataID").SourceVersion =
DataRowVersion.Current

.Parameters.Add(New SqlParameter("@intModelID",
SqlDbType.Int))
.Parameters("@intModelID").Direction =
ParameterDirection.Input
.Parameters("@intModelID").SourceColumn = "ModelID"
.Parameters("@intModelID").SourceVersion =
DataRowVersion.Current

'NetIncome
.Parameters.Add(New SqlParameter("@decNetIncome",
SqlDbType.Decimal))
.Parameters("@decNetIncome").Direction =
ParameterDirection.Input
.Parameters("@decNetIncome").SourceColumn = "NetIncome"
.Parameters("@decNetIncome").SourceVersion =
DataRowVersion.Current

'TotalAssets
.Parameters.Add(New SqlParameter("@decTotalAssets",
SqlDbType.Decimal))
.Parameters("@decTotalAssets").Direction =
ParameterDirection.Input
.Parameters("@decTotalAssets").SourceColumn =
"TotalAssets"
.Parameters("@decTotalAssets").SourceVersion =
DataRowVersion.Current


'Month
.Parameters.Add(New SqlParameter("@intMonth",
SqlDbType.Int))
.Parameters("@intMonth").Direction =
ParameterDirection.Input
.Parameters("@intMonth").SourceColumn = "Month"
.Parameters("@intMonth").SourceVersion =
DataRowVersion.Current


'Year
.Parameters.Add(New SqlParameter("@intYear",
SqlDbType.Int))
.Parameters("@intYear").Direction =
ParameterDirection.Input
.Parameters("@intYear").SourceColumn = "Year"
.Parameters("@intYear").SourceVersion =
DataRowVersion.Current


'ModifiedDate
.Parameters.Add(New SqlParameter("@dtModifiedDate",
SqlDbType.DateTime))
.Parameters("@dtModifiedDate").Direction =
ParameterDirection.Input
.Parameters("@dtModifiedDate").Value = Now
.Parameters("@dtModifiedDate").SourceVersion =
DataRowVersion.Current
End With
End With
Catch ex As Exception
End Try
----------------------------------------------------------------------------
--------------

Thanks.

STom
Miha Markic said:
Hi STom,

How are your commands defined?

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com

STom said:
I have a component that adds a few decimal numbers together to arrive at a
total but when I use my .Update function for my data adapter, I get the
following exception:

{System.Data.SqlClient.SqlException}
[System.Data.SqlClient.SqlException]:
{System.Data.SqlClient.SqlException}
HelpLink: Nothing
InnerException: Nothing
Message: "Error converting data type numeric to decimal."
Source: ".Net SqlClient Data Provider"
StackTrace: " at System.Data.Common.DbDataAdapter.Update(DataRow[]
dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)
at BrightCompass.XALibrary.XADataAccess.UpdateCaseData(DataSet ds,
SqlConnection conn) in C:\Projects 2\Executive Advantage\DotNet
Version\EXE\ExecutiveAdvantage\XALibrary\XALibrary\XADataAccess.vb:line 441"
TargetSite: {System.Reflection.RuntimeMethodInfo}


Here is the code that calculates the number:
Me.BaseXACase.TotalAssets = CDec(Me.BaseXACase.CashNearCash) +
CDec(Me.BaseXACase.AccountsReceivable) + _
CDec(Me.BaseXACase.Inventory) +
CDec(Me.BaseXACase.OtherCurrentAssets)

In my update stored procedure,my parameters are declared as:

CREATE PROCEDURE dbo.UpdateLocalData
(
@intCaseDataID int,
@intModelID int,
@decNetIncome decimal (18,8),
@decTotalAssets decimal,
@intMonth int,
@intYear int,
@dtModifiedDate datetime
)

In my table design, the TotalAssets field is a decimal and it is also
Click to expand...
set
to
(18,8).

So what I don't understand is why it thinks this is a numeric and not a
decimal. I have looked at this thing until I'm color blind. Can someone tell
me what I'm not seeing here?

Thanks.

STom
Click to expand...
Click to expand...
 
Hi STom,

Try defining decimal parameters like this:
decimal (18,8)
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@dc", System.Data.SqlDbType.Decimal, 9,
System.Data.ParameterDirection.Input, false, ((System.Byte)(18)),
((System.Byte)(8)), "dc", System.Data.DataRowVersion.Current, null));

decimal

this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@dcn", System.Data.SqlDbType.Decimal, 9,
System.Data.ParameterDirection.Input, false, ((System.Byte)(18)),
((System.Byte)(0)), "dcn", System.Data.DataRowVersion.Current, null));

And try it.


--
Miha Markic - DXSquad/RightHand .NET consulting & software development
miha at rthand com

STom said:
Miha,

Here is my update command:
-------------------------------------------------------------------------- --
------
Try
With adp
.UpdateCommand = New SqlCommand
With .UpdateCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "UpdateLocalData"
.Connection = conn

.Parameters.Add(New SqlParameter("@intCaseDataID",
SqlDbType.Int))
.Parameters("@intCaseDataID").Direction =
ParameterDirection.Input
.Parameters("@intCaseDataID").SourceColumn =
"CaseDataID"
.Parameters("@intCaseDataID").SourceVersion =
DataRowVersion.Current

.Parameters.Add(New SqlParameter("@intModelID",
SqlDbType.Int))
.Parameters("@intModelID").Direction =
ParameterDirection.Input
.Parameters("@intModelID").SourceColumn = "ModelID"
.Parameters("@intModelID").SourceVersion =
DataRowVersion.Current

'NetIncome
.Parameters.Add(New SqlParameter("@decNetIncome",
SqlDbType.Decimal))
.Parameters("@decNetIncome").Direction =
ParameterDirection.Input
.Parameters("@decNetIncome").SourceColumn = "NetIncome"
.Parameters("@decNetIncome").SourceVersion =
DataRowVersion.Current

'TotalAssets
.Parameters.Add(New SqlParameter("@decTotalAssets",
SqlDbType.Decimal))
.Parameters("@decTotalAssets").Direction =
ParameterDirection.Input
.Parameters("@decTotalAssets").SourceColumn =
"TotalAssets"
.Parameters("@decTotalAssets").SourceVersion =
DataRowVersion.Current


'Month
.Parameters.Add(New SqlParameter("@intMonth",
SqlDbType.Int))
.Parameters("@intMonth").Direction =
ParameterDirection.Input
.Parameters("@intMonth").SourceColumn = "Month"
.Parameters("@intMonth").SourceVersion =
DataRowVersion.Current


'Year
.Parameters.Add(New SqlParameter("@intYear",
SqlDbType.Int))
.Parameters("@intYear").Direction =
ParameterDirection.Input
.Parameters("@intYear").SourceColumn = "Year"
.Parameters("@intYear").SourceVersion =
DataRowVersion.Current


'ModifiedDate
.Parameters.Add(New SqlParameter("@dtModifiedDate",
SqlDbType.DateTime))
.Parameters("@dtModifiedDate").Direction =
ParameterDirection.Input
.Parameters("@dtModifiedDate").Value = Now
.Parameters("@dtModifiedDate").SourceVersion =
DataRowVersion.Current
End With
End With
Catch ex As Exception
End Try
-------------------------------------------------------------------------- --
--------------

Thanks.

STom
Miha Markic said:
Hi STom,

How are your commands defined?
Click to expand...
at
a
total but when I use my .Update function for my data adapter, I get the
following exception:

{System.Data.SqlClient.SqlException}
[System.Data.SqlClient.SqlException]:
{System.Data.SqlClient.SqlException}
HelpLink: Nothing
InnerException: Nothing
Message: "Error converting data type numeric to decimal."
Source: ".Net SqlClient Data Provider"
StackTrace: " at System.Data.Common.DbDataAdapter.Update(DataRow[]
dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)
at BrightCompass.XALibrary.XADataAccess.UpdateCaseData(DataSet ds,
SqlConnection conn) in C:\Projects 2\Executive Advantage\DotNet
Click to expand...
Version\EXE\ExecutiveAdvantage\XALibrary\XALibrary\XADataAccess.vb:line
441"
TargetSite: {System.Reflection.RuntimeMethodInfo}


Here is the code that calculates the number:
Me.BaseXACase.TotalAssets = CDec(Me.BaseXACase.CashNearCash) +
CDec(Me.BaseXACase.AccountsReceivable) + _
CDec(Me.BaseXACase.Inventory) +
CDec(Me.BaseXACase.OtherCurrentAssets)

In my update stored procedure,my parameters are declared as:

CREATE PROCEDURE dbo.UpdateLocalData
(
@intCaseDataID int,
@intModelID int,
@decNetIncome decimal (18,8),
@decTotalAssets decimal,
@intMonth int,
@intYear int,
@dtModifiedDate datetime
)

In my table design, the TotalAssets field is a decimal and it is also
Click to expand...
set
to
(18,8).

So what I don't understand is why it thinks this is a numeric and not a
decimal. I have looked at this thing until I'm color blind. Can
Click to expand...
someone
tell
me what I'm not seeing here?

Thanks.

STom
Click to expand...
Click to expand...
Click to expand...
 
Miha,

Here is what I tried:
1. In my stored proc, I set the parameter to:
@decTotalAssets decimal (18,8)

then, in my function for my update command, I did the following:
..Parameters.Add(New System.Data.SqlClient.SqlParameter("@decTotalAssets",
System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input,
False, 18, 8, "TotalAssets", System.Data.DataRowVersion.Current, Nothing))

2. Next I tried setting my stored proc parameter to:
@decTotalAssets decimal

In the Update command I did:
..Parameters.Add(New System.Data.SqlClient.SqlParameter("@decTotalAssets",
System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input,
False, 18, 0, "TotalAssets", System.Data.DataRowVersion.Current, Nothing))

Both of these still resulted in the same exception.

Thanks.

STom
Miha Markic said:
Hi STom,

Try defining decimal parameters like this:
decimal (18,8)
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@dc", System.Data.SqlDbType.Decimal, 9,
System.Data.ParameterDirection.Input, false, ((System.Byte)(18)),
((System.Byte)(8)), "dc", System.Data.DataRowVersion.Current, null));

decimal

this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@dcn", System.Data.SqlDbType.Decimal, 9,
System.Data.ParameterDirection.Input, false, ((System.Byte)(18)),
((System.Byte)(0)), "dcn", System.Data.DataRowVersion.Current, null));

And try it.


--
Miha Markic - DXSquad/RightHand .NET consulting & software development
miha at rthand com

STom said:
Miha,

Here is my update command:
--------------------------------------------------------------------------
--
------
Try
With adp
.UpdateCommand = New SqlCommand
With .UpdateCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "UpdateLocalData"
.Connection = conn

.Parameters.Add(New SqlParameter("@intCaseDataID",
SqlDbType.Int))
.Parameters("@intCaseDataID").Direction =
ParameterDirection.Input
.Parameters("@intCaseDataID").SourceColumn =
"CaseDataID"
.Parameters("@intCaseDataID").SourceVersion =
DataRowVersion.Current

.Parameters.Add(New SqlParameter("@intModelID",
SqlDbType.Int))
.Parameters("@intModelID").Direction =
ParameterDirection.Input
.Parameters("@intModelID").SourceColumn = "ModelID"
.Parameters("@intModelID").SourceVersion =
DataRowVersion.Current

'NetIncome
.Parameters.Add(New SqlParameter("@decNetIncome",
SqlDbType.Decimal))
.Parameters("@decNetIncome").Direction =
ParameterDirection.Input
.Parameters("@decNetIncome").SourceColumn = "NetIncome"
.Parameters("@decNetIncome").SourceVersion =
DataRowVersion.Current

'TotalAssets
.Parameters.Add(New SqlParameter("@decTotalAssets",
SqlDbType.Decimal))
.Parameters("@decTotalAssets").Direction =
ParameterDirection.Input
.Parameters("@decTotalAssets").SourceColumn =
"TotalAssets"
.Parameters("@decTotalAssets").SourceVersion =
DataRowVersion.Current


'Month
.Parameters.Add(New SqlParameter("@intMonth",
SqlDbType.Int))
.Parameters("@intMonth").Direction =
ParameterDirection.Input
.Parameters("@intMonth").SourceColumn = "Month"
.Parameters("@intMonth").SourceVersion =
DataRowVersion.Current


'Year
.Parameters.Add(New SqlParameter("@intYear",
SqlDbType.Int))
.Parameters("@intYear").Direction =
ParameterDirection.Input
.Parameters("@intYear").SourceColumn = "Year"
.Parameters("@intYear").SourceVersion =
DataRowVersion.Current


'ModifiedDate
.Parameters.Add(New SqlParameter("@dtModifiedDate",
SqlDbType.DateTime))
.Parameters("@dtModifiedDate").Direction =
ParameterDirection.Input
.Parameters("@dtModifiedDate").Value = Now
.Parameters("@dtModifiedDate").SourceVersion =
DataRowVersion.Current
End With
End With
Catch ex As Exception
End Try
--------------------------------------------------------------------------
--
--------------

Thanks.

STom
Click to expand...
arrive
at
a
total but when I use my .Update function for my data adapter, I get the
following exception:

{System.Data.SqlClient.SqlException}
[System.Data.SqlClient.SqlException]:
{System.Data.SqlClient.SqlException}
HelpLink: Nothing
InnerException: Nothing
Message: "Error converting data type numeric to decimal."
Source: ".Net SqlClient Data Provider"
StackTrace: " at System.Data.Common.DbDataAdapter.Update(DataRow[]
dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)
at BrightCompass.XALibrary.XADataAccess.UpdateCaseData(DataSet ds,
SqlConnection conn) in C:\Projects 2\Executive Advantage\DotNet
Version\EXE\ExecutiveAdvantage\XALibrary\XALibrary\XADataAccess.vb:line
441"
TargetSite: {System.Reflection.RuntimeMethodInfo}


Here is the code that calculates the number:
Me.BaseXACase.TotalAssets = CDec(Me.BaseXACase.CashNearCash) +
CDec(Me.BaseXACase.AccountsReceivable) + _
CDec(Me.BaseXACase.Inventory) +
CDec(Me.BaseXACase.OtherCurrentAssets)

In my update stored procedure,my parameters are declared as:

CREATE PROCEDURE dbo.UpdateLocalData
(
@intCaseDataID int,
@intModelID int,
@decNetIncome decimal (18,8),
@decTotalAssets decimal,
@intMonth int,
@intYear int,
@dtModifiedDate datetime
)

In my table design, the TotalAssets field is a decimal and it is
Click to expand...
also
set
to
(18,8).

So what I don't understand is why it thinks this is a numeric and
Click to expand...
Click to expand...
not
a
Click to expand...
Click to expand...
 
Miha,

Here is the structure of the table in the database which matches the
structure of the datatable:

CREATE TABLE [dbo].[tblCaseData] (
[CaseDataID] [int] IDENTITY (1, 1) NOT NULL ,
[ModelID] [int] NOT NULL ,
[CaseTypeID] [int] NOT NULL ,
[TotalAssets] [decimal](18, 8) NULL ,
[NetIncome] [decimal](18, 8) NULL ,
[Month] [int] NULL ,
[Year] [int] NULL ,
[CreatedDate] [datetime] NULL ,
[ModifiedDate] [datetime] NULL
) ON [PRIMARY]
 
Hi STom,

No, no. The structure of table in your dataset.

--
Miha Markic - DXSquad/RightHand .NET consulting & software development
miha at rthand com



STom said:
Miha,

Here is the structure of the table in the database which matches the
structure of the datatable:

CREATE TABLE [dbo].[tblCaseData] (
[CaseDataID] [int] IDENTITY (1, 1) NOT NULL ,
[ModelID] [int] NOT NULL ,
[CaseTypeID] [int] NOT NULL ,
[TotalAssets] [decimal](18, 8) NULL ,
[NetIncome] [decimal](18, 8) NULL ,
[Month] [int] NULL ,
[Year] [int] NULL ,
[CreatedDate] [datetime] NULL ,
[ModifiedDate] [datetime] NULL
) ON [PRIMARY]
Click to expand...
 
Miha,

I create my datatable by doing a SELECT * from the table. It is exactly like
the table in the database.

Now, on a positive note, I have decided to start from scratch on this table
(using a different table name) to see if I mistyped anything. As of right
now, it is working without any problem.

This baffles me to no end, the code looks identical. I'll pound on it more
tonight to see if it really consistently works.

Thanks for all of your help.

STom
Miha Markic said:
Hi STom,

No, no. The structure of table in your dataset.

--
Miha Markic - DXSquad/RightHand .NET consulting & software development
miha at rthand com



STom said:
Miha,

Here is the structure of the table in the database which matches the
structure of the datatable:

CREATE TABLE [dbo].[tblCaseData] (
[CaseDataID] [int] IDENTITY (1, 1) NOT NULL ,
[ModelID] [int] NOT NULL ,
[CaseTypeID] [int] NOT NULL ,
[TotalAssets] [decimal](18, 8) NULL ,
[NetIncome] [decimal](18, 8) NULL ,
[Month] [int] NULL ,
[Year] [int] NULL ,
[CreatedDate] [datetime] NULL ,
[ModifiedDate] [datetime] NULL
) ON [PRIMARY]

Miha Markic said:
Hi STom,

How is your DataTable structure?

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com

Miha,

Here is what I tried:
1. In my stored proc, I set the parameter to:
@decTotalAssets decimal (18,8)

then, in my function for my update command, I did the following:
.Parameters.Add(New System.Data.SqlClient.SqlParameter("@decTotalAssets",
System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input,
False, 18, 8, "TotalAssets", System.Data.DataRowVersion.Current, Nothing))

2. Next I tried setting my stored proc parameter to:
@decTotalAssets decimal

In the Update command I did:
.Parameters.Add(New System.Data.SqlClient.SqlParameter("@decTotalAssets",
System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input,
False, 18, 0, "TotalAssets", System.Data.DataRowVersion.Current, Nothing))

Both of these still resulted in the same exception.
Click to expand...
Click to expand...
Click to expand...
 
It's normally caused by your decimal(18,8) can take up to xxxxxxxxxx.xxxxxxxxx digits.
If the users key in value more than 9999999999, you would normally get this error

Another thing you need to do is: your total asset needs to be defined too in stored proc to decimal(18,8), otherwise you will get your decimal behind truncated.
 
Back
Top