update form, check boxes

[rice-daddy]

so im trying to update the database thru a from with checkboxes

i looked at tons of example code but its still not working. heres the code:

update form:

Smoking
<cfif #rsUnit.Smoking# is "yes">
<cfinput type="checkbox" name="Smoking" checked />
<cfelse>
<cfinput type="checkbox" name="Smoking" />
</cfif>

Kitchen
<cfif #rsUnit.Kitchen# is "yes">
<cfinput type="checkbox" name="Kitchen" checked />
<cfelse>
<cfinput type="checkbox" name="Kitchen" />
</cfif>

Action Page/Handler:

<cfif not IsDefined("Form.Smoking")>
<cfset form.Smoking = "No">
<cfelse>
<cfset form.Smoking = "yes">
</cfif>

<cfif not IsDefined("Form.Kitchen")>
<cfset form.Kitchen = "no">
<cfelse>
<cfset form.Kitchen = "yes">
</cfif>


<cfquery datasource = "u31db" name = "rsUpdate">
UPDATE tblRentalUnit
SET
Smoking='#form.Smoking#',
Kitchen='#form.Kitchen#'

WHERE UnitID='#form.UnitID#'
</cfquery>



It gives me a data type mismatch error. i have no idea whats going on please help

[JayStang]

Sounds like a SQL error. What is the datatype of the "Smoking" and "Kitchen" columns? If its looking for a integer or a booleon, use 1 or 0 instead of yes, no.

Also this is just personal preference, but i prefer using one "input" for the checkbox and integrate the if statment inside it.

example:
<input type="checkbok" name="Kitchen" <cfif rsUnit.Kitchen eq 'yes'>checked</cfif> />

pound signs are not required in a cfif either.

hope that helps

[cfmatt]

I would re-write your code using the cfqueryparam, its always good practice to use the cfqueryparam tag.

Something like:

<cfquery datasource = "u31db" name = "rsUpdate">
UPDATE 
	tblRentalUnit
SET 
	Smoking=<cfqueryparam value="#form.Smoking#" CFSQLType="CF_SQL_INTEGER" >,
	Kitchen=<cfqueryparam value="#form.Kitchen#" CFSQLType="CF_SQL_INTEGER" >
WHERE 
	UnitID=<cfqueryparam value="#form.UnitID#" CFSQLType="CF_SQL_INTEGER" >
</cfquery>

This example assumes that your checbox values and UnitID are integers otherwise change to suit.

Also try using cfc's if you are not already using them.

hope this helps

[pradeepk]

Also can be done like this,

Smoking
<cfif #rsUnit.Smoking# is "yes">
<cfinput type="checkbox" name="Smoking" checked />
<cfelse>
<cfinput type="checkbox" name="Smoking" />
</cfif>

Kitchen
<cfif #rsUnit.Kitchen# is "yes">
<cfinput type="checkbox" name="Kitchen" checked />
<cfelse>
<cfinput type="checkbox" name="Kitchen" />
</cfif>

Action Page/Handler:

<cfif not IsDefined("Form.Smoking")>
<cfset IsSmoking= 0>
<cfelse>
<cfset IsSmoking= 1>
</cfif>

similarly for kitchen also
....
...
...




<cfquery datasource = "u31db" name = "rsUpdate">
UPDATE tblRentalUnit
SET
Smoking=#IsSmoking#,
Kitchen=#Kitchen#

WHERE UnitID=#form.UnitID#
</cfquery>