Solved

Classic ASP saving checkbox value to MSAccess database

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now