Error message: http://System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting the varchar value ‘NULL’ to data type int.
7December 5, 2019 by Kenneth Fisher
I love this error. Primarily because it demonstrates two very important things.
- Errors matter. Make sure when you ask someone for help you give them the exact error and circumstances causing the error.
- Experience matters. If you’ve been working with database development for a while you can probably pinpoint exactly what’s causing this error just from the error.
When I was handed this error from one of my co-workers I started by telling them exactly what was wrong, and then out of curiosity started a quick poll.
I’m honestly a little mad at myself because of the three possible answers none of them were actually the correct one. I mean they are all true to a certain extent, but not the real problem.
What’s wrong?
If you look at the error it’s pretty clear what’s happened.
Conversion failed when converting the varchar value ‘NULL’ to data type int.
First of all you have to realize that the varchar value ‘NULL’ does not mean a NULL value. I’ve seen this throw people on more than one occasion. A string containing the word ‘NULL’ is not in any way the same as the value NULL. It’s a string. Someone messed up and put the word NULL in there. Next there is an implicit conversion going on of a varchar to an int.
Basically what happened is that someone stored integers into a varchar column. How do I know that? Well, it works most of the time, which means that there is an implicit conversion of a varchar to an int that’s working. I.E. integers stored in a varchar column.
So, you could say this is a data problem because someone stored the word ‘NULL’ in a column meant for an INT. You could call it a coding problem because the code allowed that to happen.
Both are true, although most likely neither one is really the point. It’s almost certainly a design problem. You should NEVER store numbers in a varchar column, dates in a varchar column etc. Store your data in the appropriate data type and you avoid problems like this. Now that said, it is possible, and just possible, that this column has to be a varchar because sometimes the values are just numbers, sometimes they have letters in them. In that case the problem is that someone compared this column incorrectly to a column that’s an integer. In that case it’s back to being a code problem.
Oh, and I should point out, that in 30 years I can count the number of times I’ve seen the word ‘NULL’ belong in a varchar column on one hand. So yea, regardless there is a data problem.
But primarily, this is a design problem. Put your data into the correct data types people.
SQL Studies 
KF: You should NEVER store numbers in a varchar column
How do feel about fixed length collections of numbers to whom no mathematical operation is likely to be carried out upon them, e.g. telephone numbers, postcodes, certain IDs and so on?
I regard these as strings (although I don’t always define them as such). To be sure, one of the INT family of datatypes would take up less space but will then require an implicit conversion when used in text, as it often will be.
I would tend to agree those are strings. Particularly since things like postcodes can have letters in them depending on the country. And yea, you are right. I should have been more specific. Numbers that need to be treated as numbers.
I hate that you got me on the twitter poll with this question. I really thought I was better than that. Just goes to show you how important it is to pay close attention to the error! And yes, please please use the correct data types!
It’s ok. Like I said, I messed up on the poll in the first place. I can’t believe I didn’t put design on there.
In some cases, the value in a VARCHAR field is always an integer “by accident”. The classic examples are when the field is storing an account number or when it is storing a checkbox/radio button reference.
I have seen plenty of (usually former c) programmers who will happily check whether txt_CheckBox = 0 rather than including the quotes. It works fine until it doesn’t.
Which reminds me of one of my (many) other pet peeves… Why do so many Database Developers use VARCHAR(1) instead of CHAR(1) ? I know that VARCHAR(1) behaves slightly differently with LIKE comparisons (VARCHAR(1) = ” is NOT LIKE ‘ ‘ while CHAR(1) = ” IS LIKE ‘ ‘) but this is such a specific difference that I cannot believe anyone in their right mind is relying on it.
Agree completely.
[…] Kenneth Fisher has a fun error for us: […]