By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,024 Members | 2,027 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,024 IT Pros & Developers. It's quick & easy.

How do i retrieve a Return Value (0 or 1) from my stored proc?

P: n/a
Hi there,
I have a storec proc that schedules a Sql job and finally it returns
0 then it was successfull and if it returns 1 then its unsuccessful.
Now when i run the stored proc in the query analyzer its returning

SQLServerAgent is not currently running so it cannot be notified of
this action.
Stored Procedure: aisinspect.dbo.SP_ChangeInspectionDateNew
Return Code = 0

So in my code behind page i am trying to retrieve the restult with the
below line of code.

Result = cmd.ExecuteScalar.ToString

But its throwing "Object reference not set to an instance of an
object."

I know that ExectureSclara returns the first row first column value.
But my stored proc doesn't return as a row column result its simply
displyaing the above text.
So i am just wondering any other method of cmd object that return the
above sort of values? or i do need to change my stored proc just to
return the result in a row (either 0 or 1) so that i can use
Executescalar in my codebehind page?

Thanks
-L

Dec 21 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
If you are using hte return keyword in in sproc, executeScalar won't work.
Try SELECT 1 and SELECT 0

also is ExecuteScalar() not ExecuteScalar (paranthesis)

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/
"Learner" <pr****@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Hi there,
I have a storec proc that schedules a Sql job and finally it returns
0 then it was successfull and if it returns 1 then its unsuccessful.
Now when i run the stored proc in the query analyzer its returning

SQLServerAgent is not currently running so it cannot be notified of
this action.
Stored Procedure: aisinspect.dbo.SP_ChangeInspectionDateNew
Return Code = 0

So in my code behind page i am trying to retrieve the restult with the
below line of code.

Result = cmd.ExecuteScalar.ToString

But its throwing "Object reference not set to an instance of an
object."

I know that ExectureSclara returns the first row first column value.
But my stored proc doesn't return as a row column result its simply
displyaing the above text.
So i am just wondering any other method of cmd object that return the
above sort of values? or i do need to change my stored proc just to
return the result in a row (either 0 or 1) so that i can use
Executescalar in my codebehind page?

Thanks
-L

Dec 21 '05 #2

P: n/a
If you are using return on the SP use:

SqlParameter ret = new SqlParameter("@RETURN_VALUE", returnValue);
ret.Direction = ParameterDirection.ReturnValue;
cmd.Parameteres.Add(ret);

// Get Data from Database
int numRows = cmd.ExecuteNonQuery();

this._id = int.Parse(ret.Value.ToString());
Dec 21 '05 #3

P: n/a
the retun value comes back as a parameter

try:

SqlParameter returnValue = new
SqlParameter("retuenValue",SqlDbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(returnValue)
cmd.ExecuteNonQuery();

int theReturnValue = (int) returnValue.Value;

or change your sql to

set nocount on
declare @r int
exec @r = myproc
select @r as returnValue

than executescaler will work
-- bruce (sqlwork.com)

"Learner" <pr****@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Hi there,
I have a storec proc that schedules a Sql job and finally it returns
0 then it was successfull and if it returns 1 then its unsuccessful.
Now when i run the stored proc in the query analyzer its returning

SQLServerAgent is not currently running so it cannot be notified of
this action.
Stored Procedure: aisinspect.dbo.SP_ChangeInspectionDateNew
Return Code = 0

So in my code behind page i am trying to retrieve the restult with the
below line of code.

Result = cmd.ExecuteScalar.ToString

But its throwing "Object reference not set to an instance of an
object."

I know that ExectureSclara returns the first row first column value.
But my stored proc doesn't return as a row column result its simply
displyaing the above text.
So i am just wondering any other method of cmd object that return the
above sort of values? or i do need to change my stored proc just to
return the result in a row (either 0 or 1) so that i can use
Executescalar in my codebehind page?

Thanks
-L

Dec 21 '05 #4

P: n/a
Hello there,

You can do one thing design stored procedure with one out parameter.
Then in that procedure you assign your result.
From your asp.net code execute that procedure before executeNonQuery

define a parameter with out property.
Then executeNonQuery
Then retrive value of parameter.

B

Dec 22 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.