We have a VS2010 Crystal Report set up with Advantage OLE DB (ADO) as the database connection method. The Datapath of the connection is then set programmatically at runtime. We are currently running v10.10 of the client components again 10.10 and 11.10 databases.

We set the RecordSelectionFormula to a string built using our own query builder. This all works as long as the filter does not contain any date criteria. Adding date filters causes invalid SQL to be passed to the database.

e.g. filter="(({WHAUDIT.DATE} >= CDate(#2012-12-01#) AND {WHAUDIT.DATE} <= CDate(#2013-01-31#)))"

where WHAUDIT.DATE is a DATE type.

produces the following error:

Failed to retrieve data from the database.
Details: ADO Error Code: 0x80040e14
Source: Advantage OLE DB Provider
Description: Error 7200:  AQE Error:  State = S0000;   NativeError = 2159;  [iAnywhere Solutions][Advantage SQL Engine]Invalid argument to scalar function: CONVERT -- Location of error in the SQL statement is: 304    SELECT "WHAUDIT"."TIME", "WHAUDIT"."USER", "WHAUDIT"."TYPE", "WHAUDIT"."PRODUCT", "WHAUDIT"."CODE", "WHAUDIT"."FROM", "WHAUDIT"."TO", "WHAUDIT"."GRNNO", "WHAUDIT"."BATCH", "WHAUDIT"."PALLETID", "WHAUDIT"."PO", "WHAUDIT"."DATE", "WHAUDIT"."QUANTITY" FROM   "WHAUDIT" "WHAUDIT" WHERE  ("WHAUDIT"."DATE">=CONVERT(DATETIME, '12-01-2012', 110) AND "WHAUDIT"."DATE"<=CONVERT(DATETIME, '01-31-2013', 110))
Native Error: 7200 [Database Vendor Code: 7200 ]

It appears that Crystal is correctly identifying the type of {WHAUDIT.DATE} as a Date, as it complains if we use CDateTime(). But when it is translated into SQL it appears to erroneously believes that the field is a DATETIME and uses the CONVERT() scalar function on the constant. It uses the SQL Server syntax which is invalid for Advantage.

Exactly the same report and filter work fine if the database connection uses the Advantage StreamlineSQL ODBC driver, however we would prefer to use the the more flexible OLE DB if we can.

The rest of the application uses the Entity Model and .NET Data Provider.

Is this a problem with the Advantage OLE DB Provider or Crystal Reports?

Why does ODBC work but OLE DB does not?

Is there any way we can structure the RecordSelectionFormula, or set any other options to avoid this problem?

Thank you in advance for any suggestions.

Alban

asked 03 Jan '13, 08:37

Alban's gravatar image

Alban
1214613
accept rate: 100%

The Convert() scalar function in Advantage SQL takes 2 parameters, instead of 3. I do not know much about Crystal but it seems that maybe the CDate() tells Crystal Report to insert the Conver() scalar when using the OLE DB driver.

(03 Jan '13, 08:55) Alex Wong

Also the order of the arguments is different. The point is that the ODBC driver does not have this problem.

(03 Jan '13, 08:56) Alban

Since the problem does not occur when using the ODBC driver, it then seems the CR is generating different SQL statements depending on the driver. I do not know what causes the change in behavior. The ODBC and OLE DB drivers are passive that they just pass the SQL statement to the server.

(03 Jan '13, 11:28) Alex Wong

There was a reason originally to use the CDate() function, but I cannot remember what it was. I suspect it may have been when I was using a string representation of the date rather than a date literal.

Following Alex Wong's suggestion that it could be the CDate() method that was messing it up I took that out and it does now appear to work.

i.e.

filter="(({WHAUDIT.DATE} >= #2012-12-01# AND {WHAUDIT.DATE} <= #2013-01-31#))"

Thanks.

link

answered 03 Jan '13, 09:06

Alban's gravatar image

Alban
1214613
accept rate: 100%

edited 03 Jan '13, 09:38

Mark%20Wilkins's gravatar image

Mark Wilkins
7.2k226133

Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×325
×16
×8

Asked: 03 Jan '13, 08:37

Seen: 3,928 times

Last updated: 03 Jan '13, 11:28

Advantage Developer Zone Contact Us Privacy Policy Copyright Info


Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.