saai saai - 2 months ago 12x
ASP.NET (C#) Question

Error converting data type varchar to real

I am assigned with a task to debug an existing ASP.NET C# system. I found out that this is caused by a stored procedure, and the error message is

Msg 8114, Level 16, State 5

Error converting data type varchar to real.

No line numbers or any description of errors. Any kind of heads up is appreciated. Where should I look into, How to debug so that to find exact error. Please help.


This is too long for a comment.

The problem is obvious -- you have a string and you are treating it as a number. There are three possible reasons.

The first is an explicit conversion where the string is converted to a number. This should be easy to find. You can replace the explicit conversion with try_convert() in SQL Server 2012+.

The second is not much harder. The + operator is used both for addition and string concatenation. It often causes this type of problem. All numeric arguments need to be converted to strings. Or, change all code that does concatenation to use the CONCAT() function -- the arguments are automatically converted to strings.

The third is implicit conversion for numeric operations. If you have any other operations (or function calls) that require numeric values, then SQL Server will implicitly convert the values to numbers. This can be very hard to find. Try to write code that does not rely on implicit conversions.

Note: Trying to filter out bad values using a WHERE clause (even in a subquery or CTE) does not work. If you hae