1897501 - Command Object with Parameter that returns data with a single quote or apostrophe in the value generates a SQL Syntax error

SAP Knowledge Base Article - Public

1897501 - Command Object with Parameter that returns data with a single quote or apostrophe in the value generates a SQL Syntax error

Symptom

Command Object with Parameter that returns data with a single quote or apostrophe in the value generates a SQL Syntax error

Environment

Crystal Reports 2008

Crystal Reports 2008 for Business One

Crystal Reports 2011 ( 4.0 )

Crystal Reports 2013 ( 4.1 )

Reproducing the Issue

Example SQL for command table:

SELECT "CUSTOMER"."CUSTOMERNAME"
FROM   "XTREME"."CUSTOMER" "CUSTOMER"
where "CUSTOMER"."CUSTOMERNAME"={?name}

Cause

Our code should escape the single quote for command table -- changing ' to '' such as 'Spokes 'N Wheels Ltd. ' to 'Spokes ''N Wheels Ltd. '

Resolution

A possible workaround is to use allow multiple values parameter, works for CR 2008 Service Pack 6. Does not work for CR 2008 SP 4 for Business One

  • Edit the Command and Parameter and check “Allows multiple Values” while creating parameter in command table.

This issue has been tracked with ADAPT01700722 and should be in the following patches:

  • CR 2008 - Fix Pack 6.3
  • CR 2011 ( 4.0 ) - Fix Pack 5.13, 7.2 and Service Pack 8
  • CR 2013 ( 4.1 ) - Patch 1.2
  • CR 2013 for Business One - Support Pack 2

Keywords

KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Problem

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013