parentheses and an SQL insert

[timmoser]

I'm using CF 4.5. Can't figure out why this would happen. I have an SP with an insert statement. Everything works fine except for parentheses and double quotes. This happened in more then one SP and/or page. It's an intranet site so I was able to make a rule that parenthesis and double quotes are not allowed in free text areas. Well now; I have run into a situation where I have to allow them.

So has anyone ever run into a situation where parentheses and double quotes would cause an error in the insert?

This is the error.......................

ODBC Error Code = S1000 (General error)
[MERANT][ODBC Sybase driver][SQL Server]Unclosed quote before the character string 'false'.
SQL = "{call s_it_tt_update '23061 ', '2002080074P', 'IT Work', 'All work associated with Imations/DSS (cquisition', '04/20/2003', 0.0, 1, 0.0, 0.0, 0.0, 0.0, 0.0, 0, 'INSERT', 'false'}"



This is part of the CF code..................


<CFSET varSrProj=FORM[&quot;hdnSrProj_#xcnt#&quot;]>
<CFSET varProjWeek=FORM[&quot;totProjWeek_#xcnt#&quot;]>
<CFSET varSun=FORM[&quot;txtSun_#xcnt#&quot;]>
<CFSET varMon=FORM[&quot;txtMon_#xcnt#&quot;]>
<CFSET varTue=FORM[&quot;txtTue_#xcnt#&quot;]>
<CFSET varWed=FORM[&quot;txtWed_#xcnt#&quot;]>
<CFSET varThr=FORM[&quot;txtThr_#xcnt#&quot;]>
<CFSET varFri=FORM[&quot;txtFri_#xcnt#&quot;]>
<CFSET varSat=FORM[&quot;txtSat_#xcnt#&quot;]>
<CFIF #Variables.varSrProj# GT &quot;&quot;
AND #Variables.varProjWeek# GT 0>
<cfset variables.addedtotemplate = 'true'>
<CFSET varType=FORM[&quot;txtType_#xcnt#&quot;]>
<CFSET varDesc=FORM[&quot;hdndesc_#xcnt#&quot;]>
<cfquery datasource=&quot;gvdb&quot; name=&quot;saveChanges&quot;>
{call s_it_tt_update '#variables.locUid#', '#Variables.varSrProj#', '#Variables.varType#',
'#Variables.varDesc#', '#FORM.xxtend#',
#variables.varSun#, #variables.varMon#,
#variables.varTue#, #variables.varWed#,
#variables.varThr#, #variables.varFri#,
#variables.varSat#, 0, 'INSERT', <CFIF isdefined('FORM.chkSaveTemplate')>'true'<CFELSE>'false'</CFIF>}
</cfquery>


This is the SP..........................................


CREATE PROCEDURE dbo.s_it_tt_update
@uid CHAR(10),
@activity CHAR(30),
@acttype CHAR(25),
@description CHAR(50),
@weekdate smalldatetime,
@sunday numeric(9,2),
@monday numeric(9,2),
@tuesday numeric(9,2),
@wednesday numeric(9,2),
@thursday numeric(9,2),
@friday numeric(9,2),
@saturday numeric(9,2),
@rowid numeric(10,0),
@ioru CHAR(10),
@updateTemplate CHAR(5)
AS
BEGIN

IF @ioru = &quot;UPDATE&quot;
BEGIN
UPDATE it_tt_activity
SET acttype=@acttype, activity=@activity, description=@description,
entry_date=GETDATE(), week_date=@weekdate, sunday=@sunday, monday=@monday,
tuesday=@tuesday, wednesday=@wednesday, thursday=@thursday, friday=@friday,
saturday=@saturday
WHERE rowid=@rowid
END

IF @ioru = &quot;INSERT&quot;
BEGIN
-- Insert the record
INSERT INTO it_tt_activity(uid, acttype, activity, description, entry_date,
week_date, sunday, monday, tuesday, wednesday, thursday,
friday, saturday)
VALUES (@uid, @acttype, @activity, @description, GETDATE(),
@weekdate, @sunday, @monday, @tuesday, @wednesday, @thursday,@friday, @saturday)
END

IF @updateTemplate='true'
BEGIN
-- Insert the record
INSERT INTO it_tt_acttemplate(uid, acttype, activity, description, entry_date, week_date, sunday, monday, tuesday, wednesday, thursday, friday, saturday)
VALUES (@uid, @acttype, @activity, @description, GETDATE(),
@weekdate, @sunday, @monday, @tuesday, @wednesday, @thursday, @friday, @saturday)
END
END


Thanks in advance for your input.