Problem in passing selection formula

Problem in passing selection formula

10 Aug 2011 02:08:09
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

public partial class Report_CallDetails : System.Web.UI.Page
{
    private ReportDocument CallDetReport;
    //object for getting type of complaint
    private string cmplt;
    //object for getting status
    private string stat;
    //Local object to store from date
    private string from_date;
    //Local object to store to date
    private string to_date;

    protected void Page_Load(object sender, EventArgs e)
    {

    }

    private void ConfigureCrystalReport()
    {
        CallDetReport = new ReportDocument();
        string reportPath = Server.MapPath("CallDetails.rpt");
        CallDetReport.Load(reportPath);
        string selectionFormula = "{tblLogComplaint.complaint}="
        + "'"
        + cmplt
        + "'"
        + " AND {tblLogComplaint.status}="
        + "'"
        + stat
        + "'"
        + " AND {tblLogComplaint.Date_Time} between"
        + " '"
        + Convert.ToDateTime(from_date).ToString("MM/dd/yyyy")
        + "'"
        + " AND "
        + "'"
        + Convert.ToDateTime(to_date).ToString("MM/dd/yyyy")
        + "'";

        CallDetReport.DataDefinition.RecordSelectionFormula = selectionFormula;
        CrystalReportViewer1.ReportSource = CallDetReport;
    }
    
    protected void btnRptGen_Click(object sender, EventArgs e)
    {
        cmplt = RadCmpTyp.SelectedItem.Text;
        stat = RadStatus.SelectedItem.Text;
        from_date = FromDate.Value;
        to_date = ToDate.Value;
        ConfigureCrystalReport();
    }
}
<form id="form1" runat="server">
        <div>
            Type :<asp:RadioButtonList ID="RadCmpTyp" runat="server">
                <asp:ListItem>All</asp:ListItem>
                <asp:ListItem>Electrical</asp:ListItem>
                <asp:ListItem>Civil</asp:ListItem>
            </asp:RadioButtonList>
            Status :<asp:RadioButtonList ID="RadStatus" runat="server">
                <asp:ListItem>All</asp:ListItem>
                <asp:ListItem>Closed</asp:ListItem>
                <asp:ListItem>Hold</asp:ListItem>
                <asp:ListItem>Pending</asp:ListItem>
            </asp:RadioButtonList>
            <p>
                <asp:Label ID="lblFrmDate" runat="server" Text="From Date:"></asp:Label>
                <input type="text" runat="server" id="FromDate" name="FromDate" onfocus="showCalendarControl(this);"
                    visible="true" /><asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
                        ErrorMessage="*" ControlToValidate="FromDate" ValidationGroup="TextSubmit"></asp:RequiredFieldValidator>&nbsp;&nbsp;&nbsp;&nbsp;
                <asp:Label ID="lblToDate" runat="server" Text="To Date:"></asp:Label>
                <input type="text" runat="server" id="ToDate" name="ToDate" onfocus="showCalendarControl(this);"
                    visible="true" />
                <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ErrorMessage="*"
                    ControlToValidate="ToDate" ValidationGroup="TextSubmit"></asp:RequiredFieldValidator>
                <asp:Button ID="btnRptGen" runat="server" Text="Generate Report" OnClick="btnRptGen_Click" />
        
            </p>
            <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />
        </div>
    </form>
My database table contains list of fields.


Customer Name	Address    complaint   status	Date_Time   	
-------------	-------    ---------   ------	---------
ABC		DC Street  Electrical  pending	07/22/2011
XYZ		New Strt   Civil       Pending  08/28/2011


*Based upon complaint type,status and date of complaint	i've to display records.
*Date is main criteria here for range...

I get error:

                      The remaining text does not appear to be part of the formula. Error in File C:\DOCUME~1\Haji\LOCALS~1\Temp\CallDetails {6F357A3B-876C-4D3E-AEC6-4B73A30CA6EC}.rpt: Error in formula . '{tblLogComplaint.complaint}='Electrical' AND {tblLogComplaint.status}='Pending' AND {tblLogComplaint.Date_Time} between '07/22/2011' AND '07/29/2011'' The remaining text does not appear to be part of the formula.

 

Please Help...

-Ayaz

 

Fresh   Boarder
Posts: 7
From 10-Aug-2011
You're Points: 14
Bookmark
re:Problem in passing selection formula10 Aug 2011 03:08:26

HI,

When you are work with date in your selection formula, you must be careful.becuase date is giving lots of problem.

1. try to cast date from your string

2.or use this way

.SelectionFormula = "{Query.DateofService} =" & "#2000-01-26 00:00:00#"

3.check your date is valid before pass into the report.

thank you

RRaveen
Gold  Boarder
Posts: 156
From 03-Jun-2009
You're Points: 1106
Bookmark
re:Problem in passing selection formula10 Aug 2011 05:08:38

Hi,

Thanks for your reply. I passed the string by casting still it doesn't respond properly. Will you please initialize where i did mistake by correcting the code. I need date range as "between".. So please consider it.

 

Thanks,

-Ayaz

Ayaz
Fresh  Boarder
Posts: 7
From 10-Aug-2011
You're Points: 14
Bookmark
re:Problem in passing selection formula10 Aug 2011 05:08:26

Hi,

Thanks for your reply. I passed the string by casting still it doesn't respond properly. Will you please initialize where i did mistake by correcting the code. I need date range as "between".. So please consider it.

A date-time is required here. Error in File C:\DOCUME~1\Haji\LOCALS~1\Temp\CallDetails {79C14633-D3E1-4358-B4A4-BB81E9981AA3}.rpt: Error in formula . '{tblLogComplaint.complaint}='Electrical' AND {tblLogComplaint.status}='Pending' AND {tblLogComplaint.Date_Time}= '#7/20/2011 12:00:00 AM#' & '#7/29/2011 12:00:00 AM#'' A date-time is required here.

Thanks,

-Ayaz

Ayaz
Fresh  Boarder
Posts: 7
From 10-Aug-2011
You're Points: 14
Bookmark
re:Problem in passing selection formula10 Aug 2011 05:08:51

Hi,

Thanks for your reply. I passed the string by casting still it doesn't respond properly. Will you please initialize where i did mistake by correcting the code. I need date range as "between".. So please consider it.

A date-time is required here. Error in File C:\DOCUME~1\Haji\LOCALS~1\Temp\CallDetails {79C14633-D3E1-4358-B4A4-BB81E9981AA3}.rpt: Error in formula . '{tblLogComplaint.complaint}='Electrical' AND {tblLogComplaint.status}='Pending' AND {tblLogComplaint.Date_Time}= '#7/20/2011 12:00:00 AM#' & '#7/29/2011 12:00:00 AM#'' A date-time is required here.

Thanks,

-Ayaz

Ayaz
Fresh  Boarder
Posts: 7
From 10-Aug-2011
You're Points: 14
Bookmark
re:Problem in passing selection formula10 Aug 2011 23:08:00

Hi,

as per error message, you can see, the date time is the issue

so Just convert it.

 

'{tblLogComplaint.complaint}='Electrical' AND {tblLogComplaint.status}='Pending' AND {tblLogComplaint.Date_Time}= DateTime("7/20/2011 12:00:00") & DateTime("7/29/2011")'

 

thank you

RRaveen
Gold  Boarder
Posts: 156
From 03-Jun-2009
You're Points: 1106
Bookmark
^ Scroll to Top