Getting back a return value from a SQL Count query

[Greg Smith]

I am trying to write a SQL query that will return a record count to my C#
application. I wrote the following query:

ALTER PROCEDURE up_justificatio n_duplicate
AS
SELECT COUNT(*)
FROM tblJustificatio ns

RETURN COUNT(*)
The table has four records in it. Running the query in the IDE gets me the
following results:

Running dbo."up_justifi cation_duplicat e".
-----------
4
No more results.
(1 row(s) returned)
@RETURN_VALUE = 1
Finished running dbo."up_justifi cation_duplicat e".

I have tried to extract the count in my application but all I know how to do
is read the return value (always a 1) with a statement like "x =
da.SelectComman d.Parameters[0].Value.ToString ();".
How do I get back the correct count, in this case 4?

Any help is greatly appreciated.

[Nicholas Paldino [.NET/C# MVP]]

Greg,

The way you have it, the count is being returned in a result set. What
you want to do is execute the command getting the scalar. Call
ExecuteScalar on the SqlCommand you are using to access the database. If
you want to set the count to the return value, then you need to change your
stored procedure to return a value like this:

alter PROCEDURE up_justificatio n_duplicate
AS
return select COUNT(*) from asset

Then you should be able to access the return value through the
parameters.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"Greg Smith" <gj*@umn.edu> wrote in message
news:ev******** ******@TK2MSFTN GP10.phx.gbl...

I am trying to write a SQL query that will return a record count to my C#
application. I wrote the following query:

ALTER PROCEDURE up_justificatio n_duplicate
AS
SELECT COUNT(*)
FROM tblJustificatio ns

RETURN COUNT(*)
The table has four records in it. Running the query in the IDE gets me the following results:

Running dbo."up_justifi cation_duplicat e".
-----------
4
No more results.
(1 row(s) returned)
@RETURN_VALUE = 1
Finished running dbo."up_justifi cation_duplicat e".

I have tried to extract the count in my application but all I know how to do is read the return value (always a 1) with a statement like "x =
da.SelectComman d.Parameters[0].Value.ToString ();".
How do I get back the correct count, in this case 4?

Any help is greatly appreciated.