My return parameter is coming back null

  • Thread starter Thread starter tshad
  • Start date Start date
T

tshad

I am calling a stored procedure which I know is returning 0, but my code
shows it as null.

What am I missing?

*************************************
SqlCommand dbCommand = new SqlCommand("Add",
new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnectString"].ConnectionString));
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Parameters.AddWithValue("@CALLS",
Convert.ToInt32(((Label)gvr.FindControl("lblGiftCount")).Text));
retValue = dbCommand.Parameters.Add("RetValue", SqlDbType.Int);
retValue.Direction = ParameterDirection.ReturnValue;

dbCommand.Connection.Open();

dbReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
*************************************

After the ExecureRead, retValue.Value = null????

Thanks,

Tom
 
I am calling a stored procedure which I know is returning 0, but my code
shows it as null.

What am I missing?

*************************************
SqlCommand dbCommand = new SqlCommand("Add",
new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnectString"].ConnectionString));
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Parameters.AddWithValue("@CALLS",
Convert.ToInt32(((Label)gvr.FindControl("lblGiftCount")).Text));
retValue = dbCommand.Parameters.Add("RetValue", SqlDbType.Int);
retValue.Direction = ParameterDirection.ReturnValue;

dbCommand.Connection.Open();

dbReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
*************************************

After the ExecureRead, retValue.Value = null????
Click to expand...

Could you try with:

retValue = dbCommand.Parameters.Add("@RetValue", SqlDbType.Int);

If that does not solve the problem, then I think we need to see
the SP.

Arne
 
OK,

I changed it to:

******************************************
dbCommand = new SqlCommand("GetValue",
new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnectString"].ConnectionString));
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Parameters.AddWithValue("@CALLS",
Convert.ToInt32(((Label)gvr.FindControl("lblGiftCount")).Text));
retValue = dbCommand.Parameters.Add("@RetValue", SqlDbType.Int);
retValue.Direction = ParameterDirection.ReturnValue;

dbCommand.Connection.Open();

dbReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
********************************************
The SP only does a return:

*******************************************
ALTER PROCEDURE [dbo].[GetValue]
@CALLS int
AS

RETURN 1
*******************************************

If I look at retValue.Value or dbCommand.Parameters[1].Value, I get null.

I also tried taking out the CommandBehavior.CloseConnection out but that
didn't help.

I did notice in the profiler what is being sent is:

exec GetValue @CALLS=15

and not somthing like

Declare @Temp int
exec @Temp = GetValue @CALLS=15

Which when I run from a query, @Temp = 1.

Thanks,

Tom

Arne Vajhøj said:
I am calling a stored procedure which I know is returning 0, but my code
shows it as null.

What am I missing?

*************************************
SqlCommand dbCommand = new SqlCommand("Add",
new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnectString"].ConnectionString));
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Parameters.AddWithValue("@CALLS",
Convert.ToInt32(((Label)gvr.FindControl("lblGiftCount")).Text));
retValue = dbCommand.Parameters.Add("RetValue", SqlDbType.Int);
retValue.Direction = ParameterDirection.ReturnValue;

dbCommand.Connection.Open();

dbReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
*************************************

After the ExecureRead, retValue.Value = null????
Click to expand...

Could you try with:

retValue = dbCommand.Parameters.Add("@RetValue", SqlDbType.Int);

If that does not solve the problem, then I think we need to see
the SP.

Arne
Click to expand...
 
Figured it out.

Apparently, you can't get the Return value until you have read through the
reader and then closed the connection (not sure if I have to close the
connection).

But when I added:

while(dbReader.Read())
{
}

It gave me back the return.

Also, not sure if I need to run the while to the end or just read the first
record.

I actually am not getting a result set back, so I changed it to:

dbCommand.Connection.Open();

dbCommand.ExecuteNonQuery();

dbCommand.Connection.Close();

And that should work work

Thanks,

Tom
tshad said:
OK,

I changed it to:

******************************************
dbCommand = new SqlCommand("GetValue",
new

SqlConnection(ConfigurationManager.ConnectionStrings["ConnectString"].ConnectionString));
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Parameters.AddWithValue("@CALLS",
Convert.ToInt32(((Label)gvr.FindControl("lblGiftCount")).Text));
retValue = dbCommand.Parameters.Add("@RetValue", SqlDbType.Int);
retValue.Direction = ParameterDirection.ReturnValue;

dbCommand.Connection.Open();

dbReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
********************************************
The SP only does a return:

*******************************************
ALTER PROCEDURE [dbo].[GetValue]
@CALLS int
AS

RETURN 1
*******************************************

If I look at retValue.Value or dbCommand.Parameters[1].Value, I get null.

I also tried taking out the CommandBehavior.CloseConnection out but that
didn't help.

I did notice in the profiler what is being sent is:

exec GetValue @CALLS=15

and not somthing like

Declare @Temp int
exec @Temp = GetValue @CALLS=15

Which when I run from a query, @Temp = 1.

Thanks,

Tom

Arne Vajhøj said:
I am calling a stored procedure which I know is returning 0, but my code
shows it as null.

What am I missing?

*************************************
SqlCommand dbCommand = new SqlCommand("Add",
new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnectString"].ConnectionString));
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Parameters.AddWithValue("@CALLS",
Convert.ToInt32(((Label)gvr.FindControl("lblGiftCount")).Text));
retValue = dbCommand.Parameters.Add("RetValue", SqlDbType.Int);
retValue.Direction = ParameterDirection.ReturnValue;

dbCommand.Connection.Open();

dbReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
*************************************

After the ExecureRead, retValue.Value = null????
Click to expand...

Could you try with:

retValue = dbCommand.Parameters.Add("@RetValue", SqlDbType.Int);

If that does not solve the problem, then I think we need to see
the SP.

Arne
Click to expand...
Click to expand...
 
tshad said:
Figured it out.

Apparently, you can't get the Return value until you have read through the
reader and then closed the connection (not sure if I have to close the
connection).
Click to expand...

The requirements are strict: You can only read the return or output
parameters AFTER you have closed the datareader but BEFORE you close the
connection.
Note that the requirement is to CLOSE the DataReader; it is not
necessary to read through all the records. And in your case (as you have
already figured out), given that you are not returning any rows at all, it
is better to use ExecuteNonQuery instead of ExecuteReader.
 
Alberto Poblacion said:
The requirements are strict: You can only read the return or output
parameters AFTER you have closed the datareader but BEFORE you close the
connection.
Note that the requirement is to CLOSE the DataReader; it is not
necessary to read through all the records. And in your case (as you have
already figured out), given that you are not returning any rows at all, it
is better to use ExecuteNonQuery instead of ExecuteReader.
Click to expand...
What about when you have CommandBehavior.CloseConnection?

Doesn't this close the DataReader and Connection at the same time when you
are finished reading?

How would you read all the data, and get the return values if you have to
close the DataReader before you can get the return values?

Thanks,

Tom
 
How would you read all the data, and get the return values if you have to
close the DataReader before you can get the return values?
Click to expand...

I cannot think of a situation where I would want BOTH a recordset AND return
value coming back from a stored procedure. Any SP I've ever written with a
return value I've executed with ExecuteNonQuery.
 
tshad said:
What about when you have CommandBehavior.CloseConnection?

Doesn't this close the DataReader and Connection at the same time when you
are finished reading?

How would you read all the data, and get the return values if you have to
close the DataReader before you can get the return values?
Click to expand...

This is why CommandBehavior.CloseConnection is _optional_. Usually, you
want to close the connection after you are done using the datareader, and in
these cases you can achieve both things at the same time by means of
CommandBehavior.CloseConnection. But there are several cases in which you
don't want to close the connection. One is when you need to retrieve output
parameters. Another is when you need to immediately send additional queries
to the server, so you want the connection open for sending those commands.
When you need to do this, you should NOT specify
CommandBehavior.CloseConnection, because it would frustrate your purpose.
 
I cannot think of a situation where I would want BOTH a recordset AND return
value coming back from a stored procedure.
Click to expand...

If the SP has to return a result set and a single value, then returning
the single value as a return value instead of as a second result set
seems relative obvious.
Any SP I've ever written with a
return value I've executed with ExecuteNonQuery.
Click to expand...

I have seen both result set and return value many times.

(Sybase not MS SQLServer but ...)

Arne
 
Back
Top