newbie: my code crashe, NULL value


J

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
 
Ad

Advertisements

T

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
 
T

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
 
C

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

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 Bådshaug said:
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

Jeff said:
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
 
Ad

Advertisements

J

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


Cowboy (Gregory A. Beamer) said:
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 said:
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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top