I have the following: Insert Into Treatments (TransactionID, Account, Code) Values (1, 1000, '01120')
where TransactionID is an Int, Account is an Int and Code is a varchar(5).
This query has never failed... until today. I just received an exception report from one of my clients where the query failed with the following explanation: "Error: The conversion of the varchar value '01120' overflowed an INT1 column. Use a larger integer column."
What conversion is the SQL engine talking about? Is the term "Code" a restricted expression which is causing confusion? I've never received any warning to that effect.
This is a Microsoft SQL Server forum and INT1 is not a SQL Server data type. You may be using mysql/mariadb in which case you may be better off posting on a mysql/mariadb forum.
It may be worth checking for triggers on the table.
Thanks for your reply. It was a good presumption on your part given the error message. But the error message was generated by a VS.net application which is connected to its Microsoft SQL Server database. The query has been run millions of times, has always worked and continues to work. It just bugged on this single occasion. Question is: why?
Interesting - you can reproduce this error message using this SELECT CAST('-1' AS tinyint) in SQL Server. But - using this SELECT CAST(-1 AS tinyint) generates an arithmetic overflow error.
Somewhere in your code there is an attempt to either convert the Code to a tinyint - or an attempt to insert the Code into a column defined as tinyint. Since this doesn't normally happen, it would be in a branch that doesn't normally get called.
I suspect this will be a branch of code that is called when the insert fails for some other reason and you are trying to capture the data that was being inserted when that other error occurred. In that part of the code it could be as simple as the Account and Code being swapped in position or it could be an incorrect definition.
This is a straightforward Insert. The complete exception message states "Query failed for Insert Into Treatments (TransactionID, Account, Code) Values (1, 1000, '01120')" so I know that the code didn't swap the order of the data w.r.t. the defined columns or anything like that. If the query had failed for another reason, it would have stated so e.g. no connection to database. This is all just weird. And I still can't explain Ifor's valid point about INT1 which is not a SQL Server column type. Just weird.
As I stated - you can reproduce the error in SQL Server. The error states INT1 exactly the same way - when you try to convert a string to a tinyint.
So it is very clear that your code - somewhere - is attempting to convert that string to a tinyint. So either the variables are swapped or column order is swapped or some other code is being called that isn't normally called.
Ha. I see what you mean w.r.t the INT1 mention which clears that up (thanks for that!). But as indicated, the query is Insert Into Treatments (TransactionID, Account, Code) Values (1, 1000, '01120') where there is no swap in columns or values and no other code is being run. In principle, there was no possible misinterpretation. At this point, I'm leaning towards tagging this as an aberration due to hardware e.g. an old catalytic capacitor can alter the square wave signal to such an extent that a 1 is read as 0 and vice versa.
