cf sql incorrect syntax error

I've looked at this code and can't find the problem. I've gone through the sql specs and made sure every insert field value matches the data type. I'm assuming I have a bit column that may be getting a value other than 1 or 2.

65 : values(#val(QI.isrecn)#,'#trim(TitleCase(QI.isfnam))#' + ' ' + '#trim(TitleCase(QI.islnam))#','#QI.isrecn#','#trim(QI.isemai)#',#QI.isdent#)
66 : </cfquery>
67 : <cfquery name="QusersInfoInsert" datasource="#request.dsn#">
68 : Insert into users_info
69 : (userid,

SQLSTATE         HY000
SQL          Insert into users_info (userid, FirstName, LastName, Address1, Address2, City, State, ZipCode, Country, Country_Phone_Prefix, Home_Phone, Home_Area_Code, Work_Phone, Work_Area_Code, Fax_Phone, Fax_24_hour_home, Cell_Phone, Email_Address_Work, Current_Title, Specialty, Date_of_Birth, Marital_Status, Spouse_Name, Number_of_Kids, Mr_Mrs_Ms_Miss, Requested_Salary, Current_Salary, Current_Company_Number, Previous_Company_Number, Title_Number, Secretary_Name, Original_Recruiter, Current_Consultant, Date_Entered, Date_Updated, Last_Phone_Date, Date_of_Last_Correspondance, Market_Plan, Next_Market_Plan, Last_Mail_Date, Next_Mail_Date, Current_Letter, Next_Letter, Market_Sender_Consultant, Off-Limits_Flag, Tax_Specialty_Id, Year_Started_in_Tax, Do_Not_Mail_To_Work, Do_Not_Mail_To_Home, Resume_In_House, Placement_Date, Outside_Audit_Firm, Outside_Tax_Firm, Manual_File, JD_Year, Number_of_Web_Updates, Changed_Company_Flag, Bird_Dog, Honors_JD, Send_Christmas_Card, On_a_Search_Job, TEI_Flag, New_Email_Flag, Changed_Title_Flag, Date_Update_Returned, Past_Law_Flag, Past_Corp_Flag, Past_Public_Flag, Tier_Tracking, Top_25_JD_Flag, Position_3_Tier, Active_Placement, Email_Unsubscribe, Corporate_Officer, Mail_Stop, Above_Top_Tax, Contract_Candidate, Job_Board) values( (param 1) , (param 2) , (param 3) , (param 4) , (param 5) , (param 6) , (param 7) , (param 8) , (param 9) , (param 10) , (param 11) , (param 12) , (param 13) , (param 14) , (param 15) , (param 16) , (param 17) , (param 18) , (param 19) , (param 20) , (param 21) , (param 22) , (param 23) , (param 24) , (param 25) , (param 26) , (param 27) , (param 28) , (param 29) , (param 30) , (param 31) , (param 32) , (param 33) , (param 34) , (param 35) , (param 36) , (param 37) , (param 38) , (param 39) , (param 40) , (param 41) , (param 42) , (param 43) , (param 44) , (param 45) , (param 46) , (param 47) , (param 48) , (param 49) , (param 50) , (param 51) , (param 52) , (param 53) , (param 54) , (param 55) , (param 56) , (param 57) , (param 58) , (param 59) , (param 60) , (param 61) , (param 62) , (param 63) , (param 64) , (param 65) , (param 66) , (param 67) , (param 68) , (param 69) , (param 70) , (param 71) , (param 72) , (param 73) , (param 74) , (param 75) , (param 76) , (param 77) , (param 78) , (param 79) )



<cfquery name="QusersInfoInsert" datasource="#request.dsn#">
Insert into users_info
(userid,
FirstName,
LastName,
Address1,
Address2,
City,
State,
ZipCode,
Country,
Country_Phone_Prefix,
Home_Phone,
Home_Area_Code,
Work_Phone,
Work_Area_Code,
Fax_Phone,
Fax_24_hour_home,
Cell_Phone,
Email_Address_Work,
Current_Title,
Specialty,
Date_of_Birth,
Marital_Status,
Spouse_Name,
Number_of_Kids,
Mr_Mrs_Ms_Miss,
Requested_Salary,
Current_Salary,
Current_Company_Number,
Previous_Company_Number,
Title_Number,
Secretary_Name,
Original_Recruiter,
Current_Consultant,
Date_Entered,
Date_Updated,
Last_Phone_Date,
Date_of_Last_Correspondance,
Market_Plan,
Next_Market_Plan,
Last_Mail_Date,
Next_Mail_Date,
Current_Letter,
Next_Letter,
Market_Sender_Consultant,
Off-Limits_Flag,
Tax_Specialty_Id,
Year_Started_in_Tax,
Do_Not_Mail_To_Work,
Do_Not_Mail_To_Home,
Resume_In_House,
Placement_Date,
Outside_Audit_Firm,
Outside_Tax_Firm,
Manual_File,
JD_Year,
Number_of_Web_Updates,
Changed_Company_Flag,
Bird_Dog,
Honors_JD,
Send_Christmas_Card,
On_a_Search_Job,
TEI_Flag,
New_Email_Flag,
Changed_Title_Flag,
Date_Update_Returned,
Past_Law_Flag,
Past_Corp_Flag,
Past_Public_Flag,
Tier_Tracking,
Top_25_JD_Flag,
Position_3_Tier,
Active_Placement,
Email_Unsubscribe,
Corporate_Officer,
Mail_Stop,
Above_Top_Tax,
Contract_Candidate,
Job_Board)


values(

<cfqueryparam value="#val(QI.isrecn)#" CFSQLType="cf_sql_integer">,
<cfqueryparam value="#trim(TitleCase(QI.isfnam))#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#trim(TitleCase(QI.islnam))#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#trim(TitleCase(QI.isadr1))#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#trim(TitleCase(QI.isadr2))#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#trim(TitleCase(QI.iscity))#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#trim(TitleCase(QI.isstat))#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#QI.iszipa#' + '-' + '#QI.iszipb#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#trim(TitleCase(QI.iscnty))#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#trim(TitleCase(QI.ispcou))#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#QI.ishfon#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#QI.ishfac#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#QI.iswfon#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#QI.iswfac#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#QI.isemer#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#val(QI.ishir1)#" CFSQLType="cf_sql_bit">,
<cfqueryparam value="#QI.iscfon#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#trim(TitleCase(QI.isema2))#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#trim(TitleCase(QI.istitl))#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#QI.istaxs#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#QI.isdob#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#QI.ismsta#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#trim(TitleCase(QI.ismate))#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#trim(TitleCase(QI.iskids))#" CFSQLType="cf_sql_smallint">,
<cfqueryparam value="#trim(TitleCase(QI.ismrms))#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#trim(TitleCase(QI.issalr))#" CFSQLType="cf_sql_smallint">,
<cfqueryparam value="#trim(TitleCase(QI.issalc))#" CFSQLType="cf_sql_smallint">,
<cfqueryparam value="#val(QI.iscomp)#" CFSQLType="cf_sql_integer">,
<cfqueryparam value="#val(QI.iscmp2)#" CFSQLType="cf_sql_integer">,
<cfqueryparam value="0" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#trim(TitleCase(QI.isectf))#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#trim(TitleCase(QI.isorgc))#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#trim(TitleCase(QI.iscons))#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#val(QI.isdent)#" CFSQLType="cf_sql_date">,
<cfqueryparam value="#val(QI.isdupd)#" CFSQLType="cf_sql_date">,
<cfqueryparam value="0" CFSQLType="cf_sql_date">,
<cfqueryparam value="#trim(TitleCase(QI.islcsp))#" CFSQLType="cf_sql_date">,
<cfqueryparam value="#trim(TitleCase(QI.ismktp))#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#trim(TitleCase(QI.isnmkt))#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#trim(TitleCase(QI.islmdt))#" CFSQLType="cf_sql_date">,
<cfqueryparam value="#trim(TitleCase(QI.isnmdt))#" CFSQLType="cf_sql_date">,
<cfqueryparam value="#trim(TitleCase(QI.iscltr))#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#trim(TitleCase(QI.isnltr))#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#trim(TitleCase(QI.ismkts))#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#val(QI.isoffl)#" CFSQLType="cf_sql_bit">,
<cfqueryparam value="#val(QI.istaxs)#" CFSQLType="cf_sql_integer">,
<cfqueryparam value="#trim(TitleCase(QI.istxyr))#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#val(QI.isnad2)#" CFSQLType="cf_sql_bit">, <!---do not mail work --->
<cfqueryparam value="#val(QI.isnadd)#" CFSQLType="cf_sql_bit">, <!---do not mail home --->
<cfqueryparam value="#val(QI.isrinh)#" CFSQLType="cf_sql_bit">,
<cfqueryparam value="#trim(TitleCase(QI.ispldt))#" CFSQLType="cf_sql_date">,
<cfqueryparam value="#trim(TitleCase(QI.isoaf))#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#trim(TitleCase(QI.isotx))#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#val(QI.isrsfl)#" CFSQLType="cf_sql_bit">,
<cfqueryparam value="#trim(TitleCase(QI.isjdyr))#" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#val(QI.isflg1)#" CFSQLType="cf_sql_bit">,
<cfqueryparam value="#val(QI.isflg2)#" CFSQLType="cf_sql_bit">,
<cfqueryparam value="#val(QI.isflg5)#" CFSQLType="cf_sql_bit">,
<cfqueryparam value="#val(QI.isflg7)#" CFSQLType="cf_sql_bit">,
<cfqueryparam value="#VAL(QI.isflg8)#" CFSQLType="cf_sql_bit">,
<cfqueryparam value="#VAL(QI.isflg9)#" CFSQLType="cf_sql_bit">,
<cfqueryparam value="#VAL(QI.isonsr)#" CFSQLType="cf_sql_bit">,
<cfqueryparam value="#VAL(QI.istie)#" CFSQLType="cf_sql_bit">,
<cfqueryparam value="#VAL(QI.isxtr1)#" CFSQLType="cf_sql_bit">,
<cfqueryparam value="#VAL(QI.isflg8)#" CFSQLType="cf_sql_bit">,
<cfqueryparam value="#QI.isurtn#'" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="#VAL(QI.isflga)#" CFSQLType="cf_sql_bit">,
<cfqueryparam value="#VAL(QI.isflgb)#" CFSQLType="cf_sql_bit">,
<cfqueryparam value="#VAL(QI.ishir2)#" CFSQLType="cf_sql_bit">,
<cfqueryparam value="#VAL(QI.ishir3)#" CFSQLType="cf_sql_bit">,
<cfqueryparam value="#VAL(QI.isflgc)#" CFSQLType="cf_sql_bit">,
<cfqueryparam value="#VAL(QI.istxfl)#" CFSQLType="cf_sql_integer">,
<cfqueryparam value="#VAL(QI.isactp)#" CFSQLType="cf_sql_bit">,
<cfqueryparam value="#VAL(QI.ishir5)#" CFSQLType="cf_sql_bit">,
<cfqueryparam value="#VAL(QI.iscorp)#" CFSQLType="cf_sql_bit">,
<cfqueryparam value="#trim(TitleCase(QI.ismstp))#'" CFSQLType="cf_sql_varchar">,
<cfqueryparam value="0" CFSQLType="cf_sql_bit">,
<cfqueryparam value="#val(QI.ishotc)#" CFSQLType="cf_sql_bit">,
<cfqueryparam value="#QI.ishir7#" CFSQLType="cf_sql_varchar">
)


</cfquery>
LVL 1
larksysAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_Commented:
You seem to have 1 less column name in the select list than in the value list.  I am sure you're more familiar the column codes and what they mean and can easily find the missing column.
0
larksysAuthor Commented:
I guess that happens when you keep changing your code. Let me fix that and run it again. BTW - congrats, rookie.
0
_agx_Commented:
Congratulations on what?
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

larksysAuthor Commented:
Yes there was 1 column name missing. But no change. Same error.
0
_agx_Commented:
> ]Incorrect syntax near the keyword 'Off'.

Its difficult to tell not being able to see any of the values. But if that is the error message you're referring to, then that may be your problem.   If the value is being inserted into a BIT column it should be a number, not OFF.
0
_agx_Commented:
It could also be an empty value.  Though it would be much easier to diagnose the problem if you posted the actual values sent with the query.  ie the  (param 1) , (param 2) , (param 3), .. values
0
larksysAuthor Commented:
There are no columns coming from the source table that have the value "off" in it. The values are listed below the error message above. I'm going to match them up with the column names one more time by values.
0
_agx_Commented:
> The values are listed below the error message above.

No they are not.
0
larksysAuthor Commented:
Most Valuable Rookie Expert

      
1      KCTS                                  3,467,727
2      DatabaseMX                 1,885,534
3      RobSampson                1,315,547
4      MrHusy                           1,075,228
5      imitchie                        1,064,462
6      farhankazi                     1,046,387
7      LauraEHunterMVP       1,024,596
8      _agx_                                  970,415
9      michko                                  850,464
10      nizsmo                                 741,688
0
larksysAuthor Commented:
Oops. That was before I spec'd cfqueryparam.
0
_agx_Commented:
> Most Valuable Rookie Expert

That is so funny.  Everyone notices these things *except* me.  lol
0
_agx_Commented:
> Oops. That was before I spec'd cfqueryparam.

If full debugging is on, the values should show beneath the query.  
0
larksysAuthor Commented:
I found a couple of mis-matches between using bit and integer. I've checked it twice since I changed it. Same error. Am I missing something on the SQL defs?

Col          Type      Allow Null

UserID      int      Unchecked
FirstName      varchar(50)      Checked
LastName      varchar(50)      Checked
Address1      varchar(50)      Checked
Address2      varchar(50)      Checked
City      varchar(50)      Checked
State      varchar(4)      Checked
ZipCode      varchar(20)      Checked
Country      varchar(50)      Checked
Country_Phone_Prefix      varchar(50)      Checked
Home_Phone      varchar(50)      Checked
Home_Area_Code      varchar(50)      Checked
Work_Phone      varchar(50)      Checked
Work_Area_Code      varchar(3)      Checked
Fax_Phone      varchar(50)      Checked
Fax_24_hour_home      bit      Checked
Cell_Phone      varchar(50)      Checked
Email_Address_Work      varchar(50)      Checked
Current_Title      varchar(50)      Checked
Specialty      varchar(50)      Checked
Date_of_Birth      varchar(50)      Checked
Marital_Status      varchar(1)      Checked
Spouse_Name      varchar(50)      Checked
Number_of_Kids      smallint      Checked
Mr_Mrs_Ms_Miss      varchar(50)      Checked
Requested_Salary      smallint      Checked
Current_Salary      smallint      Checked
Current_Company_Number      int      Checked
Previous_Company_Number      int      Checked
Title_Number      int      Checked
Secretary_Name      varchar(50)      Checked
Original_Recruiter      varchar(50)      Checked
Current_Consultant      varchar(50)      Checked
Date_Entered      datetime      Checked
Date_Updated      datetime      Checked
Last_Phone_Date      datetime      Checked
Date_of_Last_Correspondance      datetime      Checked
Market_Plan      varchar(50)      Checked
Next_Market_Plan      varchar(50)      Checked
Last_Mail_Date      datetime      Checked
Next_Mail_Date      datetime      Checked
Current_Letter      varchar(50)      Checked
Next_Letter      varchar(50)      Checked
Market_Sender_Consultant      varchar(50)      Checked
Off_Limits_Flag      bit      Checked
Tax_Specialty_Id      int      Checked
Year_Started_in_Tax      varchar(4)      Checked
Do_Not_Mail_To_Work      bit      Checked
Do_Not_Mail_To_Home      bit      Checked
Resume_In_House      bit      Checked
Placement_Date      datetime      Checked
Outside_Audit_Firm      varchar(50)      Checked
Outside_Tax_Firm      varchar(50)      Checked
Manual_File      bit      Checked
JD_year      varchar(4)      Checked
Number_of_Web_Updates      bit      Checked
Changed_Company_Flag      bit      Checked
Bird_Dog      bit      Checked
Honors_JD      bit      Checked
Salary_Verified      bit      Checked
Send_Christmas_Card      bit      Checked
On_A_Search_Job      bit      Checked
TEI_Flag      bit      Checked
New_Email_Flag      bit      Checked
Changed_Title_Flag      bit      Checked
Date_Update_Returned      varchar(50)      Checked
Past_Law_Flag      bit      Checked
Past_Corp_Flag      bit      Checked
Past_Public_Flag      bit      Checked
Tier_Tracking      bit      Checked
Top_25_JD_Flag      bit      Checked
Position_3_Tier      int      Checked
Active_Placement      bit      Checked
Email_Unsubscribe      bit      Checked
Corporate_Officer      bit      Checked
Mail_Stop      varchar(50)      Checked
Above_Top_Tax      bit      Checked
Contract_Candidate      bit      Checked
Job_Board      varchar(10)      Checked
0
_agx_Commented:
They  look okay but they still might not match up with the actual values inserted.  Its really hard to say without seeing the values that are sent.  

I noticed a few left over single quotes, probably from the conversion. Though I doubt they would throw an error.

... value="#QI.isurtn#'"

You could try splitting up the query, into smaller pieces.  Just to see which part throws an error.  

Is it feasible to insert the values into a work table, where all columns are varchar type.  Then scrub the data in the work table. When its ready insert it into the permanent table?  

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
larksysAuthor Commented:
I found a column name of OFF-LIMITS_FLAG that should have been OFF_LIMITS_FLAG. Strange error message for such an error. Thanks for making me look harder.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.