Kavi suja

Kavi suja

  • NA
  • 298
  • 171.1k

Passing Parameter to SubReport using ReportViewer

Jun 14 2013 1:56 AM
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