Solved

Problem with SQL Update Query in CF 8

Posted on 2009-05-14
15
332 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 350 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
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 video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

773 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