|
|
Posted by js on 10/24/06 23:15
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
Navigation:
[Reply to this message]
|