How to pass a query to CrystalReportViewer1.SelectionFormula

Question

User-1330485181 posted

Dear All,

I have a problem to pass a query to  CrystalReportViewer.SelectionFormula.  Here is my query :           

             sParam = " {qryGLTRX.TrxDate} >= \'" + Convert.ToDateTime(sTGL1) + "\'";
             sParam = sParam + " and {qryGLTRX.TrxDate} <= " + "\'" + Convert.ToDateTime(sTGL2) + "\'";
             sParam = sParam + " and {qryGLTRX.Trxactno} >= " + "\'" + sCOA1 + "\'";
             sParam = sParam + " and {qryGLTRX.Trxactno} <= " + "\'" + sCOA2 + "\'";

I want to pass that query to CrystalReportViewer.SelectionFormula = sParam

CrystalReportViewer1.ReportSource = rptDoc;
CrystalReportViewer.SelectionFormula = sParam;
rptDoc.Refresh();

Is it possible to do this ?

I always get an error like this below

FormulaException was unhandled by user code

A date-time is required here.
Details: errorKind
Error in File ledger_card {553AD865-BB07-4ED3-AD49-8D36EEC3B4A5}.rpt:
Error in formula  Record_Selection: 
' {qryGLTRX.TrxDate} >= '4/1/2014 12:00:00 AM' and {qryGLTRX.TrxDate} <= '5/2/2014 12:00:00 AM' and {qryGLTRX.Trxactno} >= '20000000' and {qryGLTRX.Trxactno} <= '30000000''
A date-time is required here.
Details: errorKind

Does anyone know how to solve this problem ?

Regards,

Sentoso

Answer 1

User-1353043493 posted

use like 

sParam = " {qryGLTRX.TrxDate} in Date(" + Convert.ToDateTime(sTGL1) + ") to Date(" + Convert.ToDateTime(sTGL2) +") ";

Answer 2

User-1330485181 posted

use like 

sParam = " {qryGLTRX.TrxDate} in Date(" + Convert.ToDateTime(sTGL1) + ") to Date(" + Convert.ToDateTime(sTGL2) +") ";

Thank Dharmesh for the answer.  I have tried your code but It still generate an error like this below

Server Error in '/WebTckData' Application.
The ) is missing.
Details: errorKindError in File ledger_card {6F4AA53A-A0AD-4FD2-8D1A-C22FF113C5BE}.rpt:
Error in formula  Record_Selection:
' {qryGLTRX.TrxDate} in Date(4/1/2014 12:00:00 AM) to Date(5/5/2014 12:00:00 AM) '
The ) is missing.
Details: errorKind
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Runtime.InteropServices.COMException: The ) is missing.
Details: errorKindError in File ledger_card {6F4AA53A-A0AD-4FD2-8D1A-C22FF113C5BE}.rpt:
Error in formula  Record_Selection:
' {qryGLTRX.TrxDate} in Date(4/1/2014 12:00:00 AM) to Date(5/5/2014 12:00:00 AM)) '
The ) is missing.
Details: errorKind

But If I change the code into

sParam =  " {qryGLTRX.TrxDate} in Date(" + Convert.ToDateTime(sTGL1).ToShortDateString() + ") to Date(" + Convert.ToDateTime(sTGL2).ToShortDateString() + ")";

It produces no error but there is no data to show.  There should be a data to show since I query using SQL server there is a data on that range of date.

Answer 3

User-1330485181 posted

Finally, I found the answer :

1. Use Calendar component, don't use TextBox for Date Field.

2. Type sParam like this below :

sParam = " {qryGLTRX.TrxDate} IN DateValue (" + Calendar1.SelectedDate.ToString("yyyy,MM,dd") + " ) TO DateValue (" + Calendar2.SelectedDate.ToString("yyyy,MM,dd") + " )";

CrystalReportViewer.SelectionFormula = sParam;

It works very well.