Lesson Learned #302: System.InvalidCastException: Specified cast is not valid

Today, we have a service request that our customer faced the following error message: System.InvalidCastException: Specified cast is not valid reading a Resultset or doing any operation with the database.

Following, I would like to share my lessons learned with this issue. 

• First, I would like to check if this error is raising by SQL Database. For this reason, I wrote down this small TSQL-code:

create procedure MyCodeTest(@state as int) 
as select @state

• Calling EXEC MyCodeTest 'X' we got the following error: Msg 8114, Level 16, State 1, Procedure MyCodeTest, Line 0 [Batch Start Line 1] Error converting data type varchar to int. 
• Even, running SELECT CAST('X' AS int) or SELECT CONVERT(int,'X'), our customer faced the problem: Msg 245, Level 16, State 1, Line 9 Conversion failed when converting the varchar value 'X' to data type int.
  

So, all points to, depending on, the value that the ResultSet is reading or the code conversion that is using is not the expecting values. In terms of coding, for example, in SQL Server TSQL we have TRY_PARSE (Transact-SQL) - SQL Server | Microsoft Learn to check the value conversion test before executing the conversion. 

Enjoy!