[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Problem with SQL Update Query in CF 8

Posted on 2009-05-14
15
Medium Priority
?
337 Views
Last Modified: 2013-12-24
I am using Access 2000 in a CF 8 application. The application allows school district alumni to check a box in their profile if they have a Facebook account. When updating their profile they can either select or de-select this check box. Whenever I try to update my profile it comes back with an error message that says "Too few parameters. Expected 2". I know that typically this error means that I have misspelled or mis-used a column name. But, I have checked carefully and my column names all appear correct. Any ideas?


<!--- Determine if user checked Facebook --->
<cfquery name="qFacebook" datasource="#DataSource#">
Select AlumID, Facebook
FROM TBL_Alum
WHERE AlumID = <cfqueryparam value=#Session.Auth.AlumID# cfsqltype="cf_sql_numeric" maxlength="10">
</cfquery>
 
<!--- If yes do the following --->
 
<cfif (qFacebook.Facebook EQ "no") AND (facebook IS "yes")>
   <cfquery name="qFacebook_Yes" datasource="#DataSource#">
     UPDATE TBL_Alum
     SET Facebook = "yes" 
     WHERE AlumID = <cfqueryparam value=#Session.Auth.AlumID# cfsqltype="cf_sql_numeric" maxlength="10">
</cfquery>
 
<!--- If no do the following --->
 
  <cfelseif (qFacebook.Facebook EQ "yes") AND (facebook IS NOT "yes")>
     <cfquery name="qFacebook_No" datasource="#DataSource#">
      UPDATE TBL_Alum
      SET Facebook = "no" 
      WHERE AlumID = <cfqueryparam value=#Session.Auth.AlumID# cfsqltype="cf_sql_numeric" maxlength="10">
     </cfquery>
</cfif>

Open in new window

0
Comment
Question by:kenjpete
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 3
15 Comments
 
LVL 27

Expert Comment

by:azadisaryev
ID: 24386802
what's the data type of your Facebook column?

Azadi
0
 
LVL 27

Expert Comment

by:azadisaryev
ID: 24386847
you should not use double quotes in your SQL - use single quotes instead.

Azadi
0
 

Author Comment

by:kenjpete
ID: 24386998
The Facebook column is a Yes/No field. I tried single quotes and removing the quotes. In both cases I am getting a "Data Type Mismatch" error?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 52

Expert Comment

by:_agx_
ID: 24387248
You should be using cfqueryparam for all columns.  Use "cf_sql_bit" for yes/no columns.   Either true/false or 1/0 should work for the values.   Yes/no might work as well.

 UPDATE TBL_Alum
SET Facebook = <cfqueryparam value="false" cfsqltype="cf_sql_bit">
WHERE AlumID = <cfqueryparam value=#Session.Auth.AlumID# cfsqltype="cf_sql_numeric" maxlength="10">
     </cfquery>
0
 
LVL 27

Expert Comment

by:azadisaryev
ID: 24387274
try 0 instead of 'no' and 1 instead of 'yes'
(or is it something silly like -1 for 'yes' and 0 for 'no'... can't remember if it's the yes/no ms access fields that uses these ridiculous values or some other one...)

the actual values ms access stores in those fields are NOT 'yes' and 'no' - that's just what ms access displays for you to see...

Azadi
0
 
LVL 52

Expert Comment

by:_agx_
ID: 24387422
Also, you may as well just run the update using cf_sql_bit and the selected value.  Either way you are doing a query.  So you may as well just use a single update.  The extra select and cfif/cfelse just adds a lot of extra code and decreases readability.  

0
 
LVL 52

Expert Comment

by:_agx_
ID: 24387433
ie ...
          UPDATE TBL_Alum
          SET Facebook = <cfqueryparam value="#theNewFacebookValue#" cfsqltype="cf_sql_bit">
          WHERE AlumID = <cfqueryparam value=#Session.Auth.AlumID# cfsqltype="cf_sql_numeric" maxlength="10">
0
 

Author Comment

by:kenjpete
ID: 24387495
I updated the code below as follows. While it is not generating an error message....it is not updating properly either. **AQX - not sure what you mean by just doing a single update? Don't I have to check for both possibilities?
***********************
<!--- Determine if user checked Facebook --->
<cfquery name="qFacebook" datasource="#DataSource#">
Select AlumID, Facebook
FROM TBL_Alum
WHERE AlumID = <cfqueryparam value=#Session.Auth.AlumID# cfsqltype="cf_sql_numeric" maxlength="10">
</cfquery>

<!--- If yes do the following --->

<cfif (qFacebook.Facebook EQ 0) AND (facebook IS "yes")>
   <cfquery name="qFacebook_Yes" datasource="#DataSource#">
     UPDATE TBL_Alum
     SET Facebook = <cfqueryparam value="1" cfsqltype="cf_sql_bit">
     WHERE AlumID = <cfqueryparam value=#Session.Auth.AlumID# cfsqltype="cf_sql_numeric" maxlength="10">
</cfquery>

<!--- If no do the following --->

  <cfelseif (qFacebook.Facebook EQ 1) AND (facebook IS NOT "yes")>
 
     <cfquery name="qFacebook_No" datasource="#DataSource#">
      UPDATE TBL_Alum
      SET Facebook = <cfqueryparam value="0" cfsqltype="cf_sql_bit">
      WHERE AlumID = <cfqueryparam value=#Session.Auth.AlumID# cfsqltype="cf_sql_numeric" maxlength="10">
     </cfquery>
</cfif>
0
 

Author Comment

by:kenjpete
ID: 24387508
Ok...but what would my cfif statement look like then?
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 1400 total points
ID: 24387597
> **AQX - not sure what you mean by just doing a single update? Don't I have to check
> for both possibilities?

No.  In your FORM and table this is just a boolean (true/false) value.  All you are really trying to do is set the table value to whatever the user selected: yes/no.  You can achieve that with just an UPDATE.

If they checked the box, the UPDATE will set the table value to "yes".  Otherwise it will be set to "no". That sounds like all you need.

Example:  
<cfset form.theNewFacebookValue = "yes">

<!--- the value will be set to yes --->
<cfquery ...>
          UPDATE TBL_Alum
          SET Facebook = <cfqueryparam value="#form.theNewFacebookValue #" cfsqltype="cf_sql_bit">
          WHERE AlumID = <cfqueryparam value=#Session.Auth.AlumID# cfsqltype="cf_sql_numeric" maxlength="10">
</cfquery>
0
 

Author Comment

by:kenjpete
ID: 24387911
Is this what you mean?

<cfparam name="form.facebook" default="">
<cfset form.theNewFacebookValue = "#form.facebook#">

<!--- Set new Facebook value --->
<cfquery name="qFacebook" datasource="#DataSource#">
 UPDATE TBL_Alum
 SET Facebook = <cfqueryparam value="#form.theNewFacebookValue#" cfsqltype="cf_sql_bit">
 WHERE AlumID = <cfqueryparam value=#Session.Auth.AlumID# cfsqltype="cf_sql_numeric" maxlength="10">
</cfquery>

I get an error with this that says Invalid Data for CFSQLTYPE CF_SQL_BIT? Currently my data type is set to yes/no, but it doesn't seem to matter. I get the same error if I use True/False or On/Off as well.

0
 

Author Comment

by:kenjpete
ID: 24388181
I'm not sure if this helps but I just dumped the form.facebook value. When the facebook check box is checked the form value comes back as 0? When it is NOT checked it throws a "Element Facebook is undefined in form" error. If I add this line of code:

<cfparam name="form.facebook" default="">

and the check box is again NOT checked the form value is an empty string? Why is not setting it to 1 or some other value?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 24388429
We can't see your form code, but unless you are using flash forms or something, just set the checkbox value (and default) to whatever you need.  In your case, set the values to "yes" and "no".  Then the query will work fine.

FORM:
<input type="checkbox"  value="yes" name="facebook">

DEFAULT:
<cfparam name="form.facebook" default="no">
0
 
LVL 52

Expert Comment

by:_agx_
ID: 24388439
> Why is not setting it to 1 or some other value?

    ... you have to set the desired value in your code.
0
 

Author Closing Comment

by:kenjpete
ID: 31581549
Thank you...that worked great!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question