newbie: my code crashe, NULL value

Hey
asp.net 2.0
My code below crashes at the "return (int)cmd.Parameters["@dotnet.itags.org.return"].Value;"
line. In the debugging window I see that when this exception occur, the
"return (int)cmd.Parameters["@dotnet.itags.org.return"].Value;" has a NULL value...
public override int SendMessage(MessageDetails message)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("AH_network_SendMessages", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@dotnet.itags.org.receiver", SqlDbType.NVarChar).Value =
message.Sender;
cmd.Parameters.Add("@dotnet.itags.org.sender", SqlDbType.NVarChar).Value =
message.Sender;
cmd.Parameters.Add("@dotnet.itags.org.title", SqlDbType.NVarChar).Value =
message.Title;
cmd.Parameters.Add("@dotnet.itags.org.body", SqlDbType.NVarChar).Value =
message.Body;
cmd.Parameters.Add("@dotnet.itags.org.return", SqlDbType.Int).Direction =
ParameterDirection.Output;
cn.Open();
int ret = ExecuteNonQuery(cmd);
return (int)cmd.Parameters["@dotnet.itags.org.return"].Value;
}
}
This is the stored procdure called in the method:
ALTER PROCEDURE dbo.AH_network_SendMessages
@dotnet.itags.org.sender nvarchar(256),
@dotnet.itags.org.receiver nvarchar(256),
@dotnet.itags.org.title nvarchar(100),
@dotnet.itags.org.body nvarchar(2000),
@dotnet.itags.org.return int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO AH_Messages (sender, receiver, title, body)
VALUES (@dotnet.itags.org.sender, @dotnet.itags.org.receiver, @dotnet.itags.org.title, @dotnet.itags.org.body);
set @dotnet.itags.org.return = 1;
select 1;
END
Please, what am I doing wrong here?
JeffIf altering the signature of your SP, you may consider the following option
that should work.
SqlParameter returnValue = sqlCommand.Parameters.Add("@.YourSPReturnValue",
SqlDbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;
return (Int32)returnValue.Value;
Declaring an explicit return value is in my opinion preferrable, as it is
more clear about your intent and somewhat cleans up the SP declaration.
Tor Bdshaug
tor.badshaug(AT)bekk.no
"Jeff" <it_consultant1@.hotmail.com.NOSPAM> wrote in message
news:unl2osv6GHA.4580@.TK2MSFTNGP03.phx.gbl...
> Hey
> asp.net 2.0
> My code below crashes at the "return
> (int)cmd.Parameters["@.return"].Value;" line. In the debugging window I see
> that when this exception occur, the "return
> (int)cmd.Parameters["@.return"].Value;" has a NULL value...
> public override int SendMessage(MessageDetails message)
> {
> using (SqlConnection cn = new SqlConnection(this.ConnectionString))
> {
> SqlCommand cmd = new SqlCommand("AH_network_SendMessages", cn);
> cmd.CommandType = CommandType.StoredProcedure;
> cmd.Parameters.Add("@.receiver", SqlDbType.NVarChar).Value =
> message.Sender;
> cmd.Parameters.Add("@.sender", SqlDbType.NVarChar).Value =
> message.Sender;
> cmd.Parameters.Add("@.title", SqlDbType.NVarChar).Value =
> message.Title;
> cmd.Parameters.Add("@.body", SqlDbType.NVarChar).Value =
> message.Body;
> cmd.Parameters.Add("@.return", SqlDbType.Int).Direction =
> ParameterDirection.Output;
> cn.Open();
> int ret = ExecuteNonQuery(cmd);
> return (int)cmd.Parameters["@.return"].Value;
> }
> }
> This is the stored procdure called in the method:
> ALTER PROCEDURE dbo.AH_network_SendMessages
> @.sender nvarchar(256),
> @.receiver nvarchar(256),
> @.title nvarchar(100),
> @.body nvarchar(2000),
> @.return int OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT INTO AH_Messages (sender, receiver, title, body)
> VALUES (@.sender, @.receiver, @.title, @.body);
> set @.return = 1;
> select 1;
> END
> Please, what am I doing wrong here?
> Jeff
>
What is the ExecuteNonQuery method you try to call after all?
Normally, you will do a cmd.ExecuteNonQuery, rather than
ExecuteNonQuery(cmd).
Could it be that your ExecuteNonQuery(SqlCommand cmd) method in somewhat
fail to do a cmd.ExecuteNonQuery.
The fact that the output parameter does not have a value may suggest that
may be the case.
Tor Bdshaug
tor.badshaug(AT)bekk.no
"Jeff" <it_consultant1@.hotmail.com.NOSPAM> wrote in message
news:unl2osv6GHA.4580@.TK2MSFTNGP03.phx.gbl...
> Hey
> asp.net 2.0
> My code below crashes at the "return
> (int)cmd.Parameters["@.return"].Value;" line. In the debugging window I see
> that when this exception occur, the "return
> (int)cmd.Parameters["@.return"].Value;" has a NULL value...
> public override int SendMessage(MessageDetails message)
> {
> using (SqlConnection cn = new SqlConnection(this.ConnectionString))
> {
> SqlCommand cmd = new SqlCommand("AH_network_SendMessages", cn);
> cmd.CommandType = CommandType.StoredProcedure;
> cmd.Parameters.Add("@.receiver", SqlDbType.NVarChar).Value =
> message.Sender;
> cmd.Parameters.Add("@.sender", SqlDbType.NVarChar).Value =
> message.Sender;
> cmd.Parameters.Add("@.title", SqlDbType.NVarChar).Value =
> message.Title;
> cmd.Parameters.Add("@.body", SqlDbType.NVarChar).Value =
> message.Body;
> cmd.Parameters.Add("@.return", SqlDbType.Int).Direction =
> ParameterDirection.Output;
> cn.Open();
> int ret = ExecuteNonQuery(cmd);
> return (int)cmd.Parameters["@.return"].Value;
> }
> }
> This is the stored procdure called in the method:
> ALTER PROCEDURE dbo.AH_network_SendMessages
> @.sender nvarchar(256),
> @.receiver nvarchar(256),
> @.title nvarchar(100),
> @.body nvarchar(2000),
> @.return int OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT INTO AH_Messages (sender, receiver, title, body)
> VALUES (@.sender, @.receiver, @.title, @.body);
> set @.return = 1;
> select 1;
> END
> Please, what am I doing wrong here?
> Jeff
>
I am not sure why you are selecting 1 after setting the @.return. There is no
need unless you are doing something with it. Since you are ExecuteNonQuery,
this is a wasted cycle.
Next, why are you not testing "ret" in your code. If it is -1, the insert
failed for some reason. By testing that you could determine what your issue
is.
Also, why have you not wrapped the open and ExecuteNonQuery() in a try. Here
is a good pattern:
try
{
cn.Open();
int ret = ExecuteNonQuery(cmd);
}
finally
{
cn.Dispose();
}
Not sure what you are returning.
Next suggestion. Do not return until you test the parameter. If it is is
null, the cast to int will blow up (nice technical term ;-> ).
Be careful with calling thisgs return in a stored proc, as SQL Server
already returns a value (even if you do not declare it) called
@.RETURN_VALUE.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com/
****************************************
*********
Think Outside the Box!
****************************************
*********
"Jeff" <it_consultant1@.hotmail.com.NOSPAM> wrote in message
news:unl2osv6GHA.4580@.TK2MSFTNGP03.phx.gbl...
> Hey
> asp.net 2.0
> My code below crashes at the "return
> (int)cmd.Parameters["@.return"].Value;" line. In the debugging window I see
> that when this exception occur, the "return
> (int)cmd.Parameters["@.return"].Value;" has a NULL value...
> public override int SendMessage(MessageDetails message)
> {
> using (SqlConnection cn = new SqlConnection(this.ConnectionString))
> {
> SqlCommand cmd = new SqlCommand("AH_network_SendMessages", cn);
> cmd.CommandType = CommandType.StoredProcedure;
> cmd.Parameters.Add("@.receiver", SqlDbType.NVarChar).Value =
> message.Sender;
> cmd.Parameters.Add("@.sender", SqlDbType.NVarChar).Value =
> message.Sender;
> cmd.Parameters.Add("@.title", SqlDbType.NVarChar).Value =
> message.Title;
> cmd.Parameters.Add("@.body", SqlDbType.NVarChar).Value =
> message.Body;
> cmd.Parameters.Add("@.return", SqlDbType.Int).Direction =
> ParameterDirection.Output;
> cn.Open();
> int ret = ExecuteNonQuery(cmd);
> return (int)cmd.Parameters["@.return"].Value;
> }
> }
> This is the stored procdure called in the method:
> ALTER PROCEDURE dbo.AH_network_SendMessages
> @.sender nvarchar(256),
> @.receiver nvarchar(256),
> @.title nvarchar(100),
> @.body nvarchar(2000),
> @.return int OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT INTO AH_Messages (sender, receiver, title, body)
> VALUES (@.sender, @.receiver, @.title, @.body);
> set @.return = 1;
> select 1;
> END
> Please, what am I doing wrong here?
> Jeff
>
Thanks, the error was in the ExecuteNonQuery method, which is just a wrapper
around SqlCommand.ExecuteNonQuery... It's solved now, thanks to your tip
about checking ExecuteNonQuery
"Tor Bdshaug" <tor.badshaug@.nospam.com> wrote in message
news:%23TqCCAw6GHA.4996@.TK2MSFTNGP04.phx.gbl...
> What is the ExecuteNonQuery method you try to call after all?
> Normally, you will do a cmd.ExecuteNonQuery, rather than
> ExecuteNonQuery(cmd).
> Could it be that your ExecuteNonQuery(SqlCommand cmd) method in somewhat
> fail to do a cmd.ExecuteNonQuery.
> The fact that the output parameter does not have a value may suggest that
> may be the case.
> Tor Bdshaug
> tor.badshaug(AT)bekk.no
> "Jeff" <it_consultant1@.hotmail.com.NOSPAM> wrote in message
> news:unl2osv6GHA.4580@.TK2MSFTNGP03.phx.gbl...
>
Hey
Thanks, yes selecting 1 after setting the @.return to 1 is bad programming. I
know it, I will later rewrite this stored procedure and then I will fix
this.. the procedure is also missing a commit/rollback... I guess auto
commit is enabled but I prefer using explicit transaction...
"Cowboy (Gregory A. Beamer)" <NoSpamMgbworld@.comcast.netNoSpamM> wrote in
message news:ON3X9Jw6GHA.2288@.TK2MSFTNGP05.phx.gbl...
>I am not sure why you are selecting 1 after setting the @.return. There is
>no need unless you are doing something with it. Since you are
>ExecuteNonQuery, this is a wasted cycle.
> Next, why are you not testing "ret" in your code. If it is -1, the insert
> failed for some reason. By testing that you could determine what your
> issue is.
> Also, why have you not wrapped the open and ExecuteNonQuery() in a try.
> Here is a good pattern:
> try
> {
> cn.Open();
> int ret = ExecuteNonQuery(cmd);
> }
> finally
> {
> cn.Dispose();
> }
> Not sure what you are returning.
> Next suggestion. Do not return until you test the parameter. If it is is
> null, the cast to int will blow up (nice technical term ;-> ).
> Be careful with calling thisgs return in a stored proc, as SQL Server
> already returns a value (even if you do not declare it) called
> @.RETURN_VALUE.
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
> http://gregorybeamer.spaces.live.com/
> ****************************************
*********
> Think Outside the Box!
> ****************************************
*********
> "Jeff" <it_consultant1@.hotmail.com.NOSPAM> wrote in message
> news:unl2osv6GHA.4580@.TK2MSFTNGP03.phx.gbl...
>