InvalidCastException Hangs W/ Stored Procedure Output Parameter

  • Thread starter Thread starter Phill
  • Start date Start date
P

Phill

I have a SQL Server stored procedure that returns an int. Sometimes a
null value is returned which causes the problem.

//This is what I did:
return (int)cmd.Parameters["nextCount"].Value;


//What I should do is this:
if( cmd.Parameters["nextCount"].Value != DBNull.Value )
{
return (int)cmd.Parameters["nextCount"].Value;
}

The only way I see the exception is when I run the program w/
debugging. Because the program seems to hang on that line and keeps
sucking up memory unless I kill it. I thought (even if running w/o
debugging) an exception would halt the program and since I have no
catch blocks should stop the program and display an exception. But the
program keeps running in task manager and keeps sucking up a little
more memory all the time?

Why is this?
Thank you for any help.
 
Phill said:
I have a SQL Server stored procedure that returns an int. Sometimes a
null value is returned which causes the problem.

//This is what I did:
return (int)cmd.Parameters["nextCount"].Value;


//What I should do is this:
if( cmd.Parameters["nextCount"].Value != DBNull.Value )
{
return (int)cmd.Parameters["nextCount"].Value;
}

The only way I see the exception is when I run the program w/
debugging. Because the program seems to hang on that line and keeps
sucking up memory unless I kill it. I thought (even if running w/o
debugging) an exception would halt the program and since I have no
catch blocks should stop the program and display an exception. But the
program keeps running in task manager and keeps sucking up a little
more memory all the time?

Why is this?
Click to expand...

Could you post a short but complete program which demonstrates the
problem?

See http://www.pobox.com/~skeet/csharp/complete.html for details of
what I mean by that.
 
Sure.

static void Main(string[] args)
{
SqlConnection conn = new SqlConnection( "MyConnString" );
conn.Open();
Console.WriteLine( GetMenuName(conn, 1) );
Console.ReadLine();
conn.Close();
}

