Geek Answers Handbook

The decimal value of 1.5 ends as 15 in the database - how, why?

Greetings to the good people on the Internet :)

I have a string value of "1.5", which after Convert.ToDecimal () ends as 1.5M when viewing it in the debugger. So far so good, I think. The decimal value is then passed to the stored procedure call inside the data set. The type of parameter that interests me is defined as NUMBER (7.2) in the database, so it should contain numbers that have numbers after the decimal separator.

The problem is that somewhere along the line, the decimal value loses its separator and joins the precision with a scale making up a completely new number, as indicated in the header of this message. I even tried to set the assembly of Precision and Scale parameters inside the parameters in that the call to the stored procedure corresponded to DB (7.2), but that did not help either.

Do you have any ideas what might happen here?

EDIT:

Here is the code that calls the stored proc:

CaseFactory.UtilsAdapter.SetCaseAction(DefId, action, doneBy, assignedTo, comment, status, searchStatus, priority, access, relStatus, relStatusFixKit, totalhrs, out Common.RETURN_CODE, out Common.RETURN_TEXT);

=> SetCaseAction simply calls the stored procedure in the database using the DataSet mechanics. 'totalhrs' is the pair that interests me, it is the decimal 1.5M at this point. I also checked NLS_NUMERIC_CHARACTERS in Oracle and they are set to ','

Therefore, it uses a comma for decimal places and spaces for thousands. I don’t see anything unusual. It can only be strange that "1.5" ends as "1.5M" with a dot inside after ToDecimal (), which may be interpreted incorrectly. However, it seems unlikely because the regional settings on my server use "," as a decimal separator if .NET does not use some other settings? Really confused.

+4
source share
5 answers

I suspect that you are converting it at some point without the correct culture, for example, concatenating it (instead of using the parameter) into a TSQL query via ToString() or just using the wrong culture when calling ToDecimal . "1,5" is ambiguous between "1 decimal point 5" and "1 separator of groups 5"; the latter case is analyzed as 15.

+4
source

I would suggest that this is a localization problem - commas are used to separate thousands, not decimal places in many systems. Check the language settings in your database.

+2
source

The decimal separator in SQL is equal . but not,. Therefore, 1.5 is interpreted as 15 , not 1.5 . To avoid this problem, always use parameterized queries, rather than encode the values ​​directly into the query text.

+1
source

Some code on how you pass the value to the stored procedure will be useful (if you do it yourself).

Some people still use String.Format to generate query strings. In addition to errors like the description, it is open to SQL injection.

You must use parameterized queries. Typically, doing this will work:

 SqlCommand cmd = new SqlCommand("...", connection); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@myDecimal", decimalVariable);

EDIT
Of course, this should not be done only for some parameters, but for all parameters of the stored procedure! The above is just an example of how to pass parameters to a stored procedure in the first place.

+1
source

As Thomas said, in SQL, the decimal separator in SQL is "."

In this case, you can simply use:

yourVariable.Replace (',', '.')

And then pass the parameter to the function performing the transaction.

-1
source

More articles:


All Articles