how to display record on crystal report from between two dates using store procedure...
i have used below store procedure to find record from between date.
now issue is when i pass date into textboxes the report is load but when i press next or previous button of crystal report the all records are disappear from the crystal report and between dates are not display in crystal report also... i have pass parameters on crystal report design but not display date. and also disappear from crystal report while exporting record....
CREATE PROCEDURE MonthlyReport
(@FromDate NVARCHAR(10), @ToDate NVARCHAR(10))
AS
BEGIN
SELECT E_ID, E_NAMEOFEMP, E_ADDRESS, E_PRESENTDATE, E_INDATETIME, E_OUTDATETIME,E_PHONE FROM EMPLOYEE_MASTER
WHERE CONVERT(DATETIME,E_PRESENTDATE,103) BETWEEN CONVERT(DATETIME,@FromDate,103) AND CONVERT(DATETIME,@ToDate,103)
END
and i have used below code for load crystal report.
SqlConnection con = new SqlConnection(sqlCon);
SqlCommand cmd = new SqlCommand("MonthlyReport", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FromDate", txtstartdate.Text);
cmd.Parameters.AddWithValue("@ToDate", txtenddate.Text);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
ReportDocument crpt = new ReportDocument();
monthlyrpt.Visible = true;
crpt.Load(Server.MapPath("~/Report/MothlyReport.rpt"));
crpt.SetDataSource(dt);
monthlyrpt.ReportSource = crpt;
((TextObject)crpt.ReportDefinition.Sections["Section1"].ReportObjects["txtFromdate"]).Text = txtstartdate.Text;
((TextObject)crpt.ReportDefinition.Sections["Section1"].ReportObjects["txtTodate"]).Text = txtenddate.Text;
}
else
{
monthlyrpt.Visible = false;
lblmessage.Visible = true;
lblmessage.Text = "There is no records in the date you selected.";
lblmessage.ForeColor = System.Drawing.Color.Red;
}