Carla Romere
asked on
Using Dates to Filter Crystal Reports 2010 with VS2010 and c#
I have the following code in my submit button to filter the data that is shown in the crystal report 2010/vs 2010 c#.
This gives me the following error:
A date-time is required here. Details: errorKind
Error in File emptymixers {E2F90D89-36FB-4550-99C2-6 4409FED7E8 B}.rpt: Error in
formula Record_Selection: '{VW_MIXER_EMPTY_TIMES_ALL .FACTORY}= '01' and
{VW_MIXER_EMPTY_TIMES_ALL. DATETIME_D UMP} 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.
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();
}
This gives me the following error:
A date-time is required here. Details: errorKind
Error in File emptymixers {E2F90D89-36FB-4550-99C2-6
formula Record_Selection: '{VW_MIXER_EMPTY_TIMES_ALL
{VW_MIXER_EMPTY_TIMES_ALL.
'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.
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 + ")]";
Try replacing your selectformula with this
"{VW_MIXER_EMPTY_TIMES_ALL
ASKER
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:
Now I'm getting this error:
The ] is missing. Details: errorKind Error in File emptymixers {3B587F13-3E1F-42FD-9C59-9 5460B31DE2 B}.rpt: Error in formula Record_Selection: '{VW_MIXER_EMPTY_TIMES_ALL .FACTORY}= '01' and {VW_MIXER_EMPTY_TIMES_ALL. DATETIME_D UMP} 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?
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();
}
Now I'm getting this error:
The ] is missing. Details: errorKind Error in File emptymixers {3B587F13-3E1F-42FD-9C59-9
This doesn't make sense to me because it looks like I've got it in there?
ASKER
GJParker - so I tried your solution and now my code is this:
The ) is missing. Details: errorKind Error in File emptymixers {77C9A833-E1C3-4CD8-BBE0-2 FD8CA476D5 2}.rpt: Error in formula Record_Selection: '{VW_MIXER_EMPTY_TIMES_ALL .FACTORY}= '01' and {VW_MIXER_EMPTY_TIMES_ALL. DATETIME_D UMP} 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.
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();
}
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-2
This is just weird. In both cases, the closing bracket or paren is there.
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_D UMP} IN [CDATETIME(3/21/2012 6:00:00 AM) TO CDATETIME(3/21/2012 12:00:00 PM)]
{VW_MIXER_EMPTY_TIMES_ALL.
ASKER
I still get "The ) is missing."
ASKER
If I just use the Crystal Reports select record formula and pick "between dates" it gives this: {VW_MIXER_EMPTY_TIMES_ALL. DATETIME_D UMP} in DateTime (2012, 03, 21, 00, 00, 00) to DateTime (2012, 03, 21, 10, 30, 00) . So I origianlly tried this:
A date-time is required here. Details: errorKind Error in File emptymixers {97C75856-8EA2-4AC8-9E61-0 B04B45346B F}.rpt: Error in formula Record_Selection: '{VW_MIXER_EMPTY_TIMES_ALL .FACTORY}= '01' and {VW_MIXER_EMPTY_TIMES_ALL. DATETIME_D UMP} 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.
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 + "]";
and that gives me this error:A date-time is required here. Details: errorKind Error in File emptymixers {97C75856-8EA2-4AC8-9E61-0
and it doesn't pull the minutes, seconds and milliseconds correctly. They all come out as zeroes and they are not really.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:
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();
}
Try
string selectFormula = "{VW_MIXER_EMPTY_TIMES_ALL .FACTORY}= '" + factory + "' and {VW_MIXER_EMPTY_TIMES_ALL. DATETIME IN DateTime(" + startdate + ") TO DateTime(" + enddate + ")";
mlmcc
string selectFormula = "{VW_MIXER_EMPTY_TIMES_ALL
mlmcc
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