Solved

Classic ASP saving checkbox value to MSAccess database

Posted on 2011-02-28

2 solutions

1,422 Views

Last Modified: 2012-05-11

I am having a problem saving checkbox values to an MSAccess table. See code below for snippets. Its a fairly long complex form with a number of checkboxes but I'm having the same problem with all of them. I get an error on the line with:
MM_editCmd.Execute
stating "Microsoft JET Database Engine error '80040e14'

Syntax error in UPDATE statement. "

I've tried every permutation of checking the value of the checkbox I can think of and they all error out. If I simply hard code:
kcheckboxExo = True
it works fine.

HELP!

Kim King

<input <%If (cstr((rsWorks.Fields.Item("ExhibOnly").Value)) = cstr(true)) Then Response.Write("checked") : Response.Write("")%> type="checkbox" name="checkboxExo" value="ExhibOnly">

'then check for value:
if len(request.form("checkboxExo"))>0 then
    kcheckboxExo = True
end if

'sql:
  MM_editQuery = "update webworks set ExhibOnly="&kcheckboxExo&", CustomSort='"& kcustsort & "', Status="&kcheckboxstat&" where webworks.originalworknumber = " & MM_recordId &";"

'data:
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_editConnection
    MM_editCmd.CommandText = MM_editQuery
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close

Open in new window

Comment

Question by:KimKing

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

5 Comments

LVL 28

Expert Comment

by:omgang

ID: 349989382011-02-28

Have you tried writing a -1 for True and a 0 for False?
OM Gang

LVL 6

Accepted Solution

by:Russell Lucas

Russell Lucas earned 250 total points

ID: 349989742011-02-28

I would suggest you need to control both the true and false values for KcheckboxExo:-

'then check for value:
if len(request.form("checkboxExo"))>0 then
    kcheckboxExo = True
else 
    kcheckboxExo = False
end if

Open in new window

I would then also ensure that this variable is declared as a Boolean value, otherwise it may not be returning the true boolean values for True and False.

If this fails I would suggest changing the field on the database itself to a small integer and simply writing 0 or 1 to it. It generally works better and you can read and right the values directly to the checkbox.

Author Comment

by:KimKing

ID: 350018812011-02-28

Funinig_Stroll,

Adding the false statement fixed the straight checkbox problem, but there are two that are numeric fields (1 or 0) that I'm having trouble with the code below. If I don't make it cstr(kcheckboxwkstat), I get a type mismatch error.

Thanks,

Kim

kcheckboxwkstat = request.form("checkboxwkstat")
if cstr(kcheckboxwkstat)="1" then
    kcheckboxwkstat=1
else
    kcheckboxwkstat=0
end if

Open in new window

LVL 9

Assisted Solution

by:dan_neal

dan_neal earned 250 total points

ID: 350033342011-02-28

Looks like your checking form the wrong value.
You field value is [value="ExhibOnly"] but your checking for > 0 in the server logic.

Checkboxes are sent back with the value of the checkbox only when its checked. If unchecked then its "".

kcheckboxwkstat = request.form("checkboxwkstat")
if cstr(kcheckboxwkstat)="ExhibOnly" then
    kcheckboxwkstat=1
else
    kcheckboxwkstat=0
end if

Open in new window

LVL 6

Expert Comment

by:Russell Lucas

ID: 350061002011-03-01

What dan_neal has put is correct, a alternative solution is to lose the "Value=" part all together so that the checkbox just return a 0 or 1.

Whichever suits you best.

Featured Post

Question has a verified solution.

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

Comparison Of Access Files

Article by: Bitsqueezer

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.

Clever Gmail Targeting Phishing Email Discovered

Article by: Dermot

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…

MS Access – Basics of Designing Tables

Video by: Jim

Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…