<newbie> Problem with return value from SP

[Jeff]

ASP.NET 2.0

Below is the source code of my stored procedure and some C# code from my
webproject. The PROBLEM is that the stored procedure for example returns the
value 1, but in the C# code variable t (int t =
Convert.ToInt32(cmd.ExecuteScalar()) get the value 0, but it should be
1.....

*************** table and stored procedure **************
create table Location (
Id uniqueidentifier not null,
Description nvarchar(100),
CONSTRAINT PK_Location PRIMARY KEY (Id)
)

CREATE PROCEDURE dbo.Test @app uniqueidentifier
AS
SET NOCOUNT ON
DECLARE @count int;
SELECT @count = count(*) from Location where Id = @app;
RETURN @count;

********************* C# code ***********************
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("Test", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@app", SqlDbType.UniqueIdentifier).Value = app;

cn.Open();
int t = Convert.ToInt32(cmd.ExecuteScalar());

if (t == 0)
return false;
else
return true;
}

What must I do so the C# code catches the correct value?

Best Regards!

Jeff

 

[KJ]

I think you need to change your proc to the below. If my memory serves
me well, executescalar returns the first row, first column value from
the resultset.

CREATE PROCEDURE dbo.Test @app uniqueidentifier
AS
SET NOCOUNT ON
SELECT count(*)
from Location where Id = @app;

 

[Guest]

ASP.NET 2.0

Below is the source code of my stored procedure and some C# code from my
webproject. The PROBLEM is that the stored procedure for example returns the
value 1, but in the C# code variable t (int t =
Convert.ToInt32(cmd.ExecuteScalar()) get the value 0, but it should be
1.....

*************** table and stored procedure **************
create table Location (
Id uniqueidentifier not null,
Description nvarchar(100),
CONSTRAINT PK_Location PRIMARY KEY (Id)
)

CREATE PROCEDURE dbo.Test @app uniqueidentifier
AS
SET NOCOUNT ON
DECLARE @count int;
SELECT @count = count(*) from Location where Id = @app;
RETURN @count;

********************* C# code ***********************
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("Test", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@app", SqlDbType.UniqueIdentifier).Value = app;

cn.Open();
int t = Convert.ToInt32(cmd.ExecuteScalar());

if (t == 0)
return false;
else
return true;

}

What must I do so the C# code catches the correct value?

Best Regards!

Jeff

Your stored procedure returned nothing

Eiter change it as

ALTER PROCEDURE dbo.Test @app uniqueidentifier
AS
SELECT count(*) from Location where Id = @app;

Or declare the @count as an OUT parameter (see BOL)

 

[Guest]

Your stored procedure returned nothing

Eiter change it as

ALTER PROCEDURE dbo.Test @app uniqueidentifier
AS
SELECT count(*) from Location where Id = @app;

Or declare the @count as an OUT parameter (see BOL)- Hide quoted text -

- Show quoted text -

ADO.NET: Get Out Parameters from a Stored Procedure
http://quickstart.developerfusion.co.uk/QuickStart/howto/doc/adoplus/OutParams.aspx

 

[bruce barker]

ExecuteScalar returns the value of the first column of the first row. it
will thow an error if there is not a row returned. you can change the
proc return a row:

SELECT count(*) as Count from Location where Id = @app;

or access the return value in the c# code.

SqlCommand cmd = new SqlCommand("Test", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@app", SqlDbType.UniqueIdentifier).Value = app;
cmd.Parameters.Add("@returnValue", SqlDbType.Int).Direction =
ParameterDirection.ReturnValue;

cn.Open();
cmd.ExecuteNonQuery();
int t = Convert.ToInt32(cmd.Parameters["@returnValue"].Value);

note: you cannot access a return value until all resultsets have been read.

-- bruce (sqlwork.com)