query woes

[Imperial, Bob]

I've been looking at everything I can to get this query to work and I
have 20 others with a lot of the same basic info that work just fine but
this one is eating my lunch. I've dumped the form contents to see what's
there and all appears to be fine. I've even recreated the table in the
db but can't seem to pin this one down. I've removed the line the error
message points out in the query and the form fields passed but nada. I
am using MySQL 5 if that sheds any light.

 

 

The error occurred in D:\WebNew\csw\process\actMedicineReferral.cfm:
line 89
Called from D:\WebNew\csw\process\actMedicineReferral.cfm: line 17
Called from D:\WebNew\csw\process\actMedicineReferral.cfm: line 89
Called from D:\WebNew\csw\process\actMedicineReferral.cfm: line 17

87 :         <cfqueryparam cfsqltype="cf_sql_varchar"
value="#form.Priority_Tracking#" />,

88 :         <cfqueryparam cfsqltype="cf_sql_varchar"
value="#form.WebCIS_Note_Date#" />,

89 :         <cfqueryparam cfsqltype="cf_sql_varchar"
value="#justTheNumber#" />

90 :         )

91 : </cfquery>

________________________________

SQL

   INSERT INTO tblmedicinereferrals ( DATE_SUBMITTED, providerID,
Physician, Doctor_Email, Pager_Number, SubmittedBy, SubmittedBy_Email,
phone, First_Name, Last_Name, MR_Num, Referral_Type, Referring_To,
otherOption, DoctorName, When, DX_Symptoms, Reason_Referral,
InformedPatient, PriorityTracking, WebCIS_NoteDate, RequestID ) 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) )

 

 

My query:  the value of justTheNumber is set just above the query and
works fine on 20 other queries using it.

<cfquery name="requestMEDICINE" datasource="#dsdata3#">

INSERT INTO tblmedicinereferrals

(DATE_SUBMITTED,

 providerID,

 Physician,

Doctor_Email,

Pager_Number,

SubmittedBy,

SubmittedBy_Email,

 phone,

First_Name,

Last_Name,

MR_Num,

Referral_Type,

Referring_To,

otherOption,

 DoctorName,

When,

 DX_Symptoms,

 Reason_Referral,

 InformedPatient,

PriorityTracking,

WebCIS_NoteDate,

RequestID)

 

VALUES

(<cfqueryparam cfsqltype="cf_sql_timestamp" value="#Now()#" />,

<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.providerID#" />,

<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Physician#" />,

<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Doctor_Email#" />,

<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Pager_Number#" />,

<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.SubmittedBy#" />,

<cfqueryparam cfsqltype="cf_sql_varchar"
value="#form.SubmittedBy_Email#" />,

<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.phone#" />,

<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.First_Name#" />,

<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Last_Name#" />,

<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.MR_Num#" />,

<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Referral_Type#"
/>,

<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Referring_To#" />,

<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.otherOption#" />,

<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Doctor_Name#" />,

<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.When#" />,

<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.DX_Symptoms#" />,

<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Reason_Referral#"
/>,

<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.InformedPatient#"
/>,

<cfqueryparam cfsqltype="cf_sql_varchar"
value="#form.Priority_Tracking#" />,

<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.WebCIS_Note_Date#"
/>,

<cfqueryparam cfsqltype="cf_sql_varchar" value="#justTheNumber#" />)

</cfquery>

 

TIA

 

Bob

 



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:4804
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.15

[Chuck-69]

Bob,

The column When might be causing the problem.  'When' is a reserved word in
MySQL.  I would suggest escaping the column name with `.  Like so... `When`.

HTH

Chuck


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:4805
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.15

[Imperial, Bob]

Thanks Chuck!

That was the problem ;-) thanks a million! It pays to read the manual I
guess.

Bob
-----Original Message-----
From: Chuck [mailto:hof@...]
Sent: Friday, September 11, 2009 5:58 PM
To: cf-newbie
Subject: RE: query woes


Bob,

The column When might be causing the problem.  'When' is a reserved word
in
MySQL.  I would suggest escaping the column name with `.  Like so...
`When`.

HTH

Chuck




~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:4806
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.15