|
|
Posted by Bob Barrows [MVP] on 10/25/06 10:53
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"
Navigation:
[Reply to this message]
|