Click here to Skip to main content
15,798,592 members
Please Sign up or sign in to vote.
1.11/5 (2 votes)
See more:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.


What I have tried:

    c.sifra AS 'Šifra RM',
    c.naziv AS 'Naziv RM',
    SUM(c.kolicina) AS 'Kol RM',
    '' AS 'Prodano danas',
    '' AS 'Kol prod danas'
    popis_repromaterijal_roba AS c


    mp_racun_roba AS d
Updated 27-Jul-23 22:31pm
Richard MacCutchan 28-Jul-23 3:28am    
Which item do you need to convert, and why?
Stylus STYLUS 28-Jul-23 3:29am    
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
Richard MacCutchan 28-Jul-23 3:33am    
Which item and what exactly are you trying to do? We have never seen the code you have posted, we have no idea how your database columns are defined, or what you are trying to do with the data. So stop being cryptic, and provide proper detailed explanations if you want us to help.
Dave Kreskowiak 17-Aug-23 9:25am    
Fix the structure of your table to store the numeric value in a more appropriate type. Doing large queries where you have to convert a string to a numeric value will greatly slow down your queries as every record returned needs to have a conversion done on it before being returned.

"It doesn't work" is probably the most useless problem report we get - and we get it a lot. It tells us nothing about what is happening, or when it happens.
So tell us what it is doing that you didn't expect, or not doing that you did.
Tell us what you did to get it to happen.
Tell us any error messages.
Help us to help you!

Since you don't tell us where the error occurs (it's on the error message) or what the error is (it's also on the error message) and we have no access to your DB, we have to guess.

And most likely, it's this:
SUM(c.kolicina) AS 'Kol RM',
And treh problem is that the kolicina column isn't numeric, and non-numeric columns can't be handled by match functions like SUM.

And that leads to a bigger problem: your DB design is wrong and needs to be changed.
Never store nuymbers in NVARCHAR columns - always use INT, FLOAT, or DECIMAL instead.
If you use the wrong column datatype it's easy to set up, and lazy to get the user data into - but it always gives total nightmares after that - because the data in your DB is not valid, or is not in a consistent format, or is in a different format from that which SQL expects.

Think about it: 10/11/12 is a valid date. But is it 10th Nov 2012, 11th Oct 2012, 12th Nov 2010, or some other value entirely? The only time you can tell is when the user inputs the value, and you use his culture to convert it to a DateTime value - as soon as it arrives in the DB it's too late because you no longer have any idea what date format he used: it could be US: MM/DD/YY, European: DD/MM/YY, or ISO / Japanese YY/MM/DD - and you don't even know that the user is using the same calendar as you so the year could be well different (the Hijri date today is Jumada Al-Awwal 3, 1438)! Or even that he didn't enter "hello, my name is Jackie" which isn't even close to a date.

So when you try to convert it to a date at a later time you are almost guaranteed to get errors because the SQL server will try to convert it using it's culture - and generally you don't even know what culture the server is set to!

And you get the same problems when you store numbers as strings as well: they have to be converted every time you want to use them, they can be in the "wrong format" because the user that entered them uses Indian numbering instead of US: "2,31,50,000" instead of "23,150,000", or German: "23 150 000". Or the field may contain "Hello world" through a coding error or a lack of validation.

Always use appropriate data types - it may be easier for your code to slam in NVARCHAR all the time, but it wastes huge amounts of effort later fixing up the holes it leaves.
Share this answer
If c.kolicina are all numbers but stored as strings (field type nvarchar or something), then yes, you should absolutely fix that. If you don't have the level of access to your database to do so, then ask your IT department to fix it.

In the meantime, you could try:
sum(cast(c.kolina as int)) as 'Kol RM'

That assumes the numbers are integers. You may need a different data type.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900