“将数据类型varchar转换为真实的错误”

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

我被指派了一个任务来调试现有的ASP。c#网络系统。我发现这是由存储过程引起的，错误消息是。

Msg 8114, Level 16, State 5
Error converting data type varchar to real.

msg8114，级别16，状态5将数据类型varchar转换为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.

没有行号或任何错误描述。任何一种抬头都是值得赞赏的。我应该在哪里查找，如何调试以便找到准确的错误。请帮助。

3 个解决方案

#1

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+.

第一个是显式转换，其中字符串被转换为数字。这应该很容易找到。您可以使用SQL Server 2012+中的try_convert()替换显式转换。

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.

第二个并不难。+运算符用于添加和字符串连接。它经常引起这类问题。所有数值参数都需要转换为字符串。或者，更改所有进行连接的代码以使用CONCAT()函数——参数将自动转换为字符串。

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.

第三是数值操作的隐式转换。如果您有任何其他需要数值的操作(或函数调用)，那么SQL Server将隐式地将值转换为数字。这很难找到。尝试编写不依赖隐式转换的代码。

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

注意:试图使用WHERE子句(甚至在子查询或CTE中)过滤坏值是行不通的。如果你有

#2

Found the error, sharing my answer hoping someone is facing the same problem. This is where the error was

发现错误，分享我的答案希望有人面对同样的问题。这就是错误所在

CASE [Category].[ID]
    WHEN '1' THEN [Category].[Name]
    WHEN '2' THEN [Category].[Weight]
END

Here [Category].[Name] is string and [Category].[Weight] is real, trying different data types inside the same CASE clause, so to solve this all what I had to do is split this condition, like this

(类别)。[Name]是字符串[Category]。[权重]是真实的，在同一个CASE子句中尝试不同的数据类型，所以为了解决这个问题，我要做的就是将这个条件分割成这样。

CASE [Category].[ID]
    WHEN '1' THEN [Category].[Name]
    END,
CASE [Category].[ID]
    WHEN '2' THEN [Category].[Weight]
END

Hope this helps

希望这有助于

#3

You can simply debug your sql code as you do in Visual Studio if you have Microsoft SQL Management Studio.

如果您有Microsoft sql Management Studio，您可以像在Visual Studio中那样调试sql代码。

You can start debugging, Debug -> Start Debugging or using the icon shown in the picture or even using a keyboard shortcut Alt + F5.

您可以开始调试、调试——>开始调试或使用图片中显示的图标，甚至使用键盘快捷方式Alt + F5。

I would recommend to write the execute statement in a different query window, with parameters if necessary, don't forget to add your db call and debug this window.

我建议在另一个查询窗口中编写execute语句，如果需要，使用参数，不要忘记添加db调用并调试这个窗口。

use yourdb
exec yoursp para1 para2

You can Step Into (F11) and Step Over (F10). The place where the debugging stops or crash is the place you have to look into. You can even put brake points when once you start debugging using keyboard shortcut F9. For more look at this video.

您可以进入(F11)并跨过(F10)。调试停止或崩溃的地方是您必须查看的地方。当您开始使用键盘快捷键F9进行调试时，您甚至可以放置刹车点。更多信息请参见本视频。

#1