Filling a DataSet using a returned value from StoredProcedure

Hi all, I'm new to .net.

I'm trying to return a value from a StoredProcedure to a DataSet

----STORED PROCEDURE-------

craete procedure [dbo].[sample_procedure]
as
declare @count int
select @count = count(*) from dbo.Q_C_Users
return @count


-----------------c# FUNCTION-----------------------

public int returnCount()

{

int availableTables=0;

SqlConnection con = ConnectionManager.GetQueensDBConnection();
SqlCommand cmd = new SqlCommand("SAMPLE_PROCEDURE", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
dataAdapter.Fill(ds);
availableTables = System.Convert.ToInt16(ds.Tables[0].Rows[0].ItemArray[0].ToString());
return availableTables;

}


// by using the above code i coudn't get any output.

but once the stored procedure changed to a nore mal query its working i mean if the stored procedure written as


CREATE procedure [dbo].[SAMPLE_PROCEDURE]
AS
select count(*) from dbo.Q_C_Users


Its working fine, I guess if we return a value from Stored Proc we should add some parameters to SqlCommand but i have no idea about that.


PLS HELP, THANKS IN ADVANCED.

Good Day valluvanl

First change this

Code:

---

craete procedure [dbo].[sample_procedure]
as
declare @count int
select @count = count(*) from dbo.Q_C_Users
return @count

---

to this

Code:

---

craete procedure [dbo].[sample_procedure]
(
@OutResult int OUTPUT
)
as
set @OutResult = (select  count(*) from dbo.Q_C_Users)

---

and your C# code change it to look like this

Code:

---

public int returnCount()

{

int availableTables=0;

SqlConnection con = ConnectionManager.GetQueensDBConnection();
SqlCommand cmd = new SqlCommand("SAMPLE_PROCEDURE", con);
cmd.CommandType = CommandType.StoredProcedure;
cmdselect.Parameters.Add("@OutResult", SqlDbType.Int, 4);
cmdselect.Parameters["@OutResult"].Direction = ParameterDirection.Output;
try
{
    con.Open();
    cmd.ExecuteNonQuery();
      availableTables =(int) cmdselect.Parameters["@OutResult"].Value;
}
 catch (SqlException)
{
        throw;
}
finally
{
con.Close();
}
return availableTables;

---

and the Code will work.

Kind Regards

Vuyiswa Maseko