newbie: my code crashe, NULL value

[Jeff]

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

 

[Tor Bådshaug]

If 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 Bådshaug
tor.badshaug(AT)bekk.no

 

[Tor Bådshaug]

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 Bådshaug
tor.badshaug(AT)bekk.no

 

[Cowboy \(Gregory A. Beamer\)]

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.

 

[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

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 Bådshaug
tor.badshaug(AT)bekk.no

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

 

[Jeff]

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....

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.

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