How to allow apostrophes in string parameters

I have a few reports that contain a customer name parameter. I have
just realized that when a customer name contains a single-quote
(apostrophe), reporting services thinks that it's an unclosed string
and I receive an error.
The error I get -
"An error has occurred during report processing.
Query execution failed for data set '....'.
Line 1: Incorrect syntax near 's'.
Unclosed quotation mark before the character string"."
I know that I probably need to 'escape' the single quote, but I am
unclear if I can do this on the data tab in report designer or if I
have to do this through my views/stored procs in sql server. After
determining where to make the change, I will also need to know what
the correct syntax is.
Thanks so much for your help.
CrystalOn Feb 14, 11:59=A0am, Just Another Reporter <Crystal.War...@.gmail.com>
wrote:
> I have a few reports that contain a customer name parameter. =A0I have
> just realized that when a customer name contains a single-quote
> (apostrophe), reporting services thinks that it's an unclosed string
> and I receive an error.
> The error I get -
> "An error has occurred during report processing.
> Query execution failed for data set '....'.
> Line 1: Incorrect syntax near 's'.
> Unclosed quotation mark before the character string"."
> I know that I probably need to 'escape' the single quote, but I am
> unclear if I can do this on the data tab in report designer or if I
> have to do this through my views/stored procs in sql server. =A0After
> determining where to make the change, I will also need to know what
> the correct syntax is.
> Thanks so much for your help.
> Crystal
Go to the Data tab, and click the Properties button [...] for the
Dataset. Under the Parameters tab, you will see mappings between the
parameters used in the dataset, mapped to the Report Parameters. Note
that these are expressions, so you can put your own code in here to
"escape" the single quote.
What you will want to do is do is something like:
@.Pararm =3DReplace( Parameters!Param.Value, "'", "''" )
-- Scott