Solved

Problem with SQL Update Query in CF 8

Posted on 2009-05-14
15
330 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
  • 6
  • 6
  • 3
15 Comments
 
LVL 27

Expert Comment

by:azadisaryev
Comment Utility
what's the data type of your Facebook column?

Azadi
0
 
LVL 27

Expert Comment

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

Azadi
0
 

Author Comment

by:kenjpete
Comment Utility
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
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
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
Comment Utility
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_
Comment Utility
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_
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:kenjpete
Comment Utility
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
Comment Utility
Ok...but what would my cfif statement look like then?
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 350 total points
Comment Utility
> **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
Comment Utility
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
Comment Utility
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_
Comment Utility
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_
Comment Utility
> 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
Comment Utility
Thank you...that worked great!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now