Solved

Using Dates to Filter Crystal Reports 2010 with VS2010 and c#

Posted on 2012-03-21
10
3,430 Views
Last Modified: 2012-03-21
I have the following code in my submit button to filter the data that is shown in the crystal report 2010/vs 2010 c#.

protected void Button_Submit_Click(object sender, EventArgs e)
    {
          DateTime startdate = Convert.ToDateTime(TextBox_StartDate.Text);
          DateTime enddate = Convert.ToDateTime(TextBox_EndDate.Text);string factory = ddlFactory.SelectedValue;
          string selectFormula = "{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}=" + "'" + factory + "' and " + "{VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} IN [" + "'" + startdate + "' TO " + "'" + enddate + "']";

         crystalReportViewer.SelectionFormula = selectFormula;
         crystalReportViewer.Visible = true;
         crystalReportViewer.DataBind();
    }

Open in new window


This gives me the following error:

A date-time is required here. Details: errorKind
Error in File emptymixers {E2F90D89-36FB-4550-99C2-64409FED7E8B}.rpt: Error in
formula Record_Selection: '{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}='01' and
{VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} IN ['3/20/2012 6:00:00 PM' TO
'3/21/2012 6:00:00 AM']' A date-time is required here. Details: errorKind

The dates look formatted correctly, but asp.net or CR is not recognizing them as dates.
Thanks for any help or direction you can provide.
0
Comment
Question by:Hers2keep
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 37748321
WHy are you putting the ' ' around the datetime?
Crystal takes that to be a string.
Try removing the ' ' in the datetime part

Rather than changing the selection formula have you tried using parameters to pass the values in?

mlmcc
0
 
LVL 19

Expert Comment

by:GJParker
ID: 37748348
crystal needs to see the statdate and enddate as a datetime data type where youar currently using a string.

Try replacing your selectformula  with this

"{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}=" + "'" + factory + "' and " + "{VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} IN [CDATETIME("+  startdate + ") TO CDATETIME(" +  enddate + ")]";
0
 

Author Comment

by:Hers2keep
ID: 37748380
It looks like you are right - I didn't even catch that I was turning it back into a string. I'm trying to avoid using parameters because I have to use these same Crystal Reports in another application that won't recognize the parameters, so I'm filtering the reports instead. I modified my selectFormula to this:

    protected void Button_Submit_Click(object sender, EventArgs e)
    {
        DateTime ds = Convert.ToDateTime(TextBox_StartDate.Text);
        DateTime de = Convert.ToDateTime(TextBox_EndDate.Text); 
        string factory = ddlFactory.SelectedValue;
        string selectFormula = "{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}='" + factory + "' and  {VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} IN [" + ds + " TO " + de + "]";
        crystalReportViewer.SelectionFormula = selectFormula;
        crystalReportViewer.Visible = true;
        crystalReportViewer.DataBind();
    }

Open in new window


Now I'm getting this error:

The ] is missing. Details: errorKind Error in File emptymixers {3B587F13-3E1F-42FD-9C59-95460B31DE2B}.rpt: Error in formula Record_Selection: '{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}='01' and {VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} IN [3/21/2012 6:00:00 AM TO 3/21/2012 12:00:00 PM]' The ] is missing. Details: errorKind

This doesn't make sense to me because it looks like I've got it in there?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Hers2keep
ID: 37748405
GJParker - so I tried your solution and now my code is this:
    protected void Button_Submit_Click(object sender, EventArgs e)
    {
        DateTime ds = Convert.ToDateTime(TextBox_StartDate.Text);
        DateTime de = Convert.ToDateTime(TextBox_EndDate.Text); 
        string factory = ddlFactory.SelectedValue;
        string selectFormula = "{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}='" + factory + "' and  {VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} IN [CDATETIME(" + ds + ") TO CDATETIME(" + de + ")]";
        crystalReportViewer.SelectionFormula = selectFormula;
        crystalReportViewer.Visible = true;
        crystalReportViewer.DataBind();
    }

Open in new window

