[scolty]

Hi, I cant get the following stored procedure to return the correct value.

[sql code]

create procedure numberofrows
as

declare @counter float

set @counter = (select count(*) from testtable2)

return @counter

[c# code]

...
            SqlConnection cn = new SqlConnection(connectionString);
            cn.Open();
            MessageBox.Show("connection made");
            SqlCommand storProc = new SqlCommand("numberofrows", cn);
            storProc.CommandType = CommandType.StoredProcedure;
            double result = storProc.ExecuteNonQuery();

            textBox1.Text = result.ToString();
...

i believe ExecuteNonQuery() is the issue but i dont know what to replace it with.

Any help would be appreciated.

[Curtis Rutland]

Well, first of all, ExecuteNonQuery returns the number of affected rows. In this case, there are none. It's not even a query that can affect rows.

I'd suggest modifying your SP in one of two ways. First, simply perform the select statement. I'm using my own table name here:

CREATE PROCEDURE spNumberOfRowsSelect
AS
BEGIN
	SET NOCOUNT ON;

    select count(*) as [Count] from verb
END
GO

And use ExecuteScalar instead of ExecuteNonQuery. Note that ExecuteScalar returns an Object, so it must be cast to an int for this.

You can also use Output parameters:

CREATE PROCEDURE spNumberOfRowsOut @count int output
AS
BEGIN
	SET NOCOUNT ON;

    select @count = count(*) from Verb
END
GO

In this case, you'd have to do something like this:

var conn = new SqlConnection(connStr);
var cmd = new SqlCommand("spNumberOfRowsOut", conn) {CommandType = CommandType.StoredProcedure};
var param = new SqlParameter("@count", SqlDbType.Int) { Direction = ParameterDirection.Output };
cmd.Parameters.Add(param);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
Console.WriteLine(cmd.Parameters["@count"].Value);