Hi,
I have the stored procedure here.This returns the number of old patients from tables.Now I have to pass this count as the parameter to subreport using ReportViewer.But i'm confused whether my method is correct.My Dal file,Business file as follows.
public MySqlDataReader GetPatientCounts(ReportEntity objReport)
{
DBAccess DBA = new DBAccess();
DBA.AddParameter("@PDID", objReport.pkey);
DBA.AddParameter("@PDATE", objReport.firstVisitDate.ToString("yyyy-MM-dd"));
DBA.AddParameter("@UTYPE", objReport.IsNew);
return DBA.ExecuteReader("GetPatientsCount");
}
In Business Layer
public MySqlDataReader GetPatientCounts(ReportEntity objRptBLL)
{
ReportDAL objRptDAL = new ReportDAL();
try
{
return objRptDAL.GetPatientCounts(objRptBLL);
}
catch (Exception EX)
{
throw EX;
}
finally
{
objRptDAL = null;
}
}
My subreport processing in codebehind
public void SetSubDataSource(object sender, SubreportProcessingEventArgs e)
{
DataTable dtbl = new DataTable("DataTable1");
DateTime dt = Convert.ToDateTime(txtFromDate.Text);
DateTime dt1 = Convert.ToDateTime(txtToDate.Text);
int dID = Convert.ToInt16(ddlDoctor.SelectedValue);
ReportBLLogic objRptBLL = new ReportBLLogic();
//dtbl = objRptBLL.GenerateSubReport("GenerateDoctorSubReport");
dtbl = objRptBLL.GenerateSubReportDate(dID, dt, dt1, "GetDoctorListByDate");
e.DataSources.Add(new ReportDataSource("dsReportDate", dtbl));
}
My StoredProcedure Here:
CREATE DEFINER=`root`@`%` PROCEDURE `GetPatientsCount`(IN PDID INT, IN PDATE DATE , IN UTYPE VARCHAR(20))
BEGIN
IF PDID > 0
THEN
SELECT
if(DATE_FORMAT(P.firstVisitDate, '%Y-%m-%d') = PDATE,
'New Patient',
'Old Patient')
AS UserType,
count(
if(
DATE_FORMAT(P.firstVisitDate, '%Y-%m-%d') = PDATE,
'New Patient',
'Old Patient')) as Cnt
FROM commonpersoninfo AS C,
appointment_has_doctor AS AD,
appointment AS A,
symptoms AS S,
personinfo P
WHERE A.pkey = AD.Appointment_pkey
AND C.PKEY = A.PatientInfo_PKey
AND AD.DoctorsInfo_pkey = PDID
AND S.CommonPersonInfo_pkey = C.pkey
AND DATE_FORMAT(a.STARTDATETIME, '%Y-%m-%d') = PDATE
AND P.CommonPersonInfo_pkey = C.pkey
AND if(
DATE_FORMAT(P.firstVisitDate, '%Y-%m-%d') = PDATE,
'New Patient',
'Old Patient') = UTYPE
GROUP BY UserType;
ELSE
SELECT if(DATE_FORMAT(P.firstVisitDate, '%Y-%m-%d') = PDATE,
'New Patient',
'Old Patient')
AS UserType,
count(
if(
DATE_FORMAT(P.firstVisitDate, '%Y-%m-%d') = PDATE,
'New Patient',
'Old Patient')) as Cnt
FROM commonpersoninfo AS C,
appointment_has_doctor AS AD,
appointment AS A,
symptoms AS S,
personinfo P
WHERE A.pkey = AD.Appointment_pkey
AND C.PKEY = A.PatientInfo_PKey
AND S.CommonPersonInfo_pkey = C.pkey
AND DATE_FORMAT(a.STARTDATETIME, '%Y-%m-%d') = PDATE
AND P.CommonPersonInfo_pkey = C.pkey
AND if(
DATE_FORMAT(P.firstVisitDate, '%Y-%m-%d') = PDATE,
'New Patient',
'Old Patient') = UTYPE
GROUP BY UserType;
END IF;
END
But when i debugging,In DAL file the values are not passing from database.Can anyone help me to solve this