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.Crea teCommand();
command.Command Text = "Exec some_SP";
command.Command Type = CommandType.Sto redProcedure;

int temp = command.Paramet ers.Add(new OdbcParameter(" @ReturnValue",
OdbcType.Int, 15));
DbParameter parameter = command.Paramet ers[temp];
parameter.Direc tion = ParameterDirect ion.Output;

command.Execute NonQuery();

return command.Paramet ers["@ReturnVal ue"].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]

Re: Stored procedure with ExecuteNonQuery not returning value


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

secondly, the parameter direction should be ParameterDirect ion.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?



"roundcrisi s" <roundcrisis@gm ail.comwrote in message
news:e792a11c-0053-4d96-84a4-1e920075b4bf@d6 1g2000hsa.googl egroups.com...

Hi all:
>
I m trying to get the return value of a stored procedure with
>
...
DbCommand command = connection.Crea teCommand();
command.Command Text = "Exec some_SP";
command.Command Type = CommandType.Sto redProcedure;
>
int temp = command.Paramet ers.Add(new OdbcParameter(" @ReturnValue",
OdbcType.Int, 15));
DbParameter parameter = command.Paramet ers[temp];
parameter.Direc tion = ParameterDirect ion.Output;
>
command.Execute NonQuery();
>
return command.Paramet ers["@ReturnVal ue"].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?
>

[roundcrisis]

Re: Stored procedure with ExecuteNonQuery not returning value

On Dec 5, 4:38 pm, "Dan Bass" <danb...@REMOVE blueCAPSbottle. com>
wrote:

firstly it should be this:
command.Command Text = "some_SP";
>
secondly, the parameter direction should be ParameterDirect ion.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?
>
"roundcrisi s" <roundcri...@gm ail.comwrote in message
>
news:e792a11c-0053-4d96-84a4-1e920075b4bf@d6 1g2000hsa.googl egroups.com...
>
>
>

Hi all:

>

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

>

...
DbCommand command = connection.Crea teCommand();
command.Command Text = "Exec some_SP";
command.Command Type = CommandType.Sto redProcedure;

>

int temp = command.Paramet ers.Add(new OdbcParameter(" @ReturnValue",
OdbcType.Int, 15));
DbParameter parameter = command.Paramet ers[temp];
parameter.Direc tion = ParameterDirect ion.Output;

>

command.Execute NonQuery();

>

return command.Paramet ers["@ReturnVal ue"].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]

Re: Stored procedure with ExecuteNonQuery not returning value


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



"roundcrisi s" <roundcrisis@gm ail.comwrote in message
news:23a07806-281d-4a6c-8a33-c7b87b45ef3a@w4 0g2000hsb.googl egroups.com...

On Dec 5, 4:38 pm, "Dan Bass" <danb...@REMOVE blueCAPSbottle. com>
wrote:

>firstly it should be this:
> command.Command Text = "some_SP";
>>
>secondly, the parameter direction should be
>ParameterDirec tion.ReturnValu e,
>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...@gm ail.comwrote in message
>>
>news:e792a11 c-0053-4d96-84a4-1e920075b4bf@d6 1g2000hsa.googl egroups.com...
>>
>>
>>

Hi all:

>>

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

>>

...
DbCommand command = connection.Crea teCommand();
command.Command Text = "Exec some_SP";
command.Command Type = CommandType.Sto redProcedure;

>>

int temp = command.Paramet ers.Add(new OdbcParameter(" @ReturnValue",
OdbcType.Int, 15));
DbParameter parameter = command.Paramet ers[temp];
parameter.Direc tion = ParameterDirect ion.Output;

>>

command.Execute NonQuery();

>>

return command.Paramet ers["@ReturnVal ue"].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]

Re: Stored procedure with ExecuteNonQuery not returning value

"roundcrisi s" <roundcrisis@gm ail.comwrote in message
news:23a07806-281d-4a6c-8a33-c7b87b45ef3a@w4 0g2000hsb.googl egroups.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.

[roundcrisis]

Re: Stored procedure with ExecuteNonQuery not returning value

On Dec 5, 6:30 pm, "Alberto Poblacion" <earthling-
quitaestoparaco ntes...@poblaci on.orgwrote:

"roundcrisi s" <roundcri...@gm ail.comwrote in message
>
news:23a07806-281d-4a6c-8a33-c7b87b45ef3a@w4 0g2000hsb.googl egroups.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

[roundcrisis]

Re: Stored procedure with ExecuteNonQuery not returning value

On Dec 5, 6:43 pm, roundcrisis <roundcri...@gm ail.comwrote:

On Dec 5, 6:30 pm, "Alberto Poblacion" <earthling-
>
quitaestoparaco ntes...@poblaci on.orgwrote:

"roundcrisi s" <roundcri...@gm ail.comwrote in message

>

news:23a07806-281d-4a6c-8a33-c7b87b45ef3a@w4 0g2000hsb.googl egroups.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.Crea teCommand();
IDbDataParamete r parameter = command.CreateP arameter();
parameter.Param eterName = "ReturnValu e";
parameter.Direc tion =
ParameterDirect ion.ReturnValue ;
parameter.DbTyp e = DbType.Int32;
command.Paramet ers.Add(paramet er);

command.Command Text = storedProcedure Name;
command.Command Type = CommandType.Sto redProcedure;

command.Execute NonQuery();
insertValue =
((IDbDataParame ter)command.Par ameters[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]

Re: Stored procedure with ExecuteNonQuery not returning value

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]

Re: Stored procedure with ExecuteNonQuery not returning value

On Dec 6, 5:15 pm, "Dan Bass" <danb...@REMOVE blueCAPSbottle. 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