STORED PROCEDURE FOR GET RECORDS BASED ON TWO DATES:
EX:
USE [140912FINALISERVICE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SpDispatchDateReport]
(
@FromDate datetime=null,
@ToDate datetime=null,
@Mode varchar(50)=null
)
as
begin
IF(@Mode ='DATEREPORT')
BEGIN
select i.InSlNo as[Inward SL.No],t.BillNo as[Bill No],t.Name as[Customer Name],t.Description,t.Status,t.Price,t.DispatchType,t.DispatchDate from TblDispatchItemDetails t
inner join TblInward i on i.Id =t.InwardSerialNumber where t.DispatchDate BETWEEN @FromDate AND @ToDate
END
end<code></code>
PASS PARAMETER IN CRYSTAL REPORT VIEWER LOAD EVENT
private void crystalReportViewer1_Load(object sender, EventArgs e)
{
try
{
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-GB");
LoadXMLDetails();
ReportDocument rpt = new ReportDocument();
ParameterFields pFields = new ParameterFields();
ParameterField pField1 = new ParameterField();
ParameterDiscreteValue pValue1 = new ParameterDiscreteValue();
pField1.Name = "@Mode";
pValue1.Value = "DATEREPORT";
pField1.CurrentValues.Add(pValue1);
pFields.Add(pField1);
ParameterField pField2 = new ParameterField();
ParameterDiscreteValue pValue2 = new ParameterDiscreteValue();
pField2.Name = "@FromDate";
pValue2.Value = FromDt;
pField2.CurrentValues.Add(pValue2);
pFields.Add(pField2);
ParameterField pField3 = new ParameterField();
ParameterDiscreteValue pValue3 = new ParameterDiscreteValue();
pField3.Name = "@ToDate";
pValue3.Value = ToDt;
pField3.CurrentValues.Add(pValue3);
pFields.Add(pField3);
ParameterField pField4 = new ParameterField();
ParameterDiscreteValue pValue4 = new ParameterDiscreteValue();
pField4.Name = "sent";
pValue4.Value = Sent;
pField4.CurrentValues.Add(pValue4);
pFields.Add(pField4);
ParameterField pField5 = new ParameterField();
ParameterDiscreteValue pValue5 = new ParameterDiscreteValue();
pField5.Name = "unsent";
pValue5.Value = UnSent;
pField5.CurrentValues.Add(pValue5);
pFields.Add(pField5);
ParameterField pField6 = new ParameterField();
ParameterDiscreteValue pValue6 = new ParameterDiscreteValue();
pField6.Name = "serviced";
pValue6.Value = Ser;
pField6.CurrentValues.Add(pValue6);
pFields.Add(pField6);
ParameterField pField8 = new ParameterField();
ParameterDiscreteValue pValue8 = new ParameterDiscreteValue();
pField8.Name = "beyondserviced";
pValue8.Value = Be_Ser;
pField8.CurrentValues.Add(pValue8);
pFields.Add(pField8);
ParameterField pField9 = new ParameterField();
ParameterDiscreteValue pValue9 = new ParameterDiscreteValue();
pField9.Name = "return";
pValue9.Value = Ret;
pField9.CurrentValues.Add(pValue9);
pFields.Add(pField9);
ParameterField pField10 = new ParameterField();
ParameterDiscreteValue pValue10 = new ParameterDiscreteValue();
pField10.Name = "waiting";
pValue10.Value = Cust_App;
pField10.CurrentValues.Add(pValue10);
pFields.Add(pField10);
ParameterField pField11 = new ParameterField();
ParameterDiscreteValue pValue11 = new ParameterDiscreteValue();
pField11.Name = "progressed";
pValue11.Value = Pro;
pField11.CurrentValues.Add(pValue11);
pFields.Add(pField11);
crystalReportViewer1.ParameterFieldInfo = pFields;
string filename = Application.StartupPath + "\\REPORTS\\DispatchDateReport.rpt";
rpt.Load(filename);
CrystalDecisions.Shared.TableLogOnInfo crLogonInfo = rpt.Database.Tables[0].LogOnInfo;
string fileName = "ServerInfo.xml";
string filePath = Application.StartupPath + "\\" + fileName;
if (File.Exists(filePath))
{
XmlDocument doc = new XmlDocument();
doc.Load(filePath);
XmlNodeList xnList = doc.SelectNodes("ServerInformation");
foreach (XmlNode xn in xnList)
{
if (xn["authentication"].InnerText == "false")
{
crLogonInfo.ConnectionInfo.IntegratedSecurity = true;
crLogonInfo.ConnectionInfo.ServerName = xn["Sever"].InnerText;
crLogonInfo.ConnectionInfo.DatabaseName = xn["Database"].InnerText;
crLogonInfo.ConnectionInfo.UserID = string.Empty;
crLogonInfo.ConnectionInfo.Password = string.Empty;
rpt.Database.Tables[0].ApplyLogOnInfo(crLogonInfo);
rpt.SetDatabaseLogon("", "", crLogonInfo.ConnectionInfo.ServerName, crLogonInfo.ConnectionInfo.DatabaseName);
}
else if (xn["authentication"].InnerText == "true")
{
crLogonInfo.ConnectionInfo.IntegratedSecurity = false;
crLogonInfo.ConnectionInfo.ServerName = xn["Sever"].InnerText;
crLogonInfo.ConnectionInfo.DatabaseName = xn["Database"].InnerText;
crLogonInfo.ConnectionInfo.UserID = xn["user"].InnerText;
crLogonInfo.ConnectionInfo.Password = xn["pwd"].InnerText;
rpt.Database.Tables[0].ApplyLogOnInfo(crLogonInfo);
rpt.SetDatabaseLogon(crLogonInfo.ConnectionInfo.UserID, crLogonInfo.ConnectionInfo.Password, crLogonInfo.ConnectionInfo.ServerName, crLogonInfo.ConnectionInfo.DatabaseName);
}
crystalReportViewer1.ReportSource = rpt;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, MessageBoxTile, MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
}
}<pre lang="text">
HERE I WAS USED XmlDocument FOR REASON IS SQL SERVER DATABASE CONNECTION
I THINK THIS IS USED FOR YOU ELSE SORRY