Solved

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

Posted on 2012-03-21
10
3,447 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:Carla Romere
[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 101

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:Carla Romere
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:Carla Romere
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:Carla Romere
ID: 37748440
I still get "The ) is missing."
0
 

Author Comment

by:Carla Romere
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:Carla Romere
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 101

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

696 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