Stored procedure with ExecuteNonQuery not returning value

[roundcrisis]

Hi all:

I m trying to get the return value of a stored procedure with

....
DbCommand command = connection.CreateCommand();
command.CommandText = "Exec some_SP";
command.CommandType = CommandType.StoredProcedure;

int temp = command.Parameters.Add(new OdbcParameter("@ReturnValue",
OdbcType.Int, 15));
DbParameter parameter = command.Parameters[temp];
parameter.Direction = ParameterDirection.Output;

command.ExecuteNonQuery();

return command.Parameters["@ReturnValue"].Value.ToString()
....

however the @ReturnValue is _always_ 0 (and i know it shouldn't be)
what s the problem?
is it the way I set up the parameter?

 

[Dan Bass]

firstly it should be this:
command.CommandText = "some_SP";

secondly, the parameter direction should be ParameterDirection.ReturnValue,
not output.

Try this, and if it still doesn't work, could you post your SP, or at least
the SQLe you use to run it, with results showing resultvalue can in fact no
be 0?

 

[roundcrisis]

firstly it should be this:
command.CommandText = "some_SP";

secondly, the parameter direction should be ParameterDirection.ReturnValue,
not output.

Try this, and if it still doesn't work, could you post your SP, or at least
the SQLe you use to run it, with results showing resultvalue can in fact no
be 0?

Hi all:

I m trying to get the return value of a stored procedure with

...
DbCommand command = connection.CreateCommand();
command.CommandText = "Exec some_SP";
command.CommandType = CommandType.StoredProcedure;

int temp = command.Parameters.Add(new OdbcParameter("@ReturnValue",
OdbcType.Int, 15));
DbParameter parameter = command.Parameters[temp];
parameter.Direction = ParameterDirection.Output;

command.ExecuteNonQuery();

return command.Parameters["@ReturnValue"].Value.ToString()
...

however the @ReturnValue is _always_ 0 (and i know it shouldn't be)
what s the problem?
is it the way I set up the parameter?- Hide quoted text -

- Show quoted text -

Thanks for your answer
I have tried those changes however it doesn't seem to solve the
problem
I cant show you the code for my SP because I dont use one, the user
can chose from a set of existing working stored procedures


running the above code doesn't throw any exception or anything and i
can see it running in the profiler with "exec " included
I tested without exec and runs as well without exceptions wich I find
very strange

Also, I wonder why it was the only way to create a parameter using an
odbcparameter

Cheers

 

[Dan Bass]

If you're connecting to SQL Server you may as well use the SqlClient for
your connection / command / parameters.

If you can't see the SP, how do you know that it doesn't return non-zero
values? I'd run the sql exec from Management Studio to confirm the results.

If your command type is Stored proc then you shouldn't have the exec...

firstly it should be this:
command.CommandText = "some_SP";

secondly, the parameter direction should be
ParameterDirection.ReturnValue,
not output.

Try this, and if it still doesn't work, could you post your SP, or at
least
the SQLe you use to run it, with results showing resultvalue can in fact
no
be 0?

Hi all:

I m trying to get the return value of a stored procedure with

...
DbCommand command = connection.CreateCommand();
command.CommandText = "Exec some_SP";
command.CommandType = CommandType.StoredProcedure;

int temp = command.Parameters.Add(new OdbcParameter("@ReturnValue",
OdbcType.Int, 15));
DbParameter parameter = command.Parameters[temp];
parameter.Direction = ParameterDirection.Output;

command.ExecuteNonQuery();

return command.Parameters["@ReturnValue"].Value.ToString()
...

however the @ReturnValue is _always_ 0 (and i know it shouldn't be)
what s the problem?
is it the way I set up the parameter?- Hide quoted text -

- Show quoted text -

Thanks for your answer
I have tried those changes however it doesn't seem to solve the
problem
I cant show you the code for my SP because I dont use one, the user
can chose from a set of existing working stored procedures


running the above code doesn't throw any exception or anything and i
can see it running in the profiler with "exec " included
I tested without exec and runs as well without exceptions wich I find
very strange

Also, I wonder why it was the only way to create a parameter using an
odbcparameter

Cheers

 

[Alberto Poblacion]

I have tried those changes however it doesn't seem to solve the
problem
[...]
running the above code doesn't throw any exception or anything

Your sample does not show any Openning or Closing of the Connection, but
I assume that it is somewhere in your code. Verify that you are reading the
return parameter *before* closing the connection. If you read it after doing
the Close(), it doesn't throw any exception or anything, it just returns
zero all the time.

 

[roundcrisis]

I have tried those changes however it doesn't seem to solve the
problem
[...]
running the above code doesn't throw any exception or anything

Your sample does not show any Openning or Closing of the Connection, but
I assume that it is somewhere in your code. Verify that you are reading the
return parameter *before* closing the connection. If you read it after doing
the Close(), it doesn't throw any exception or anything, it just returns
zero all the time.

Hi alberto: I m absolutely certain that the connection is open at the
time of trying to read the parameter value
actually at run time just after I run the executenonquery i can see
that the parameter value is empty

I cant actually change the type of client at the moment

 

[roundcrisis]

news:23a07806-281d-4a6c-8a33-c7b87b45ef3a@w40g2000hsb.googlegroups.com...

I have tried those changes however it doesn't seem to solve the
problem
[...]
running the above code doesn't throw any exception or anything

Your sample does not show any Openning or Closing of the Connection, but
I assume that it is somewhere in your code. Verify that you are reading the
return parameter *before* closing the connection. If you read it after doing
the Close(), it doesn't throw any exception or anything, it just returns
zero all the time.

Hi alberto: I m absolutely certain that the connection is open at the
time of trying to read the parameter value
actually at run time just after I run the executenonquery i can see
that the parameter value is empty

I cant actually change the type of client at the moment

rite so i tried this

IDbCommand command = connection.CreateCommand();
IDbDataParameter parameter = command.CreateParameter();
parameter.ParameterName = "ReturnValue";
parameter.Direction =
ParameterDirection.ReturnValue;
parameter.DbType = DbType.Int32;
command.Parameters.Add(parameter);

command.CommandText = storedProcedureName;
command.CommandType = CommandType.StoredProcedure;

command.ExecuteNonQuery();
insertValue =
((IDbDataParameter)command.Parameters[0]).Value;

and still no return value and the sp i m runing (as a test ) is a sp
that simply returns a value
???

 

[Dan Bass]

and still no return value and the sp i m runing (as a test ) is a sp
that simply returns a value
???

Right, please provide the SQL for the stored procedure.
Is it a SQL Server database that you'll always be connecting to?

 

[roundcrisis]

Right, please provide the SQL for the stored procedure.
Is it a SQL Server database that you'll always be connecting to?

ok, the problem lied on the fact that i was using an odbc connection
so the command text has to be

{ ? = CALL sp_Name }

what a nightmare anyway there you go