CODEWITHSUNDEEP
c#crystal-reportscrystal-reports-2008
Ritzel
Ritzel

Reputation: 33

Crystal Reports.Net Invalid Parameter - String to date

I am using the crsytal reports in VS2008. I want to be able to filter the dates in my crystal report viewer depending on the input of the user through a datetime picker. My problem is the date field itself in my database is in STRING format. -_- Whenever I compare values through code, I parse it to datetime.

How do I fix this? Is there any way I could do a parse of the report field? I don't want/can't change my database field into datetime because that would entail altering my whole system.

~ EDIT ~

Upon clicking the filter button I have a datetime picker named FromCreated_DTime. I parse its value into short date string (ex. 1/01/2011) and then assign it to my string parameter field. Using the Select Expert formula, I applied your code. My parameter field is named actualStart: 

Date (ToNumber (Right ({Projects.Actual_StartDate}, 4)),
  ToNumber (Left ({Projects.Actual_StartDate}, InStr ({Projects.Actual_StartDate}, "/")-1)),
  ToNumber (Mid ({Projects.Actual_StartDate}, 
                 InStr ({Projects.Actual_StartDate}, "/")+1, 
                 InStrRev({Projects.Actual_StartDate},"/")-InStr({Projects.Actual_StartDate}, "/")-1))
 ) 

 >= 

Date (ToNumber (Right ({?actualStart}, 4)),
  ToNumber (Left ({?actualStart}, InStr ({?actualStart}, "/")-1)),
  ToNumber (Mid ({?actualStart}, 
                 InStr ({?actualStart}, "/")+1, 
                 InStrRev({?actualStart},"/")-InStr({?actualStart}, "/")-1))
 )

this is my code upon filter button click

    private void Filter_Btn_Click(object sender, EventArgs e)
    {
        ReportDocument cryRpt = new ReportDocument();
        TableLogOnInfos crtableLogoninfos = new TableLogOnInfos();
        TableLogOnInfo crtableLogoninfo = new TableLogOnInfo();
        ConnectionInfo crConnectionInfo = new ConnectionInfo();

        cryRpt.Load("D:\\MY_THESIS\\WORKING FILES\\WindowsFormsApplication2\\WindowsFormsApplication2\\Reports\\Crystal Reports\\UsersReport.rpt");
        crConnectionInfo.ServerName = "RITZEL-PC\\SQLEXPRESS";
        crConnectionInfo.UserID = "NNIT-Admin";
        crConnectionInfo.Password = "password";
        crConnectionInfo.DatabaseName = "NNIT DB";

        Tables CrTables = cryRpt.Database.Tables;
        foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables)
        {
            crtableLogoninfo = CrTable.LogOnInfo;
            crtableLogoninfo.ConnectionInfo = crConnectionInfo;
            CrTable.ApplyLogOnInfo(crtableLogoninfo);
        }

        // Create parameter objects
        ParameterFields myParams = new ParameterFields();

        //PARAMETER NAME
        ParameterField myParam = new ParameterField();
        ParameterDiscreteValue myDiscreteValue = new ParameterDiscreteValue();
        myParam.ParameterFieldName = "actualStart";

        myDiscreteValue = new ParameterDiscreteValue();
        myDiscreteValue.Value = FromCreated_DTime.Value.ToShortDateString();
        myParam.CurrentValues.Add(myDiscreteValue);
        myParams.Add(myParam);

        crystalReportViewer1.ParameterFieldInfo = myParams;
        crystalReportViewer1.ReportSource = cryRpt;
    }

Upvotes: 0

Views: 2818

Answers (4)

Marc
Marc

Reputation: 778

Example using SQL Expressions in Crystal -- depends upon data source wx the SQL Exp are enabled.

I am going to ASSuME that the field dates are in 10 chr strings, i.e "mm/dd/yyyy" I am going to ASSuME that your parameter is a string. Using Date Picker is more convenient. You can convert DP dates to strings. Using {?actualStart}, {Projects.Actual_StartDate}

@SQLMonth

Left( {?actualStart}, 2 )  > 
   Left( {Projects.Actual_StartDate}, 2 )

@SQLDay

-- You can use SUBSTRING, but I'm being lazy.  
-- Time results for both and then decide

Right( Left( {?actualStart}, 5 ) , 2 )  > 
   Right( Left( {Projects.Actual_StartDate}, 5 ) , 2 )

@SQLYear

Right( {?actualStart}, 4 )  >
   Right( {Projects.Actual_StartDate}, 4 )

Then, in Select Expert, you can include:

(The REST of your selection code)  
AND
( {@SQLYear} AND {@SQLMonth} AND {@SQLDay} )

This would get pushed down to the server and reduce processing time, and return ONLY the records you want. **

** In theory... I haven't tested this, I did it from memory. :-)

Strangely enough, this would also be the code you would use if you used a SQL Command ( = "Crafting the SQL" programmaticly.*)

* OK, I tried several different (mis-)spellings of Programatically, and FF spell check didn't like n'aer a-one.

Upvotes: 0

user359040
user359040

Reputation:

UPDATED: Given a character string called Projects.Actual_StartDate, formatted as /-separated fields in Month-Day-Year order, with a four-digit year and one-or-two-digit day and month values, the following formula should convert the string to a date and compare it with a Crystal date parameter called {?actualStart}:

Date (ToNumber (Right (TrimRight ({Projects.Actual_StartDate}), 4)),
      ToNumber (Left ({Projects.Actual_StartDate}, InStr ({Projects.Actual_StartDate}, "/")-1)),
      ToNumber (Mid ({Projects.Actual_StartDate}, 
                     InStr ({Projects.Actual_StartDate}, "/")+1, 
                     InStrRev({Projects.Actual_StartDate},"/")-InStr({Projects.Actual_StartDate}, "/")-1))
     )
 >= {?actualStart}

Upvotes: 0

Marc
Marc

Reputation: 778

One point... if you are planning on using the date as a selection parameter (what you describe), if you use the method outlined, you will beat your database very unkindly. Not much of a problem in small databases (fewer records, smaller sized, etc.), but in a bigger database, you will run into problems. The method described basically PULLS BACK EVERY (qualifying) RECORD, DOES THE CONVERSION, THEN DECIDES WHETHER IT SHOULD BE KEPT OR DISCARDED. Those first 4(5) words should strike terror in your soul. It's still a good method for DISPLAYING dates, if needed, but not for using string dates for record selection.

What you CAN (should) do is to create date parameters, then convert those into a suitable string expression, and use that to search the database. You set the parameters once per report run. You would have brought back every record, converted, compared, discarded, etc. It makes my brain thrash just typing it. :-D

Hope that helped and please don't read any sarcasm in to anything, nothing but good will is intended.

Upvotes: 0

findcaiyzh
findcaiyzh

Reputation: 647

Depends on the format of your date field in database . here is an example:

stringvar yyear; 
stringvar mmonth; 
stringvar dday; 

dday := {ORDERS.ORDER_DATE}[5 to 6]; 
mmonth := {ORDERS.ORDER_DATE}[3 to 4]; 
yyear := {ORDERS.ORDER_DATE}[1 to 2]; 

if yyear < "50" then 
    date(tonumber(yyear)+2000,tonumber(mmonth),tonumber(dday)) 
else 
    date(tonumber(yyear)+1900,tonumber(mmonth),tonumber(dday))

Above code comes from: Convert date from string to date format

Upvotes: 1

Related Questions