2.0: ExecuteScalar returns null

9 Aug 2006 11:09 AM

RAM

Hello,
I am learning .NET 2.0. I am trying to call a stored procedure:

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "NextDocumentNumber";
cmd.Transaction = t;
cmd.Connection = c;
int n = (int)cmd.ExecuteScalar(); // here problem

The problem is that ExecuteScalar returns null although executed in SQL
Server Management Studio it returns some integer value. I don't understand
why because transaction t and connection c are correct. Below the code of
stored procedure:

ALTER PROCEDURE [dbo].[NextDocumentNumber]

AS

BEGIN

SET NOCOUNT ON;

DECLARE @n int

SELECT @n = COUNT(*) FROM Documents

IF @n = 0

BEGIN

SELECT @n =StartOfRange FROM Numbering

WHERE Year = YEAR(GETDATE())

RETURN @n

END

SELECT @n = MAX(Number) FROM Documents

WHERE Year = YEAR(GETDATE()) AND Movement = 'PZ'

RETURN @n + 1

END

Could you help me please to solve the problem? Thank you very much!

/RAM/

9 Aug 2006 2:03 PM

David Browne

"RAM" <r_ahims***@poczta.onet.pl> wrote in message
news:ezXNVR6uGHA.1272@TK2MSFTNGP05.phx.gbl...
> Hello,
> I am learning .NET 2.0. I am trying to call a stored procedure:
>
> SqlCommand cmd = new SqlCommand();
> cmd.CommandText = "NextDocumentNumber";
> cmd.Transaction = t;
> cmd.Connection = c;
> int n = (int)cmd.ExecuteScalar(); // here problem
>

ExecuteScalar returns the first column of the first row of the first
resultset returned by the command. Your procedure uses a return value, not
a resultset.

Furthermore, it's bad form to use the return value of a stored procedure to
return data. By convention the return value of a stored procedure is used
to return a success or failure code. If you want to return data, use an
output parameter or a resultset.

Also this procedure is not even correctly coded. To return a sequence value
based on existing data requires careful transaction handling. See "Inside
Microsoft SQL Server 2005: T-SQL Programming pp428 et seq" for a complete
treatment. Or consider simply using an IDENTITY column.

David

9 Aug 2006 2:09 PM

David Browne

Show quote

"David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in
message news:ukOeXy7uGHA.3552@TK2MSFTNGP02.phx.gbl...
>
> "RAM" <r_ahims***@poczta.onet.pl> wrote in message
> news:ezXNVR6uGHA.1272@TK2MSFTNGP05.phx.gbl...
>> Hello,
>> I am learning .NET 2.0. I am trying to call a stored procedure:
>>
>> SqlCommand cmd = new SqlCommand();
>> cmd.CommandText = "NextDocumentNumber";
>> cmd.Transaction = t;
>> cmd.Connection = c;
>> int n = (int)cmd.ExecuteScalar(); // here problem
>>
>
> ExecuteScalar returns the first column of the first row of the first
> resultset returned by the command. Your procedure uses a return value,
> not a resultset.
>
> Furthermore, it's bad form to use the return value of a stored procedure
> to return data. By convention the return value of a stored procedure is
> used to return a success or failure code. If you want to return data, use
> an output parameter or a resultset.
>
> Also this procedure is not even correctly coded. To return a sequence
> value based on existing data requires careful transaction handling. See
> "Inside Microsoft SQL Server 2005: T-SQL Programming pp428 et seq" for a
> complete treatment. Or consider simply using an IDENTITY column.
>

sb

Inside
Microsoft SQL Server 2005: T-SQL Querying pp 428 et seq

David

9 Aug 2006 4:10 PM

RAM

>> Also this procedure is not even correctly coded. To return a sequence
>> value based on existing data requires careful transaction handling. See
>> "Inside Microsoft SQL Server 2005: T-SQL Programming pp428 et seq" for a
>> complete treatment.

I don't have "Inside Microsoft SQL Server 2005". Could you describe me
please the solution in a few words?
Thank you!

9 Aug 2006 4:19 PM

David Browne

"RAM" <r_ahims***@poczta.onet.pl> wrote in message
news:%23Sn%23M58uGHA.4512@TK2MSFTNGP05.phx.gbl...
>>> Also this procedure is not even correctly coded. To return a sequence
>>> value based on existing data requires careful transaction handling. See
>>> "Inside Microsoft SQL Server 2005: T-SQL Programming pp428 et seq" for a
>>> complete treatment.
>
> I don't have "Inside Microsoft SQL Server 2005". Could you describe me
> please the solution in a few words?
> Thank you!
>

Use an Identity column.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_3iex.asp

David

9 Aug 2006 4:24 PM

RAM

> Use an Identity column.

I cannot because 'seed' value in my application is taken from special table.
Is the second solution complicated?

10 Aug 2006 1:49 AM

msnews.microsoft.com

ExecuteScalar() will not work with a return value. You can get the return
value as a parameter, however, if you explicitly declare it. If you want to
use ExecuteScalar(), use SELECT on the value instead of return.

What are you attempting here, however? If this is an identity value, just
return SCOPE_IDENTITY. If not, why are you not using an Identity value?

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think Outside the Box!
*************************************************

Show quote

"RAM" <r_ahims***@poczta.onet.pl> wrote in message
news:ezXNVR6uGHA.1272@TK2MSFTNGP05.phx.gbl...
> Hello,
> I am learning .NET 2.0. I am trying to call a stored procedure:
>
> SqlCommand cmd = new SqlCommand();
> cmd.CommandText = "NextDocumentNumber";
> cmd.Transaction = t;
> cmd.Connection = c;
> int n = (int)cmd.ExecuteScalar(); // here problem
>
> The problem is that ExecuteScalar returns null although executed in SQL
> Server Management Studio it returns some integer value. I don't understand
> why because transaction t and connection c are correct. Below the code of
> stored procedure:
>
> ALTER PROCEDURE [dbo].[NextDocumentNumber]
>
> AS
>
> BEGIN
>
> SET NOCOUNT ON;
>
> DECLARE @n int
>
> SELECT @n = COUNT(*) FROM Documents
>
> IF @n = 0
>
> BEGIN
>
> SELECT @n =StartOfRange FROM Numbering
>
> WHERE Year = YEAR(GETDATE())
>
> RETURN @n
>
> END
>
> SELECT @n = MAX(Number) FROM Documents
>
> WHERE Year = YEAR(GETDATE()) AND Movement = 'PZ'
>
> RETURN @n + 1
>
> END
>
> Could you help me please to solve the problem? Thank you very much!
>
> /RAM/
>
>