Writing a Decimal to SqlServer loses the fraction

[Guest]

Hi;

I have a Decimal type in SqlServer and I write a new Decimal (1.23) setting
a parameter of type DbDecimal to it and all it writes is a 1. The decimal is
gone. What am I missing?

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Miha Markic]

How exactly does your code look like?

 

[Guest]

Hi;

I call the below method passing it a Decimal as the object value.

public override void Set(int ind, object value)
{

if (value == null)
value = DBNull.Value;

SqlParameter param = new SqlParameter();
param.ParameterName = "@p" + ind;
param.Value = value;

cmd.Parameters.Add(param);
}


--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

How exactly does your code look like?

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Hi;

I have a Decimal type in SqlServer and I write a new Decimal (1.23)
setting
a parameter of type DbDecimal to it and all it writes is a 1. The decimal
is
gone. What am I missing?

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Norman Yuan]

If it is a decimal value, you need to specify the parameter's Precision
and/or Scale propertie. By the default Scale property is 0, meaning decimal
part of the number is cut off.

Hi;

I call the below method passing it a Decimal as the object value.

public override void Set(int ind, object value)
{

if (value == null)
value = DBNull.Value;

SqlParameter param = new SqlParameter();
param.ParameterName = "@p" + ind;
param.Value = value;

cmd.Parameters.Add(param);
}


--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

How exactly does your code look like?

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Hi;

I have a Decimal type in SqlServer and I write a new Decimal (1.23)
setting
a parameter of type DbDecimal to it and all it writes is a 1. The
decimal
is
gone. What am I missing?

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Guest]

Ok, that makes sense. Question for this then. I am trying to write a general
use library. The Decimal class does not seem to have a Precision or Scale
equivilent. Is there some way I can query the Decimal object to determine
what I should set these to?

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

If it is a decimal value, you need to specify the parameter's Precision
and/or Scale propertie. By the default Scale property is 0, meaning decimal
part of the number is cut off.

Hi;

I call the below method passing it a Decimal as the object value.

public override void Set(int ind, object value)
{

if (value == null)
value = DBNull.Value;

SqlParameter param = new SqlParameter();
param.ParameterName = "@p" + ind;
param.Value = value;

cmd.Parameters.Add(param);
}


--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

How exactly does your code look like?

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Hi;

I have a Decimal type in SqlServer and I write a new Decimal (1.23)
setting
a parameter of type DbDecimal to it and all it writes is a 1. The
decimal
is
gone. What am I missing?

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[WenYuan Wang [MSFT]]

Hi Dave,

Decimal class hasn't a Precision or Scale. You may consider adding
additional parameter (Sacle). Developer can set this parameter by himself.
public Set(int ind, object value, int sacle)

Please let me know if you have any further issue on this. I'm glad to
assist you.
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

What would work best is if the connector would use whatever scale the column
in the database was set to for the decimal so nothing had to be passed. Is
there a way to do that?

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[William \(Bill\) Vaughn]

I faced the same problem when building CLR user-defined types. After
considerable research I discovered the trick:
Use SqlFacet. I hope this helps.

''' <summary>
''' Stores Money Value
''' </summary>
' Set the SqlFacet attribute to properly describe
' the precision and scale. Defaults to 18,0
<SqlFacet(precision:=15, scale:=6)> Public Property mnyValue() As _
SqlDecimal
Get
Return m_Value
End Get
Set(ByVal value As SqlDecimal)
m_Value = value
End Set
End Property

--
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest books:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and
Hitchhiker's Guide to SQL Server 2005 Compact Edition

 

[Guest]

Hi;

I may be misunderstanding but it looks to me that this can only be used on
variables returned from a select - not on parameters I am setting to go in to
a select. Is there a way to use it when setting parameters?

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[William \(Bill\) Vaughn]

Ah, it's been 9 months since I worked with this feature, but it seems to me
the you can define a variable that can be used to set a Parameter value that
uses a SqlFacet attribute.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[WenYuan Wang [MSFT]]

HiDave,

I have performed further research and consulted with development team on
it. Actually, the default for decimal values is supposed to be inferring
the scale (SqlParameter.Scale == 0). Decimal instances carry their scale
around with them, but there isn't a nice API to obtain it. SqlClient uses
the following code to extract it. This method will also work on your side
(byte)((Decimal.GetBits((Decimal)value)[3] & 0x00ff0000) >> 0x10)

By the way, depending on the TSQL you run, however, the server may convert
this value to a different precision/scale. For example, passing this value
to a stored proc that looks like CREATE PROC FOO(@p1 DECIMAL) causes it to
be converted to the server's default precision and scale (18 & 0). To get
around that, you have to use non-default precision/scale when creating the
proc: CREATE PROC FOO(@p1 DECIMAL(18, 3) for example. Same holds true for
TSQL table columns, local variables, etc.

Hope this helps,
Sincerely,
Wen Yuan
Microsoft Online Community Support

 

[Guest]

This is fantastic - thank you very much.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm