Skip to main content
C
CFmonger
Inspiring
March 23, 2009
Answered

How to use a check box - yes/no in a form to update db

  • March 23, 2009
  • 2 replies
  • 1361 views
Hello;
I am trying to use a checkbox in a form I created. I need to for a yes or no feature. If it's checked, it is yes, if not, no.

I have it in a form that can either add a "user" or update an existing record. Right now it is throwing an error, and it is for the check box code. Here is the code that matters for this and the error:

page userEdit.cfm

<cfparam name="url.RecordID" type="integer" default="0">
<cfparam name="variables.ID" type="integer" default="#url.RecordID#">
<cfparam name="variables.Fname" default="">
<cfparam name="variables.Lname" default="">
<cfparam name="variables.userName" default="">
<cfparam name="variables.password" default="">
<cfparam name="variables.email" default="">
<cfparam name="variables.admin" default="">

<cfif url.RecordID GT 0>
<cfquery name="useRec" datasource="#APPLICATION.dataSource#">
SELECT user.Fname, user.Lname, user.userName, user.password, user.email, user.admin, user.ID
FROM user
WHERE user.ID =<cfqueryparam value="#URL.RecordID#" cfsqltype="cf_sql_integer">
</cfquery>

<cfif useRec.RecordCount EQ 1>
<cfset variables.RecordID = useRec.ID>
<cfset variables.Fname = useRec.Fname>
<cfset variables.Lname = useRec.Lname>
<cfset variables.userName = useRec.userName>
<cfset variables.password = useRec.password>
<cfset variables.email = useRec.email>
<cfset variables.admin = useRec.admin>
</cfif>
</cfif>
<head>
......
<body>
<cfoutput>
<cfform action="action.cfm" method="post">
<cfinput type="hidden" name="ID" value="#variables.ID#">
..... more for feilds here ..........
<cfinput type="checkbox" name="admin" value="#variables.admin#">
... submit buttons here ...
</cfform>
</body>

Action Page:

<cfif isDefined("Form.RecordID")>
<cfquery datasource="#APPLICATION.dataSource#">
UPDATE user
SET user.Fname=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Fname#">,
user.Lname=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Lname#">,
user.userName=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.userName#">,
user.password=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.password#">,
user.email=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.email#">,
user.admin=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.admin#">
WHERE ID =<cfqueryparam value="#form.RecordID#" cfsqlType="CF_SQL_INTEGER">
</cfquery>
<cflocation url="indexUser.cfm?RecordID=#Form.RecordID#" addtoken="no">
<cfelse>
<cfquery result="GetNewRecord" datasource="#APPLICATION.dataSource#" dbtype="ODBC">
INSERT INTO user
(Fname, Mname, Lname, userName, password, email, admin)
VALUES (<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Fname#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Lname#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.userName#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.password#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.email#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.admin#">)
</cfquery>
<cflocation url="indexUser.cfm">
</cfif>

My error message:

Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same.
The error occurred on line 33.

That is the line where it is supposed to place the checkbox into teh database on the action page.
How do I use a checkbox for this? The checkbox is the admin tab in the db and that is set to a yes/no setting

Thank you.

CFmonger
    This topic has been closed for replies.
    Correct answer Dan_Bracuk
    quote:
    Originally posted by: CFmonger
    I fixed the error, it isn't throwing that anymore. It makes a new record now when I check off the check box.

    I think it probably has to be with me scoping what that checkbox will do when it updates or adds a new record to the database? Correct? That is why it is adding a new record instead of just checking the yes/no in the database table.

    If this is my problem, then how do I scope this?

    That is not your problem. The code in the OP shows the update and insert query governed by the same if/else logic.

    2 replies

    D
    Dan_Bracuk
    Inspiring
    March 23, 2009
    Let's look at that error message again.
    Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same.

    Did you count anything in your insert query?
    C
    CFmongerAuthor
    Inspiring
    March 23, 2009
    I fixed the error, it isn't throwing that anymore. It makes a new record now when I check off the check box.

    I think it probably has to be with me scoping what that checkbox will do when it updates or adds a new record to the database? Correct? That is why it is adding a new record instead of just checking the yes/no in the database table.

    If this is my problem, then how do I scope this?
    D
    Dan_BracukCorrect answer
    Inspiring
    March 24, 2009
    quote:
    Originally posted by: CFmonger
    I fixed the error, it isn't throwing that anymore. It makes a new record now when I check off the check box.

    I think it probably has to be with me scoping what that checkbox will do when it updates or adds a new record to the database? Correct? That is why it is adding a new record instead of just checking the yes/no in the database table.

    If this is my problem, then how do I scope this?

    That is not your problem. The code in the OP shows the update and insert query governed by the same if/else logic.
    D
    Dan_Bracuk
    Inspiring
    March 23, 2009
    submit your form with the check box checked and unchecked. cfdump your form on the action page.
    C
    CFmongerAuthor
    Inspiring
    March 23, 2009
    I can't get it to dump, I tried
    <cfdump var="#form#">
    <cfabort>

    it wouldn't fire. BUT I did get it to not throw an error. but now, if you heck the box, it makes another record. I just need it to hit yes / no in the db. How do I get this to work properly, the only change in my code is this:

    <cfquery result="GetNewRecord" datasource="#APPLICATION.dataSource#" dbtype="ODBC">
    INSERT INTO user
    (Fname, Lname, userName, password, email, admin)

    I had to take out Mname, I did away with that.

    I have been trying to find out how to program in a check box like this, I have the books, and have been searching live docs and can't find a solid answer.

    How do I do it?
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices