Ошибка при преобразовании типа данных varchar к 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.

asked Aug 4, 2016 at 11:21

saaisaai

1631 gold badge3 silver badges9 bronze badges

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.

Nimantha

6,5166 gold badges27 silver badges68 bronze badges

answered Aug 4, 2016 at 11:33

Gordon LinoffGordon Linoff

1.2m57 gold badges641 silver badges782 bronze badges

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

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

Hope this helps

answered Aug 6, 2016 at 5:07

saaisaai

1631 gold badge3 silver badges9 bronze badges

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

enter image description here

You can start debugging, Debug -> Start Debugging or using the icon shown in the picture or even using a keyboard shortcut 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.

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.

answered Aug 5, 2016 at 4:11

Источник

DenKG

10 / 10 / 7

Регистрация: 23.12.2015

Сообщений: 950

31.12.2017, 14:50. Показов 3145. Ответов 2

Метки нет (Все метки)

Студворк — интернет-сервис помощи студентам

Пытаюсь обновить запись в БД.
Делаю так:

1
2

double stavka = Convert.ToDouble(textBox3.Text);
SqlCommand updateTeacher2 = new SqlCommand("Update [Преподаватели_год] Set Ставка = '" + stavka + "' where ID_преподавателя = '" + teacherID + "'", MyConnection);

И в этом случае вылетает ошибка при преобразовании типа данных varchar к real.
Если же изменяю с 1,25 на 1, то всё работает.

Что тут не так?

В БД поле «Ставка» имеет тип данных Real.

Programming Эксперт 94731 / 64177 / 26122 Регистрация: 12.04.2006 Сообщений: 116,782	31.12.2017, 14:50
2

OwenGlendower

Администратор

Эксперт .NET

15672 / 12631 / 5005

Регистрация: 17.03.2014

Сообщений: 25,715

Записей в блоге: 1

31.12.2017, 14:59

DenKG, зачем преобразовывать строку в double чтобы подставить в запрос в виде строки? Используй параметры:

double stavka = Convert.ToDouble(textBox3.Text);
SqlCommand updateTeacher2 = new SqlCommand("Update [Преподаватели_год] Set Ставка = @Ставка where ID_преподавателя = @ID_преподавателя", MyConnection);
updateTeacher2.Parameters.AddWithValue"(@Ставка", stavka);
updateTeacher2.Parameters.AddWithValue("@ID_преподавателя", teacherID);

10 / 10 / 7

Регистрация: 23.12.2015

Сообщений: 950

31.12.2017, 15:01

[ТС]

OwenGlendower, благодарю

Источник

Добрый день!
Есть строка:

declare @point varchar (100) = '-27,452;26,491;4,299'

Делю ее по символу «;» и хочу записать в переменную типа real:

declare @index int
declare @x real
select @index = CHARINDEX(';', @point)
select @x = cast(substring(@point, 0, @index) as real);

Получаю ошибку:

Сообщение 8114, уровень 16, состояние 5, строка 41
Ошибка при преобразовании типа данных varchar к real.

При использовании convert:

select @x = convert(real, substring(@point, 0, @index));

получаю ту же ошибку

Вопрос снят. Необходимо было заменить символ «,» на символ «.»:

select @x = convert(real, replace(substring(@point, 0, @index), ',', '.'));

Источник

Remove From My Forums

Question
Hi guys.

I have a varchar column in my database, it contains numbers like ‘-0.0001416882,-6.446603E-05,2.401768E-05,1.114732E-05………’

I’m trying to add a WHERE clause, so that it only show the numbers that fall between the range, for example form 0.0001 to 0.0002

Unfortunately I got ‘Error converting data type varchar to real.’ error while running the query.

Please advice.

All replies

This error cause may be two reasons
1. -6.446603E-05 «E» is exists in the number
2. In data there may be non numeric characters exists.

check below select statment

SELECT * FROM table
WHERE ISNUMERIC(col) = 0

update the column to remove characters to number
update table
set [col]=CASE WHEN CHARINDEX(‘E’, col) != 0 THEN CAST(CAST(col AS REAL) AS DECIMAL(15,5)) ELSE CAST(col AS DECIMAL(15,5)) END
where isnumeric([col]) = 1

REPLACE(CHAR(10), col, »)
- Proposed as answer by
  
  Tuesday, June 29, 2010 8:33 AM
Dear sekhara,

Thank you very much for your help.

I tried to write the query the way you did, it works!!!!!

select row
from tbl_Point
WHERE ((CASE WHEN CHARINDEX(‘E’, row) != 0 THEN CAST(CAST(row AS REAL) AS DECIMAL(15,15)) ELSE CAST(rowAS DECIMAL(15,15)) END <=0.00011) AND

(CASE WHEN CHARINDEX(‘E’, row) != 0 THEN CAST(CAST(row AS REAL) AS DECIMAL(15,15)) ELSE CAST(row AS DECIMAL(15,15)) END >=0.000011))

But when I used it in dynamic query with the below, I got the same error — Error converting data type varchar to real. Any idea?

SET @strWhere =’SELECT row FROM tbl_Point WHERE ‘
SET @strWhere = @strWhere +'((CASE WHEN CHARINDEX(»»E»», row ) != 0 THEN CAST(CAST( row AS REAL) AS DECIMAL(15,15)) ELSE CAST(row AS DECIMAL(15,15)) END <=’+@prmUpperLimit+’) AND

(CASE WHEN CHARINDEX(»»E»», + row + ‘) != 0 THEN CAST(CAST(row AS REAL) AS DECIMAL(15,15)) ELSE CAST(row AS DECIMAL(15,15)) END >=’+@prmLowerLimit+’))’
Remove the four (»»E»») keep two (»E»)

SET

@strWhere =‘SELECT
row FROM tbl_Point WHERE ‘

SET

@strWhere = @strWhere
+‘((CASE WHEN CHARINDEX(»E», row ) != 0 THEN CAST(CAST(
row AS REAL) AS DECIMAL(15,15)) ELSE CAST(row AS DECIMAL(15,15)) END <=’+@prmUpperLimit+‘)
AND

(CASE WHEN CHARINDEX(»E», + row + ) != 0 THEN CAST(CAST(row AS REAL) AS DECIMAL(15,15)) ELSE CAST(row AS DECIMAL(15,15)) END >=’

+@prmLowerLimit+‘))’
- Proposed as answer by
  sekhara shiris chinta
  Thursday, July 8, 2010 8:18 AM

Источник

Remove From My Forums

Вопрос
I’m working with a pretty massive data set here. I did a Bulk Insert and got everything loaded into a staging table. Now, everything comes in as VARCHAR. You don’t really have a lot of control over it during the Bulk Insert. I just
created a new production table:

CREATE
TABLE [dbo].[CA_Final]

(

[Date] DATETIME,


[cusip] VARCHAR(100),


[sedol] VARCHAR(100),

etc.

Insert Into [dbo].[CA_Final]

SELECT

[Date],


[cusip],


[sedol],

etc.

From StagingTable

Now I’m getting this error.

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

I’ve done this before, and it worked fine. That was a while back, and I forgot what I did to get it working. Can someone point me in the right direction here?

Thanks!

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

Ответы

Have you inspected those row/column’s?

They must have something in them preventing them from being converted. White space? Tabs?
- Помечено в качестве ответа
  
  12 сентября 2014 г. 23:01
Hummmm, looks like float did the trick.

Thanks Patrick. Thanks everyone.

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.
- Помечено в качестве ответа
  Lydia ZhangMicrosoft contingent staff
  10 октября 2014 г. 9:53

Источник

Question

All replies

Вопрос

Ответы