static public string GetMenuName(SqlConnection conn, int menuID)
{
SqlCommand cmd = new SqlCommand("proc_menu_GetName", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add( new SqlParameter("menuID", menuID ) );
cmd.Parameters.Add( new SqlParameter("name", SqlDbType.VarChar, 30,
ParameterDirection.Output, false, 0, 0, null, DataRowVersion.Current,
null ) );

cmd.ExecuteNonQuery();
return (string)cmd.Parameters["name"].Value;
}

This is an exmple of a function returning a string but the same thing
occurs. If for example there were no records matching that parameter
will be DBNull. Which is fine, but when the exception is thrown the
program just hangs.
 
Phill said:
Sure.
Click to expand...

(Tiny nitpick, but in future it would help if you could provide it in a
"cut-paste-compile" friendly format, rather than just the methods. What
you've provided is admittedly better than some others do, but the
easier you can make it for others to reproduce your problem, the more
people are likely to look at it.)
static void Main(string[] args)
{
SqlConnection conn = new SqlConnection( "MyConnString" );
conn.Open();
Console.WriteLine( GetMenuName(conn, 1) );
Console.ReadLine();
conn.Close();
}

static public string GetMenuName(SqlConnection conn, int menuID)
{
SqlCommand cmd = new SqlCommand("proc_menu_GetName", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add( new SqlParameter("menuID", menuID ) );
cmd.Parameters.Add( new SqlParameter("name", SqlDbType.VarChar, 30,
ParameterDirection.Output, false, 0, 0, null, DataRowVersion.Current,
null ) );

cmd.ExecuteNonQuery();
return (string)cmd.Parameters["name"].Value;
}

This is an exmple of a function returning a string but the same thing
occurs. If for example there were no records matching that parameter
will be DBNull. Which is fine, but when the exception is thrown the
program just hangs.
Click to expand...

So could you give a suitable database definition and stored procedure
to test this?
 
Sure, I'll try:


Code To Generate trimmed down DB:

--Create Database
CREATE DATABASE webSystem
USE webSystem
GO

--Grant Access To The Server & DB (Change To Your Server\Account)
EXEC sp_grantlogin 'MIS118\ASPNET'
EXEC sp_grantdbaccess 'MIS118\ASPNET'
EXEC sp_defaultlanguage 'MIS118\ASPNET', 'us_english'
EXEC sp_defaultdb 'MIS118\ASPNET', 'webSystem'
GO

--Create Table
CREATE TABLE menu
(
id INT NOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
parentID INT NOT NULL
)
GO

--Create Procedure
CREATE PROCEDURE proc_menu_GetName( @menuID INT, @name VARCHAR(30) OUTPUT )
AS
SELECT @name = name FROM menu WHERE id = @menuID
GO
GRANT EXECUTE ON proc_menu_GetName TO PUBLIC
GO


BTW My Connection string for this would be:
@"Data Source=MIS118\web;Initial Catalog=webSystem;Integrated Security=SSPI"

(Server Instance is web)
 
Phill said:
Sure, I'll try:
Click to expand...

<snip>

Thanks very much. I had to change the parameter names in the code to
@name and @menuID, but after that it worked.

However, I don't see the problem you were describing - if I just try to
cast the null value to string, I get an exception as expected. If I use
the code which tests the value, it all works fine.

What happens when you run your console app?
 
When running w/o debugging the console window pops up and remains
blank. Watching the process in task manager I see the amount of memory
it uses keeps increasing. It sits like this until I kill the process.

When running w/ Debugging in Visual C# 2005 Beta it stops at the last
line of GetMenuName() where it tries to cast the value to a string
and return it. It pops up a window in the code saying an invalid cast
exception was unhandled.
This is ok because I see the problem, but what concerns me is when
I'm running the program for real, this exception is not being written
out to the console window and is not stopping the program. It just
keeps running w/ no output and does not halt.
 
Phill said:
When running w/o debugging the console window pops up and remains
blank. Watching the process in task manager I see the amount of memory
it uses keeps increasing. It sits like this until I kill the process.
Click to expand...

So what's your *complete* console program for that? Here's what I've
got:

using System;
using System.Data;
using System.Data.SqlClient;

public class Test
{
static void Main(string[] args)
{
SqlConnection conn = new SqlConnection
("Data Source=localhost; "+
"Initial Catalog = webSystem; "+
"Integrated Security=SSPI");
conn.Open();
Console.WriteLine( GetMenuName(conn, 2) );
Console.WriteLine ("Done");
Console.ReadLine();
conn.Close();
}

static public string GetMenuName(SqlConnection conn, int menuID)
{
SqlCommand cmd = new SqlCommand("proc_menu_GetName", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add( new SqlParameter("@menuID", menuID ) );
cmd.Parameters.Add( new SqlParameter("@name",
SqlDbType.VarChar, 30,
ParameterDirection.Output,
false, 0, 0, null,
DataRowVersion.Current,
null ) );

cmd.ExecuteNonQuery();
return (string)cmd.Parameters["@name"].Value;
}
}

When I run that, a box pops up and asks me if I want to debug...
 
1st I'd like to thank you for taking the time to look at this Jon.

Yes, you have the program exactly as it should be to produce the
results.

This makes me think it may have something to do w/ version 2.0b of the
framework. Perhaps it is a problem specific to this beta version.
Obviously its not something in the code.

Are you running 2.0b or do you have 1.0/1.1? If you're running 2.0b
also then I'm really confused why I'm not getting the expected
exception when running w/o debugging and you are.

The reason I'm running the beta is because I'm really a C++ programmer
who was not interested in .NET until fairly recently. When I started
to check it out, the combination of C# and the class library available
to the .NET languages was too much for me to resist. (Although I still
think .NET programs take a little too long to startup) I'm trying to
get a handle on the beta for VS 2005 so I'll be ready to use it when
it is released.

I'm going to try and run this program on a machine w/ an earlier
non-beta version of the framework and see if I still get the same
results.

Thanks again Jon.
 
Phill said:
1st I'd like to thank you for taking the time to look at this Jon.

Yes, you have the program exactly as it should be to produce the
results.

This makes me think it may have something to do w/ version 2.0b of the
framework. Perhaps it is a problem specific to this beta version.
Obviously its not something in the code.
Click to expand...

Ah - I didn't realise you were running 2.0b.
Are you running 2.0b or do you have 1.0/1.1? If you're running 2.0b
also then I'm really confused why I'm not getting the expected
exception when running w/o debugging and you are.
Click to expand...

I'm running 1.1.
The reason I'm running the beta is because I'm really a C++ programmer
who was not interested in .NET until fairly recently. When I started
to check it out, the combination of C# and the class library available
to the .NET languages was too much for me to resist. (Although I still
think .NET programs take a little too long to startup) I'm trying to
get a handle on the beta for VS 2005 so I'll be ready to use it when
it is released.

I'm going to try and run this program on a machine w/ an earlier
non-beta version of the framework and see if I still get the same
results.
Click to expand...

That would be interesting to know. If this *does* seem to be a bug with
2.0b, please report it so we don't end up with it in the final kit :)
 
Well, I've had the chance to build and run that sample program on VS
2002 (1.0 of the framework) and there the exception is thrown as it is
supposed to be.

So, it does seem the problem is w/ VS 2005b and or the 2.0b Framework.
 
Back
Top