Problem with SQL Update Query in CF 8

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

kenjpeteAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
> **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
 
azadisaryevCommented:
what's the data type of your Facebook column?

Azadi
0
 
azadisaryevCommented:
you should not use double quotes in your SQL - use single quotes instead.

Azadi
0
Cloud Class® Course: C++ 11 Fundamentals

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

 
kenjpeteAuthor Commented:
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
 
_agx_Commented:
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
 
azadisaryevCommented:
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
 
_agx_Commented:
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
 
_agx_Commented:
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
 
kenjpeteAuthor Commented:
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
 
kenjpeteAuthor Commented:
Ok...but what would my cfif statement look like then?
0
 
kenjpeteAuthor Commented:
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
 
kenjpeteAuthor Commented:
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
 
_agx_Commented:
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
 
_agx_Commented:
> Why is not setting it to 1 or some other value?

    ... you have to set the desired value in your code.
0
 
kenjpeteAuthor Commented:
Thank you...that worked great!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.