Classic ASP saving checkbox value to MSAccess database

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

KimKingAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Russell LucasConnect With a Mentor IT Infrastructure Project ManagerCommented:
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.
0
 
omgangCommented:
Have you tried writing a -1 for True and a 0 for False?
OM Gang
0
 
KimKingAuthor Commented:
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

0
 
dan_nealConnect With a Mentor Commented:
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

0
 
Russell LucasIT Infrastructure Project ManagerCommented:
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.
0
All Courses

From novice to tech pro — start learning today.