Solved

Classic ASP saving checkbox value to MSAccess database

Posted on 2011-02-28

2 solutions

Medium Priority

1,447 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 1000 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 1000 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

Splitting Microsoft Access Database with Multiple User

Article by: Jason

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…

Show pictures directly from URLs in Access forms and reports

Article by: Gustav

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…

Microsoft Access

MS Access – Cascading Combos

Video by: Jim

In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

Microsoft Access

Access Desktop Databases – A Quick Tour

Video by: Jim

What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.