Solved

Classic ASP saving checkbox value to MSAccess database

Posted on 2011-02-28
5
1,383 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

0
Comment
Question by:KimKing
5 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 34998938
Have you tried writing a -1 for True and a 0 for False?
OM Gang
0
 
LVL 6

Accepted Solution

by:
Russell Lucas earned 250 total points
ID: 34998974
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
 

Author Comment

by:KimKing
ID: 35001881
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
 
LVL 9

Assisted Solution

by:dan_neal
dan_neal earned 250 total points
ID: 35003334
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
 
LVL 6

Expert Comment

by:Russell Lucas
ID: 35006100
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

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question