Need to replace quotes on command parameter

[kmcmanus]

I have an CR 10 report with a command hitting a SQL server 2000 database.

Command has a parameter that prompts the user for an activity name

The value can come in with single quotes. i.e. for parameter called ACITIVTY the value would be : Jim's game

Since Command basically posts the "value" in the SQL ( aka "not by reference") I have no chance to run replace on it before the sql run's so it error's on having single quotes in the value.

I originally had the Parameter in the where clause. Have tried to capture it using a variable but to no avail.

Help is greatly appreciated

DECLARE @MyCharVar CHAR(100)
SET @MyCharVar = Replace('{?Activity}','''','''''')

SELECT
tablename.column1
,tablename column2
...
FROM dbo.tablename
...
WHERE dbo.tablename.column1 = @MyCharVar

Kevin McManus
kevin@mcmanusconsulting.com

 

[vidru]

I can't think of a workaround using the Command, other than instructing the users to use two single quotes instead of one.

Only other option would be to turn the Command into a stored procedure, or abandon the Command altogether, using the tables/views and a non-Command CR parameter.

-dave

 

[lbass]

If you know the quote does not appear before the nth character in the field, and that the results would still be unique, you could use the left() function on both the field and the parameter within the command. A long shot, but might work.

-LB

 

[kmcmanus]

Solution was to handle on the front end before it gets to Crystal. Used the T-SQL replace function in the creation of the parameter list so user seees one qupte but double quote value is sent to the report.