locked
How to pass a query to CrystalReportViewer1.SelectionFormula RRS feed

  • 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

    Friday, May 2, 2014 3:09 AM

Answers

  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 5, 2014 12:20 AM

All replies

  • User-1353043493 posted

    use like 

    sParam = " {qryGLTRX.TrxDate} in Date(" + Convert.ToDateTime(sTGL1) + ") to Date(" + Convert.ToDateTime(sTGL2) +") ";
    Friday, May 2, 2014 10:06 AM
  • 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.


    Sunday, May 4, 2014 11:04 PM
  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 5, 2014 12:20 AM