[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Problem with SQL Update Query in CF 8

Posted on 2009-05-14
15
Medium Priority
?
339 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
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 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