Mystery of ADO.NET: DataAdapter.Update Trucates Decimal Digits

  • Thread starter Thread starter Prodip Saha
  • Start date Start date
P

Prodip Saha

I am trying to update a database field Numeric(12,2) using
DataAdapter.Update(Table). The update works but mysteriously all digits
after the decimal are truncated. If I try to update a value 999.99, I have
999.00 in the database!!!!

I checked the underlying column type in the table and it is System.Decimal.
The column is retaining all the digits when it is being passed on to the
DataAdapter to update the changed records. I use the OleDbCommandBuilder to
read the Parameters of the stored procedure that I use to update the
database. The amount field is defined as Numeric(12,2) in the stored
procedure but the Parameters collection reads the field type as
System.Decimal. As you can see my source column type and the parameter field
type are same BUT I loose the decimal.

I could have manually create the parameters and control the data types but
that is not an option when the fields are unknown.

Will appreciate if you can shed some lights.

Prodip Saha
 
Hi Prodip,

Prodip Saha said:
I am trying to update a database field Numeric(12,2) using
DataAdapter.Update(Table). The update works but mysteriously all digits
after the decimal are truncated. If I try to update a value 999.99, I have
999.00 in the database!!!!

I checked the underlying column type in the table and it is System.Decimal.
The column is retaining all the digits when it is being passed on to the
DataAdapter to update the changed records. I use the OleDbCommandBuilder to
read the Parameters of the stored procedure that I use to update the
database. The amount field is defined as Numeric(12,2) in the stored
procedure but the Parameters collection reads the field type as
System.Decimal. As you can see my source column type and the parameter field
type are same BUT I loose the decimal.

I could have manually create the parameters and control the data types but
that is not an option when the fields are unknown.
Click to expand...

You really should create it at design time.
You are invoking stored procedures you don't know the parameters?
Why would you do it?
 
Hi Miha,
First, thank you for the response.

In practice I know the parameters ahead of time. All my database activities
must be done through stored procedure(data layer dll). I have a generic
update method (takes a changed DataTable along with other parameters) in my
business layer which is used to update any tables in the database through
stored procedure. I am using the command builder to get Parameters
collection dynamically for any SP and to avoid hard coding. To get this
owrking my DataTable must have the matching columns with compatible data
types. This works for all data types except decimal (at least for now).

This is what's happening in my case (I am using Sybase ASE 12.0):
DECLARE @VAR DECIMAL
DECLARE @VAR1 NUMERIC(12,2)
--999.99 Value is converted to 999 before touching the stored procedure
--and .99 is lost according to Microsoft Documentation. The don't
SELECT @VAR=convert(decimal,999.99)

SELECT @VAR1=@VAR
SELECT @VAR VAR,@VAR1 VAR1

The result is:
@Var @Var1
999 999.00

Hope it explains the reason for IUnknown approach.
Prodip
 
Found the problem. The OleDbCommandBuilder.DeriveParameters(OleDbCommand)
does not set the scale of the decimal correctly. The stored proc parameter
was Numeric(12,2) but the ADO.Net interpreted it as Decimal with Precision
38 and Scale 0. I had to set the Scale to 2 for the Decimal param to fix the
problem.

I hope Microsoft would do a better job to build Parameters collection along
with their related properties.

Cheer!!
Prodip
 
Back
Top