ExecuteScalar returns null

[js]

I am using the following C# code and T-SQL to get result object from a
SQL Server database. When my application runs, the ExecuteScalar
returns "10/24/2006 2:00:00 PM" if inserting a duplicated record. It
returns null for all other conditions. Does anyone know why? Does
anyone know how to get the output value? Thanks.

------ C# -----
aryParams = {'10/24/2006 2pm', '10/26/2006 3pm', 2821077, null};
object oRtnObject = null;
StoredProcCommandWrapper =
myDb.GetStoredProcCommandWrapper(strStoredProcName ,aryParams);
oRtnObject = myDb.ExecuteScalar(StoredProcCommandWrapper);

------ T-SQL ---
ALTER PROCEDURE [dbo].[procmyCalendarInsert]
@pBegin datetime,
@pEnd datetime,
@pUserId int,
@pOutput varchar(200) output
AS
BEGIN
SET NOCOUNT ON;

select * from myCalendar
where beginTime >= @pBegin and endTime <= @pEnd and userId = @pUserId

if @@rowcount <0
begin
print 'Path 1'
set @pOutput = 'Duplicated reservation'
select @pOutput as 'Result'
return -1
end
else
begin
print 'Path 2'
-- check if upperlimit (2) is reached
select rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))
,count(rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30))))
from myCalendar
group by rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))
having count(rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))) =2
and (rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30))) =
rtrim(cast(@pBegin as varchar(20)))+ ', ' + rtrim(cast(@pEnd as
varchar(20))))

-- If the @@rowcount is not equal to 0 then
-- at the time between @pBegin and @pEnd the maximum count of 2 is
reached

if @@rowcount <0
begin
print 'Path 3'
set @pOutput = '2 reservations are already taken for the hours'
select @pOutput as 'Result'
return -1
end
else
begin
print 'Path 4'
--safe to insert
insert dbo.myCalendar(beginTime, endTime,userId)
values (@pBegin, @pEnd, @pUserId)
if @@error = 0
begin
print 'Path 4:1 @@error=' + cast(@@error as varchar(1))
print 'Path 4:1 @@rowcount=' + cast(@@rowcount as varchar(1))
set @pOutput = 'Reservation succeeded'
select @pOutput as 'Result'
return 0
end
else
begin
print 'Path 4:2 @@rowcount=' + cast(@@rowcount as varchar(1))
set @pOutput = 'Failed to make reservation'
select @pOutput as 'Result'
return -1
end
end
end
END

[Bob Barrows [MVP]]

js wrote:

I am using the following C# code

There was no way for you to know it (except maybe by browsing through some
of the previous questions in this newsgroup before posting yours - always a
recommended practice) , but this is a classic ADO newsgroup. ADO.Net bears
very little resemblance to classic ADO so, while you may be lucky enough to
find a dotnet-knowledgeable person here who can answer your question, you
can eliminate the luck factor by posting your question to a group where
those dotnet-knowledgeable people hang out. I suggest
microsoft.public.dotnet.framework.adonet.

But read on:

and T-SQL to get result object from a
SQL Server database. When my application runs, the ExecuteScalar
returns "10/24/2006 2:00:00 PM" if inserting a duplicated record. It
returns null for all other conditions. Does anyone know why? Does
anyone know how to get the output value? Thanks.

------ C# -----
aryParams = {'10/24/2006 2pm', '10/26/2006 3pm', 2821077, null};
object oRtnObject = null;
StoredProcCommandWrapper =
myDb.GetStoredProcCommandWrapper(strStoredProcName ,aryParams);
oRtnObject = myDb.ExecuteScalar(StoredProcCommandWrapper);

------ T-SQL ---
ALTER PROCEDURE [dbo].[procmyCalendarInsert]
@pBegin datetime,
@pEnd datetime,
@pUserId int,
@pOutput varchar(200) output
AS
BEGIN
SET NOCOUNT ON;

select * from myCalendar
where beginTime >= @pBegin and endTime <= @pEnd and userId = @pUserId

if @@rowcount <0

This is extremely misguided. Not only is it grossly inefficient, retrieving
all the records that meet the requirements, it is also preventing you from
retrieving your output value. SQL Server does not send RETURN and OUTPUT
parameter values to the client until all resultsets are sent. The above
select statement is creating a resultset that wwill be sent to the client.

If you want to verify if records exist, use IF EXISTS, as in

IF EXISTS (select * from myCalendar
where beginTime >= @pBegin and endTime <= @pEnd and userId = @pUserId)

This is more efficient because it does not retrieve a resultset, it only
verifies that the records meeting therequirements exist. If you really want
a count of the records that meet the requirements (which does not seem to be
te case here) you should use:

declare @cnt int
Set @cnt= (select count(*) from myCalendar
where beginTime >= @pBegin and endTime <= @pEnd and userId = @pUserId)

Because the result is assigned to a variable, no resultset is created that
needs to be sent to the client.

begin
print 'Path 1'
set @pOutput = 'Duplicated reservation'
select @pOutput as 'Result'
return -1
end
else
begin
print 'Path 2'
-- check if upperlimit (2) is reached
select rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))
,count(rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30))))
from myCalendar
group by rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))
having count(rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))) =2
and (rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30))) =
rtrim(cast(@pBegin as varchar(20)))+ ', ' + rtrim(cast(@pEnd as
varchar(20))))

I'm not sure what the point of the above concatenation is: are you trying to
present a datetime in a particular format? If so, are you aware that

>
-- If the @@rowcount is not equal to 0 then
-- at the time between @pBegin and @pEnd the maximum count of 2 is
reached

You do realize that because of the intervening statements, the @@rowcount
function returns a different value than was returned the first time you used
it ... ? @@error and @@rowcount are only useful if used immediately after
the statement you wish to test. New statements cause these functions to
return new values.

Anyways, you already determined above that the records exist. Why bother
checking again?

>
if @@rowcount <0

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"