Conversion failed when converting the varchar value 'null' to data type int

2
votes

I'm getting values from database to a aspx page. while getting values of one column, the column is throwing a value like...

null,402,2912,2909,2910,2913,2911,2914,2915,2388,2389,2390,
2391,2964,2965,2392,2393,2394,2395,2397

Now it throws a exception

Conversion failed when converting the varchar value 'null' to data type int.

But I want all the other values of the column except the null...

Is there any way to convert this 'null' to '0', but still all the other values should exist.

4
c#asp.netsqlsql-serversql-server-2008
You haven't shown any of the code you're using - there are lots of ways you could be approaching this, all with different ways of handling the null value. Please give us more information. - Jon Skeet
Where is the code ? i think you should use, int.TryParse instead of Convert.ToInt32 (i guess you are using it) - V4Vendetta
Please provide the code how to get this value null,402,2912,2909,2910,2913,2911,2914,2915,2388,2389,2390, 2391,2964,2965,2392,2393,2394,2395,2397 - Thit Lwin Oo
In SQL script, you can use.. IsNull(column,0) - Thit Lwin Oo

4 Answers

4
votes

Make your data type Nullable<int> instead of int to allow the converted int nullable values, or on the sql select statement you can do that: ISNULL(yourcolumn, 0) to convert null to 0.

3
votes

I would just declare my type as int? variable; which is a nullable int type. That would avoid you getting the exception, and you could easily convert your value to 0 if needed

if (value == null)
   value = 0;
2
votes

Since you say you want all of the values except the null value in your results, you could filter it in your select statement with a where clause.

SELECT * FROM COLUMN WHERE COLUMN_VALUE IS NOT NULL

Or, if you're using Linq to EF:

from x in yourObjectContext.yourColumns where x != null
0
votes

I think you are trying to change from NULL to 0 in the database. Then you should try this-

SELECT CASE WHEN columnName IS NULL THEN '0' ELSE columnName END FROM tableName