Home All Groups Group Topic Archive Search About

Num records returned from query

microsoft.public.dotnet.languages.vb
Author
28 Nov 2007 4:21 PM
Michael
Hi Everyone,
I never noticed this before, I hope someone can help here. I have the
following query:
CREATE PROCEDURE [dbo].[GetWeeklyAttendance2]
@PatientId varchar(20),
@StartDate Datetime,
@EndDate Datetime
AS
SELECT P.PatientID, A.AttendDate, 'Attendance' AS NarcoProgram, '3' AS
ProgramId,
P.LastName + ', ' + P.FirstName as PatientName, p.HomelessShelter,
p.ClientSchedule
FROM Attendance.dbo.PATIENTS P RIGHT JOIN Attendance.dbo.ATTENDANCE A ON
P.PatientID = A.PatientID
WHERE a.AttendDate >= @StartDate and a.AttendDate <= @EndDate and
p.PatientId = @PatientId and P.ActiveClient=1 and  P.HomelessShelter > 0
Union
SELECT P.PatientID, A.AttendDate, 'Independence' AS NarcoProgram, '4' AS
ProgramId,
P.LastName + ', ' + P.FirstName as PatientName, p.HomelessShelter,
p.ClientSchedule
FROM Independence.dbo.PATIENTS P RIGHT JOIN Independence.dbo.ATTENDANCE A ON
P.PatientID = A.PatientID
WHERE  a.AttendDate >= @StartDate and a.AttendDate <= @EndDate  and
p.PatientId = @PatientId and P.ActiveClient=1 and  P.HomelessShelter > 0
Union
SELECT P.PatientID, A.AttendDate, 'Alternitives_RH' AS NarcoProgram, '2' AS
ProgramId,
P.LastName + ', ' + P.FirstName as PatientName, p.HomelessShelter,
p.ClientSchedule
FROM Alternitives_RH.dbo.PATIENTS P RIGHT JOIN
Alternitives_RH.dbo.ATTENDANCE A ON P.PatientID = A.PatientID
WHERE  a.AttendDate >= @StartDate and a.AttendDate <= @EndDate  and
p.PatientId = @PatientId and P.ActiveClient=1 and  P.HomelessShelter > 0
Union
SELECT P.PatientID, A.AttendDate, 'Alternatives_BP' AS NarcoProgram, '1' AS
ProgramId,
P.LastName + ', ' + P.FirstName as PatientName, p.HomelessShelter,
p.ClientSchedule
FROM Alternatives_BP.dbo.PATIENTS P RIGHT JOIN
Alternatives_BP.dbo.ATTENDANCE A ON P.PatientID = A.PatientID
WHERE  a.AttendDate >= @StartDate and a.AttendDate <= @EndDate  and
p.PatientId = @PatientId and P.ActiveClient=1 and  P.HomelessShelter > 0
order by P.PatientID
GO

What is happening, if I run the stored procedure in Query Analyzer I will
get say 7 records for a patientid, but when the query (same proc and
parameters) is run through vb.net and SqlCommand I always get one less record
than I got in Query Analyzer. Does this make any since to you all. THanks for
any suggestions.
Michael Lee

