Problem with checkbox, yes/no files, access 2000 2

[josel]

Howdy y'all!

I am having heck of a problem with this. I read dozen of postings where this is mentioned and none of the suggestions work for me.

I have an MS Access 2000 table. Fields set to Yes/No. I use check boxes in form with value=&quot;1&quot;. In the action page, I am using <cfparam .... default=0> for each of the checkbox fields.

Now, when updating the table I get the error:

ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (121:5) to (121:65).


I noticed posting where the values are suggested to be set to &quot;true&quot; and then to &quot;false&quot; in <cfparm ...>. Mean while, the ON/OFF switch is set by values of 0 and 1. I have tried both and nothing.

Do I need quotes around variables within <cfquery ... udate> statement?

Can some one please post a simple snip of code I can use to model my own with?

FOLLOWING IS MY QUERY:
<cfif &quot;#form.Action#&quot; IS &quot;EDIT&quot;>
<cfquery name=&quot;editMyProfile&quot; datasource=&quot;HGS&quot; btype=&quot;odbc&quot;>
update contacts
set ContactFirstName='#form.membFirstName#',
ContactLastName='#form.membLastName#',
ContactCompanyName='#form.membCompany#',
ContactAddr1='#form.membAddr1#',
ContactAddr2='#form.membAddr2#',
ContactCity='#form.membCity#',
ContactState='#form.membState#',
ContactZip='#form.membZipCode#',
ContactPhone='#form.membPhone#',
ContactFax='#form.membFax#',
ContactMobile'#form.membMobile#',
ContactEmail='#form.membeMail#',
ContactWebSite='#form.membWebSite#',
ContactComments='#form.membComments#',
ContactSource='#form.membSource#',
ContactLastRevised='#dateNow#',
ContactHTML=#HTML#,
ContactApparel=#form.membApparel#,
ContactElectronics=#form.membElectronics#,
ContactFurniture=#form.membFurniture#,
ContactShoes=#form.membShoes#,
ContactNovelties=#form.membNovelties#,
ContactDomestics=#form.membDomestics#,
ContactGifts=#form.membGiftItem#,
ContactSunglasses=#form.membSunglasses#,
ContactOfficeEquipment=#form.membOffice#,
ContactHouseware=#form.membHouseware#,
ContactSportingGoods=#form.membSportingGoods#
where ContactEmail = '#form.myOLDID#'
</cfquery>
<cfelse>
<cfquery name=&quot;addProfile&quot; datasource=&quot;HGS&quot; dbtype=&quot;odbc&quot;>
insert into contacts(
ContactFirstName,
ContactLastName,
ContactCompanyName,
ContactAddr1,
ContactAddr2,
ContactCity,
ContactState,
ContactZip,
ContactPhone,
ContactFax,
ContactMobile,
ContactEmail,
ContactWebSite,
ContactComments,
ContactPassword,
ContactRegisteredOn,
ContactSource)
values('#form.membFirstname#',
'#form.membLastName#',
'#form.membCompany#',
'#form.membAddr1#',
'#form.membAddr2#',
'#form.membCity#',
'#form.membState#',
'#form.membZipCode#',
'#form.membPhone#',
'#form.membFax#',
'#form.membMobile#',
'#form.membeMail#',
'#form.membWebSite#',
'#form.membComments#',
'#form.MembPassword#',
'#dateNow#',
'#form.membSource#')
</cfquery>
</cfif>

Sorry for the long post ... Any help will be truly appreciated!

Regards;

Jose Lerebours KNOWLEDGE: Something you can give away enlessly and gain more of it in the process! - Jose Lerebours

 

[webmigit]

If a checkbox is checked, it has a value that you specify, if it is not, it is submitted as blank...

So...

Accept Visa? <INPUT type=&quot;checkbox&quot; name=&quot;acceptVisa&quot; value=&quot;1&quot;>

Would be handled like this:

<CFIF form.AcceptVisa neq &quot;&quot;>
 <CFSET form.AcceptVisa=0>
</CFIF>

#form.Accepted#

is now a number, either 1 or 0.

Your best method is radio buttons though..

Accept Visa?
 <INPUT type=&quot;checkbox&quot; name=&quot;acceptVisa&quot; value=&quot;1&quot;> Yes
 <INPUT type=&quot;checkbox&quot; name=&quot;acceptVisa&quot; value=&quot;0&quot;> No

With that method, no CFIF is needed... ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[josel]

webmigit,

Thanks for the reply!

I am not having problems checking for and setting variables based on weather the checkbox fields are checked or not but rather assigning value to my database.

The error I get is on the <cfquery ... update ...> and I figure that it has something to do with data type compatability or lack there of.

So, are boolleam fields set ON/OFF by numeric values or text string?

If numeric, then I need to set my variables to 0 or 1. If text, then I need to set my variables to TRUE or FALSE.

It is amazing that something as trivial as this, is not discussed in any of about 6 books I've gone through looking for an answer ...

Thanks;


Jose Lerebours
KNOWLEDGE: Something you can give away enlessly and gain more of it in the process! - Jose Lerebours

 

[webmigit]

I assign them by number in access and sql server:

1 = Yes / True / On
2 = No / False / Off

You set them without quotes:

dbVar=#Value#

Not dbVar='#Value#'

Does this help?

Tony ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[josel]

Tony,

Thanks so very much for holding me by the hands on this one. I truly appreciate your taking a moment to help me out!

Your explanation did away with all doubts I had about the rule of syntax and helped get the problem solved.

Thanks again!

Regards;


Jose Lerebours
KNOWLEDGE: Something you can give away enlessly and gain more of it in the process! - Jose Lerebours

 

[SidianConsulting]

I'd like to amend part of this discussion to note that if a checkbox is not checked, the checkbox name/variable is not defined in the action page. The way to combat this is to use cfparam for all checkbox fields on your action page.

-Tek