and I'm getting the same type of error but now it's complaining about the closing paren.
The ) is missing. Details: errorKind Error in File emptymixers {77C9A833-E1C3-4CD8-BBE0-2FD8CA476D52}.rpt: Error in formula Record_Selection: '{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}='01' and {VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} IN [CDATETIME(3/21/2012 6:00:00 AM) TO CDATETIME(3/21/2012 12:00:00 PM)]' The ) is missing. Details: errorKind

This is just weird. In both cases, the closing bracket or paren is there.
0
 
LVL 19

Expert Comment

by:GJParker
ID: 37748424
What happens if you paste thsi directly in to the report ?

{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}='01' and {VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} IN [CDATETIME(3/21/2012 6:00:00 AM) TO CDATETIME(3/21/2012 12:00:00 PM)]
0
 

Author Comment

by:Hers2keep
ID: 37748440
I still get "The ) is missing."
0
 

Author Comment

by:Hers2keep
ID: 37748468
If I just use the Crystal Reports select record formula and pick "between dates" it gives this: {VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} in DateTime (2012, 03, 21, 00, 00, 00) to DateTime (2012, 03, 21, 10, 30, 00) . So I origianlly tried this:
        DateTime ds = Convert.ToDateTime(TextBox_StartDate.Text);
        DateTime de = Convert.ToDateTime(TextBox_EndDate.Text); 
        string factory = ddlFactory.SelectedValue;
        string startdate;
        string enddate;
        startdate = ds.Year + "," + ds.Month + "," + ds.Day + "," + ds.Minute + "," + ds.Second + "," + ds.Millisecond;
        enddate = de.Year + "," + de.Month + "," + de.Day + "," + de.Minute + "," + de.Second + "," + de.Millisecond;
       string selectFormula = "{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}='" + factory + "' and  {VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} IN [" + startdate + " TO " + enddate + "]";

Open in new window

and that gives me this error:
A date-time is required here. Details: errorKind Error in File emptymixers {97C75856-8EA2-4AC8-9E61-0B04B45346BF}.rpt: Error in formula Record_Selection: '{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}='01' and {VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} IN [2012,3,21,0,0,0 TO 2012,3,21,0,0,0]' A date-time is required here. Details: errorKind
and it doesn't pull the minutes, seconds and milliseconds correctly. They all come out as zeroes and they are not really.
0
 
LVL 19

Accepted Solution

by:
GJParker earned 500 total points
ID: 37748517
you shoudl be using Hour, minute and seconds rather than minute, seconds and milliscends and you also still need to tell CR to convert to a datetime, try this


 startdate = ds.Year + "," + ds.Month + "," + ds.Day + "," + ds.hour + "," + ds.minute + "," + ds.Second;
enddate = de.Year + "," + de.Month + "," + de.Day + "," + de.Hour + "," + de.Minute + "," + de.Second;
string selectFormula = "{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}='" + factory + "' and  {VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} IN [CDATETIME(" + startdate + ") TO CDATETIME(" + enddate + ")]";
0
 

Author Closing Comment

by:Hers2keep
ID: 37748545
Absolutely perfect!!!! I swear you can look at something until you're blue in the face and still miss simple things (i.e. didn't have Hour in there at all).

Final working code:
    protected void Button_Submit_Click(object sender, EventArgs e)
    {
        DateTime ds = Convert.ToDateTime(TextBox_StartDate.Text);
        DateTime de = Convert.ToDateTime(TextBox_EndDate.Text); 
        string factory = ddlFactory.SelectedValue;
        string startdate;
        string enddate;
        startdate = ds.Year + "," + ds.Month + "," + ds.Day + "," + ds.Hour + "," + ds.Minute + "," + ds.Second;
        enddate = de.Year + "," + de.Month + "," + de.Day + "," + de.Hour + "," + de.Minute + "," + de.Second;
        string selectFormula = "{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}='" + factory + "' and  {VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} IN [CDATETIME(" + startdate + ") TO CDATETIME(" + enddate + ")]";

        crystalReportViewer.SelectionFormula = selectFormula;
        crystalReportViewer.Visible = true;
        crystalReportViewer.DataBind();
    }

Open in new window

0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37748762
Try


       string selectFormula = "{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}='" + factory + "' and  {VW_MIXER_EMPTY_TIMES_ALL.DATETIME IN DateTime(" + startdate + ") TO DateTime(" + enddate + ")";

mlmcc
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question