Author
28 Nov 2007 5:06 PM
zacks
On Nov 28, 11:21 am, Michael <Mich***@discussions.microsoft.com>
wrote:
Show quoteHide quote
> Hi Everyone,
> I never noticed this before, I hope someone can help here. I have the
> following query:
> CREATE PROCEDURE [dbo].[GetWeeklyAttendance2]
> @PatientId varchar(20),
> @StartDate Datetime,
> @EndDate Datetime
>  AS
> SELECT P.PatientID, A.AttendDate, 'Attendance' AS NarcoProgram, '3' AS
> ProgramId,
> P.LastName + ', ' + P.FirstName as PatientName, p.HomelessShelter,
> p.ClientSchedule
> FROM Attendance.dbo.PATIENTS P RIGHT JOIN Attendance.dbo.ATTENDANCE A ON
> P.PatientID = A.PatientID
> WHERE a.AttendDate >= @StartDate and a.AttendDate <= @EndDate and
> p.PatientId = @PatientId and P.ActiveClient=1 and  P.HomelessShelter > 0
> Union
> SELECT P.PatientID, A.AttendDate, 'Independence' AS NarcoProgram, '4' AS
> ProgramId,
> P.LastName + ', ' + P.FirstName as PatientName, p.HomelessShelter,
> p.ClientSchedule
> FROM Independence.dbo.PATIENTS P RIGHT JOIN Independence.dbo.ATTENDANCE A ON
> P.PatientID = A.PatientID
> WHERE  a.AttendDate >= @StartDate and a.AttendDate <= @EndDate  and
> p.PatientId = @PatientId and P.ActiveClient=1 and  P.HomelessShelter > 0
> Union
> SELECT P.PatientID, A.AttendDate, 'Alternitives_RH' AS NarcoProgram, '2' AS
> ProgramId,
> P.LastName + ', ' + P.FirstName as PatientName, p.HomelessShelter,
> p.ClientSchedule
> FROM Alternitives_RH.dbo.PATIENTS P RIGHT JOIN
> Alternitives_RH.dbo.ATTENDANCE A ON P.PatientID = A.PatientID
> WHERE  a.AttendDate >= @StartDate and a.AttendDate <= @EndDate  and
> p.PatientId = @PatientId and P.ActiveClient=1 and  P.HomelessShelter > 0
> Union
> SELECT P.PatientID, A.AttendDate, 'Alternatives_BP' AS NarcoProgram, '1' AS
> ProgramId,
> P.LastName + ', ' + P.FirstName as PatientName, p.HomelessShelter,
> p.ClientSchedule
> FROM Alternatives_BP.dbo.PATIENTS P RIGHT JOIN
> Alternatives_BP.dbo.ATTENDANCE A ON P.PatientID = A.PatientID
> WHERE  a.AttendDate >= @StartDate and a.AttendDate <= @EndDate  and
> p.PatientId = @PatientId and P.ActiveClient=1 and  P.HomelessShelter > 0
> order by P.PatientID
> GO
>
> What is happening, if I run the stored procedure in Query Analyzer I will
> get say 7 records for a patientid, but when the query (same proc and
> parameters) is run through vb.net and SqlCommand I always get one less record
> than I got in Query Analyzer. Does this make any since to you all. THanks for
> any suggestions.
> Michael Lee

Try running the two commands while running the SQL Profiler may tell
you something. At least it will verify for you if the two commands are
indeed the same.
Author
28 Nov 2007 5:12 PM
Trevor Benedict
Check what values are being passed into the date parameters.

Regards,

Trevor Benedict
MCSD



Show quoteHide quote
"Michael" <Mich***@discussions.microsoft.com> wrote in message
news:0C967B94-2F2D-4356-B223-6C47882387F8@microsoft.com...
> Hi Everyone,
> I never noticed this before, I hope someone can help here. I have the
> following query:
> CREATE PROCEDURE [dbo].[GetWeeklyAttendance2]
> @PatientId varchar(20),
> @StartDate Datetime,
> @EndDate Datetime
> AS
> SELECT P.PatientID, A.AttendDate, 'Attendance' AS NarcoProgram, '3' AS
> ProgramId,
> P.LastName + ', ' + P.FirstName as PatientName, p.HomelessShelter,
> p.ClientSchedule
> FROM Attendance.dbo.PATIENTS P RIGHT JOIN Attendance.dbo.ATTENDANCE A ON
> P.PatientID = A.PatientID
> WHERE a.AttendDate >= @StartDate and a.AttendDate <= @EndDate and
> p.PatientId = @PatientId and P.ActiveClient=1 and  P.HomelessShelter > 0
> Union
> SELECT P.PatientID, A.AttendDate, 'Independence' AS NarcoProgram, '4' AS
> ProgramId,
> P.LastName + ', ' + P.FirstName as PatientName, p.HomelessShelter,
> p.ClientSchedule
> FROM Independence.dbo.PATIENTS P RIGHT JOIN Independence.dbo.ATTENDANCE A
> ON
> P.PatientID = A.PatientID
> WHERE  a.AttendDate >= @StartDate and a.AttendDate <= @EndDate  and
> p.PatientId = @PatientId and P.ActiveClient=1 and  P.HomelessShelter > 0
> Union
> SELECT P.PatientID, A.AttendDate, 'Alternitives_RH' AS NarcoProgram, '2'
> AS
> ProgramId,
> P.LastName + ', ' + P.FirstName as PatientName, p.HomelessShelter,
> p.ClientSchedule
> FROM Alternitives_RH.dbo.PATIENTS P RIGHT JOIN
> Alternitives_RH.dbo.ATTENDANCE A ON P.PatientID = A.PatientID
> WHERE  a.AttendDate >= @StartDate and a.AttendDate <= @EndDate  and
> p.PatientId = @PatientId and P.ActiveClient=1 and  P.HomelessShelter > 0
> Union
> SELECT P.PatientID, A.AttendDate, 'Alternatives_BP' AS NarcoProgram, '1'
> AS
> ProgramId,
> P.LastName + ', ' + P.FirstName as PatientName, p.HomelessShelter,
> p.ClientSchedule
> FROM Alternatives_BP.dbo.PATIENTS P RIGHT JOIN
> Alternatives_BP.dbo.ATTENDANCE A ON P.PatientID = A.PatientID
> WHERE  a.AttendDate >= @StartDate and a.AttendDate <= @EndDate  and
> p.PatientId = @PatientId and P.ActiveClient=1 and  P.HomelessShelter > 0
> order by P.PatientID
> GO
>
> What is happening, if I run the stored procedure in Query Analyzer I will
> get say 7 records for a patientid, but when the query (same proc and
> parameters) is run through vb.net and SqlCommand I always get one less
> record
> than I got in Query Analyzer. Does this make any since to you all. THanks
> for
> any suggestions.
> Michael Lee
>
Author
28 Nov 2007 5:18 PM
Terry
This same thing got me the other day.  When you run the query in studio
express the row count at the bottom counts *both* the rows returned in the
results panel as well as the row returned as a 'return value'.  Place you
courser in the query panel and the rows at the bottom says 7, place it in the
results panel and it will say (and show) 6, place it down where the return
value shows at it will say 1.
--
Terry


