error when inserting C#'s decimal into SQL numeric

Question

i'm using SQL server 2008 and have a table with a field type of numeric(7,3), into which i want to insert values using a C# web-service (i know that values won't be more than 4 digits before the decimal and 3 digits afterwards). therefore my C# variable is of type decimal (as i read here). it basically looks like this -

SqlParameter vsp = new SqlParameter("@VideoStartPoint", SqlDbType.Decimal);
                    vsp.Scale = 3;
                    vsp.Precision = 7;
                    vsp.Value = VideoStartPoint;

however, i found out that it works only if the value is less than 10. for any value from 10 and above i get an error - "Error converting data type numeric to numeric". i know there's no problem in my table, for when i insert it using SQL i have no problem with >10 values.

any ideas? cheers, eRez

Can you show us your stored procedure/sql that you employed here? — Oybek, Feb 09 '12 at 09:44
Did you by chance swallow *Arithmetic overflow error .....*, Is the `VideoStartPoint` pointing to the column in question (7,3) or if a stored proc then whats the corresponding datatype — V4Vendetta, Feb 09 '12 at 09:48
no - i doesn't work if a don't specify the scale and precision (i added them after reading some advice here, but it made no change); that's my testing SQL (which works): INSERT INTO [Statistics].[dbo].[VideoStatsAuditEvent] ([EntryDateTime] ,[VideoSessionID] ,[VideoID] ,[VideoActionID] ,[videoStartPoint]) VALUES (GETDATE() ,'F02689A1-4A0A-4437-BEBD-DE9E94F68656' ,96238 ,54 ,11.1234) GO; @V4Vendetta - not sure if i understand what you meant — eRez, Feb 09 '12 at 09:54
11.1234 seems to have more than 3 digits after the decimal point. A better piece of test SQL would be to create a variable of type `decimal(7,3)`, assign the test value to that variable, and then use it in the insert statement — Damien_The_Unbeliever, Feb 09 '12 at 10:13
@Damien_The_Unbeliever - you're correct, but that was just some testing code i used, and as i mentioned - it had no problem (only cut that last '4' digit). this piece of code also have no problem - DECLARE @t numeric(7,3) SET @t = 78.123;SELECT @t — eRez, Feb 09 '12 at 10:46
@eRez - what is the datatype of the VideoStartPoint field in your VideoStatsAuditEvent table? Can you also post the full stack trace and any inner exceptions from your error handler? — Neil Moss, Feb 09 '12 at 12:46

Panagiotis Kanavos · Answer 1 · 2012-02-09T13:53:53.837

1

If the VideoStartPoint is a string and your locale uses '.' instead of ',' as a thousand separator , the value '11.1234' will be translated as 11123.000 as .NET will use your thread's locale value to convert the string to a decimal. This can happen in French, Italian, Greek locales, I think Spanish as well.

You should place a breakpoint to check the value of VideoStartPoint even if it is a decimal. There may be a problem at the client's side that is improperly converting a numeric string to decimal.

EDIT: To illustrate, the following code parses the same string "11.450" with two different locales, Greek and US, resulting in two different numbers, 11450 and 11.450

        var grNumber = decimal.Parse("123.450", CultureInfo.GetCultureInfo("el-GR"));
        var usNumber = decimal.Parse("123.450", CultureInfo.GetCultureInfo("en-US"));
        Debug.Assert(grNumber == 123450);
        Debug.Assert(usNumber == (decimal) 123.45);

The following LINQ statement returns all cultures that use '.' as their thousand separator:

        var cultures = (from culture in CultureInfo.GetCultures(CultureTypes.AllCultures)
                       where culture.NumberFormat.NumberGroupSeparator == "."
                       select culture.Name);

There are 92 different cultures corresponding to 33 ISO languages (CultureInfo.TwoLetterISOLanguageName), including Spain, Netherlands, Belgium, Germany etc.

edited Feb 09 '12 at 13:53

answered Feb 09 '12 at 11:16

Panagiotis Kanavos

120,703
13
188
236

thanks, but i don't think this is the issue. VideoStartPoint is decimal, and i read it like this - decimal VideoStartPoint = 0; if (!decimal.TryParse(requestCollection["VideoStartPoint"], out VideoStartPoint)) { VideoStartPoint = 0; } besides - had that been the reason, correct me if i wrong but it should have also happened on decimals less than 10 as well – eRez Feb 09 '12 at 11:46
No, .NET (and VBA before this) simply ignore a misplaced thousands separator. They will only fail when they encounter a misplaced decimal separator. 11.000 in one of those locales will be translated to 11000 while 11,000,000 will raise an exception. Decimal.TryParse would still succeed if requestCollection["VideoStartPoint"] contains what (to your locale) is a thousand separator. What is the value contained in requestCollection["VideoStartPoint"]? – Panagiotis Kanavos Feb 09 '12 at 11:58
for example "11.123" (that will fail, while "1.123" won't). in any case, i don't send any thousands separator what so eve – eRez Feb 09 '12 at 12:42
1

I didn't say that you are using thousand separators, just that your locale may be translating '.' as a thousand separator. Check the edit for code that illustrates this and a query to return all locales that use '.' as a thousand separator – Panagiotis Kanavos Feb 09 '12 at 13:54

error when inserting C#'s decimal into SQL numeric

1 Answers1