15

Just stumbled onto a simple error, and it prompts an interesting question. Environment: VS 2010, NET.4, C#. Getting a return value from a SQL sproc produced the "Specified cast is not valid" exception:

cm.Parameters.Add( "@si", SqlDbType.SmallInt ).Direction=   ParameterDirection.ReturnValue;
..
cm.ExecuteNonQuery( );
short   siRetVal=   (short) cm.Parameters[0].Value;     // exception here

Since the code was running in Debug mode and got interrupted at that line, I typed in the Immediate Window:

?(short) cm.Parameters[0].Value

and the result was:

Cannot unbox 'cm.Parameters[0].Value' as a 'short'

Ok, SQL sproc returns a 32-bit int (not a 16-bit short as i initially thought), that explains the exception. Should use proper width for this parameter - no questions about that.

But can anyone explain why the root cause of the error is only reported in Immediate Window? No details were exposed in the Exception Helper, Internal Exception was empty. Wouldn't it be beneficial to know the exact reason? I think it would make error analysis much simpler, no?

Edit: Added the screenshot; stack trace does not seem to hint at any ADO.NET code (which i did not expect). Even more, if i leave the ret-value parameter "declaration" as SmallInt, but provide a proper-width variable (or cast as shown), accommodating an int, everything works! I'm quite certain there's no relation to ADO/SQL.

I believe @HansPassant is revealing the true nature of what's happening here, and am inclined to accept that as an answer. Albeit it's kinda disappointing to discover that managed code cannot even provide me accurate details about the state of execution (e.g. which reference is actually null - as mentioned, or in this case of invalid cast).

Astrogator
  • 1,041
  • 11
  • 27
  • 1
    No kidding! How much logging code could be eliminated if the error message included '"somevalue" was not recognized as a valid System.Int32' (or other DataType)???? – David Dec 13 '11 at 16:13
  • 1
    Who knows who to mention this to to get it included in an upcoming update to .NET? – David Dec 13 '11 at 16:14
  • 1
    This is one of the Great Mysteries that one should not try to understand. Indulge in serenity and accept what you cannot change. – Roy Dictus Dec 13 '11 at 16:14
  • 2
    I feel your pain. After a particularly nasty experience, I posted this: http://programmers.stackexchange.com/questions/37475/what-are-developers-problems-with-helpful-error-messages – Moo-Juice Dec 13 '11 at 16:15
  • have you looked at the database to see what the actual datatype of the field is that you are trying to convert to short..? just curious – MethodMan Dec 13 '11 at 16:16
  • 5
    Also, Null Reference Exception, Which Reference!? I know you know !! – Erix Dec 13 '11 at 16:30
  • @DJKRAZE, of course (really didn't have to, since this is a return-value, so it's not declared by the sproc itself, otherwise i'd be the only one to blame :) - dragged `cm.Parameters[0].Value` into the Watch Window and saw that it's an `object{int}`. But the problem actually has nothing to do with SQL or DB. – Astrogator Dec 13 '11 at 16:31
  • @Erix: No; on an IL level, there is no simple way to get any meaningful additional details about a NullReferenceException. – SLaks Dec 13 '11 at 16:53
  • 1
    The difference exists because the debugger is not a compiler. The reason the exception is so uninformative is because the jitter needs to generate very efficient and compact code. See http://stackoverflow.com/questions/1583050/performance-surprise-with-as-and-nullable-types/3076525#3076525 – Hans Passant Dec 13 '11 at 17:14
  • @SLaks, indeed there is no easy way to tell what variable was null, but it could tell which member was being accessed – Thomas Levesque Dec 13 '11 at 19:21
  • Did you "copy the exception detail to clipboard"? I usually find the source of the error in the details. Also, try turning off the "exception assistants". They get in the way sometimes. – AMissico Dec 14 '11 at 15:41
  • While I can feel your pain, this question really isn't answerable by anyone outside of the C# team at Microsoft. I've dealt with the issue myself (interop-ing with a crappy old database with an unreliable schema). Only solution was to watch for an exception, and log all expected types/actual types ("expected: int; actual: " + cm.Parameters[0].Value.GetType()"). –  Dec 14 '11 at 16:59
  • Also, please don't ask this question again when you're trying to debug FileNotFoundExceptions and you wonder why the hell they didn't include the friggen full path of the file that wasn't friggen found. THAT is the WORST failing of the entire framework. –  Dec 14 '11 at 17:01
  • No problem, @Will. I already said i accept Hans Passant's comment as answer, because after consideration it does make sense. No debate needed :). On 2nd note i'll only say that the code _should be aware_ of which _frigging_ file it's trying to open, so if/when written properly that case should not cause as much wondering. Thanks to all! – Astrogator Dec 14 '11 at 20:22
  • @HansPassant: Most FileNotFounds do include paths. I believe that the path-less FileNotFounds are from the assembly loader, which is more complicated. – SLaks Dec 15 '11 at 18:56

1 Answers1

0

If you profile the SQL that is generated by ADO.NET you might find that ADO.NET had tried to explicitly type your output parameter as smallint. So when SQL Server executed your request it tried to assign the value returned by the stored procedure (an int) into a variable that you told it to create as a tinyint. I believe the cast exception you see is from SQL Server, not .NET.

In the command window you are using the CLR, not the TSQL query engine. Your gripe might need to be with the query engine, not .NET.

syneptody
  • 1,250
  • 1
  • 10
  • 27
  • 2
    **The gripe is** not about SQL-vs-ADO.NET or wrong 'width' of a value vs variable to hold it, but **about the lack of precise information available to the code**. Since Immediate Window does expose the root cause, _it can be included in the exception in the first place_. On many occasions that would tremendously help figuring out what went wrong, and most importantly why. Of course we learn techniques like not using multiple complex expressions on the same line - thus helping to localize the exception by line # - to pinpoint the weak spot. That's a lame excuse for .NET though, IMHO. – Astrogator Dec 13 '11 at 17:31
  • A good suggestion though, I'll redo the scenario and add the stack trace to see where does it bubble up from (didn't save it last time). – Astrogator Dec 13 '11 at 17:34
  • So I think what I am suggesting is that SQL Server is throwing a 'Specified cast is not valid error' and ADO.NET is simply picking that error up. SQL Server may not be providing the depth of detail that would be necessary for your application to throw a more meaningful exception. The operation that is performed exclusively in the CLR is in fact type-aware. As a side note you might consider using implicitly typed variables and wrapping your assignments in .TryParse() calls. – syneptody Dec 13 '11 at 19:20
  • No, this case has nothing to do with SQL/ADO, try in ImmediateWindow: `?(short) ((object)77777)` - it results in `Cannot unbox '((object)(77777))' as a 'short'`! For that matter, it can't even do `?(short) ((object)0)`! **It's specifically about the value being wider than a given placeholder**. _Besides, SQL client libraries are in native code, not .NET_ (otherwise i'd be extremely disappointed :). – Astrogator Dec 13 '11 at 22:31