Show quoteHide quote
"Michael" wrote:

> Hi Everyone,
> I never noticed this before, I hope someone can help here. I have the
> following query:
> CREATE PROCEDURE [dbo].[GetWeeklyAttendance2]
> @PatientId varchar(20),
> @StartDate Datetime,
> @EndDate Datetime
>  AS
> SELECT P.PatientID, A.AttendDate, 'Attendance' AS NarcoProgram, '3' AS
> ProgramId,
> P.LastName + ', ' + P.FirstName as PatientName, p.HomelessShelter,
> p.ClientSchedule
> FROM Attendance.dbo.PATIENTS P RIGHT JOIN Attendance.dbo.ATTENDANCE A ON
> P.PatientID = A.PatientID
> WHERE a.AttendDate >= @StartDate and a.AttendDate <= @EndDate and
> p.PatientId = @PatientId and P.ActiveClient=1 and  P.HomelessShelter > 0
> Union
> SELECT P.PatientID, A.AttendDate, 'Independence' AS NarcoProgram, '4' AS
> ProgramId,
> P.LastName + ', ' + P.FirstName as PatientName, p.HomelessShelter,
> p.ClientSchedule
> FROM Independence.dbo.PATIENTS P RIGHT JOIN Independence.dbo.ATTENDANCE A ON
> P.PatientID = A.PatientID
> WHERE  a.AttendDate >= @StartDate and a.AttendDate <= @EndDate  and
> p.PatientId = @PatientId and P.ActiveClient=1 and  P.HomelessShelter > 0
> Union
> SELECT P.PatientID, A.AttendDate, 'Alternitives_RH' AS NarcoProgram, '2' AS
> ProgramId,
> P.LastName + ', ' + P.FirstName as PatientName, p.HomelessShelter,
> p.ClientSchedule
> FROM Alternitives_RH.dbo.PATIENTS P RIGHT JOIN
> Alternitives_RH.dbo.ATTENDANCE A ON P.PatientID = A.PatientID
> WHERE  a.AttendDate >= @StartDate and a.AttendDate <= @EndDate  and
> p.PatientId = @PatientId and P.ActiveClient=1 and  P.HomelessShelter > 0
> Union
> SELECT P.PatientID, A.AttendDate, 'Alternatives_BP' AS NarcoProgram, '1' AS
> ProgramId,
> P.LastName + ', ' + P.FirstName as PatientName, p.HomelessShelter,
> p.ClientSchedule
> FROM Alternatives_BP.dbo.PATIENTS P RIGHT JOIN
> Alternatives_BP.dbo.ATTENDANCE A ON P.PatientID = A.PatientID
> WHERE  a.AttendDate >= @StartDate and a.AttendDate <= @EndDate  and
> p.PatientId = @PatientId and P.ActiveClient=1 and  P.HomelessShelter > 0
> order by P.PatientID
> GO
>
> What is happening, if I run the stored procedure in Query Analyzer I will
> get say 7 records for a patientid, but when the query (same proc and
> parameters) is run through vb.net and SqlCommand I always get one less record
> than I got in Query Analyzer. Does this make any since to you all. THanks for
> any suggestions.
> Michael Lee
>



Post Other interesting topics
Simple Pulbic Variable
Exclude code of my application
Vb.Net/SQL slowdown
sort two arrays as one?
Copying files with progress bar
How to stop disabled text boxes being greyed out
VB2008 changes?
VB.NET desktop equivalent to asp.net stylesheets
Multi language tooltips in Windows forms
using foxpro table from VB.net 2005 with oledb