469,910 Members | 1,615 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,910 developers. It's quick & easy.

Stored procedure with ExecuteNonQuery not returning value

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?

Dec 5 '07 #1
8 13456

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" <ro*********@gmail.comwrote in message
news:e7**********************************@d61g2000 hsa.googlegroups.com...
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?
Dec 5 '07 #2
On Dec 5, 4:38 pm, "Dan Bass" <danb...@REMOVEblueCAPSbottle.com>
wrote:
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" <roundcri...@gmail.comwrote in message

news:e7**********************************@d61g2000 hsa.googlegroups.com...
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
Dec 5 '07 #3

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

"roundcrisis" <ro*********@gmail.comwrote in message
news:23**********************************@w40g2000 hsb.googlegroups.com...
On Dec 5, 4:38 pm, "Dan Bass" <danb...@REMOVEblueCAPSbottle.com>
wrote:
>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" <roundcri...@gmail.comwrote in message

news:e7**********************************@d61g200 0hsa.googlegroups.com...
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
Dec 5 '07 #4
"roundcrisis" <ro*********@gmail.comwrote in message
news:23**********************************@w40g2000 hsb.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.

Dec 5 '07 #5
On Dec 5, 6:30 pm, "Alberto Poblacion" <earthling-
quitaestoparacontes...@poblacion.orgwrote:
"roundcrisis" <roundcri...@gmail.comwrote in message

news:23**********************************@w40g2000 hsb.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
Dec 5 '07 #6
On Dec 5, 6:43 pm, roundcrisis <roundcri...@gmail.comwrote:
On Dec 5, 6:30 pm, "Alberto Poblacion" <earthling-

quitaestoparacontes...@poblacion.orgwrote:
"roundcrisis" <roundcri...@gmail.comwrote in message
news:23**********************************@w40g2000 hsb.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
???
Dec 6 '07 #7

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?
Dec 6 '07 #8
On Dec 6, 5:15 pm, "Dan Bass" <danb...@REMOVEblueCAPSbottle.com>
wrote:
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?
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
Dec 7 '07 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Michael | last post: by
2 posts views Thread by Mike P | last post: by
2 posts views Thread by Dino L. | last post: by
6 posts views Thread by David Lozzi | last post: by
4 posts views Thread by scparker | last post: by
9 posts views Thread by fniles | last post: by
2 posts views Thread by jed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.