Ok - I see the issue from your script.
You are calling several "Select" statements, each returning a single row.
in C# this will probably look like a dataset with many tables. each table will have 1 row. Add this dataset as a datasource and the gridview will only show data from 1 table.
To fix this, you need to build the result and return it as a single select. I suggest creating a table variable and populating that. I have re-written the script below but please test and correct it!
ALTER Proc [dbo].[EmpShiftCal]
As
Begin
Declare @ShiftName varchar(50),
@CheckTime time,
@WorkDate date,
@StartDate date,
@EndDate date,
@EmpID int,
@CheckType char(8),
@StartTime Time,
@AttDate datetime,
@TimeNow time,
@TimePlus Datetime,
@DateConvert datetime,
@date1 date,@date2 date
Declare @ResultTable table
(
EmpId int,
Name varchar(50),
StartDate date,
EndDate date,
WorkDate date,
CheckTime time,
CheckType char(8),
TimeNow time,
DateAttendance datetime,
LateInMinutes int,
WorkHours decimal(16,2),
OnlyDate datetime,
OnlyDatePlus datetime
)
Set @date1 = dateadd(month, -1, GETDATE());
Set @date2 = GetDate()
DECLARE EmpShift CURSOR FOR
Select EmpShift.EmpID,
EmpShift.ShiftName,
EmpShift.StartDate,
EmpShift.EndDate,
Convert(varchar(10), CHECKINOUT.CheckTime, 121) As WorkDate,
convert(char(5), CHECKINOUT.CheckTime, 108) As CheckTime,
CHECKINOUT.CheckType,
CHECKINOUT.CheckTime
From EmpShift
Inner Join CHECKINOUT On EmpShift.EmpID=CHECKINOUT.USERID
Where ((EmpShift.StartDate Between @date1 AND @date2)
AND (EmpShift.EndDate Between @date1 AND @date2))
And EmpShift.EmpID=191
AND (CHECKINOUT.CheckTime Between @Date1 AND @Date2)
GROUP BY EmpShift.EmpID, Convert(varchar(10),
CHECKINOUT.CheckTime, 121),convert(char(5), CHECKINOUT.CheckTime, 108),
EmpShift.EmpID,
EmpShift.ShiftName,
EmpShift.StartDate,
EmpShift.EndDate,
CHECKINOUT.CheckType,
CHECKINOUT.CheckTime
order by StartDate
Open EmpShift
FETCH NEXT FROM EmpShift INTO @EmpID,@ShiftName,@StartDate,@EndDate,@WorkDate,@CheckTime,@CheckType,@DateConvert
While(@@FETCH_STATUS=0)
BEGIN
Select @StartTime=StartTime From ShiftTable Where ShiftName=@ShiftName
if(@ShiftName!='' and @CheckType='I')
Begin
Set @TimePlus=DATEADD(DAY, 1, @WorkDate)
Set @AttDate=datediff(dd,@WorkDate,@WorkDate)
Set @TimeNow=(Select MAX(CHECKINOUT.CheckTime) From CHECKINOUT Where CheckTime Between @AttDate AND @TimePlus AND CHECKTYPE='O' AND USERID=191)
insert into @ResultTable(
EmpId ,
Name ,
StartDate,
EndDate ,
WorkDate,
CheckTime ,
CheckType ,
TimeNow ,
DateAttendance ,
LateInMinutes ,
WorkHours ,
OnlyDate ,
OnlyDatePlus )
VALUES (@EmpID,
@ShiftName,
@StartDate,
@EndDate,
@WorkDate,
@CheckTime,
@CheckType,
@TimeNow,
@AttDate,
DATEDIFF(N,@CheckTime,@StartTime),
DATEDIFF(N,@CheckTime,@TimeNow)/60.00,
@DateConvert,
@TimePlus )
End
FETCH NEXT FROM EmpShift INTO @EmpID,@ShiftName,@StartDate,@EndDate,@WorkDate,@CheckTime,@CheckType,@DateConvert
End
Close EmpShift
Deallocate EmpShift
select * from @ResultTable
End