Checkbox & Queries

[thenagus]

I am working on a youth rights website, and while working on the membership application, I receive an datatype mismatch error.

I have pinpointed the error to be related to checkbox values.

All form variables are passed to the next page which then stores the forms values as session variables.

In the database, each of the database fields are set to Yes/No datatypes. Check forms checkbox saves its variable in the session as 'on' for true.

Here is the error I receive:

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

And here is just one of my queries, as a three have the same issues:

Support = oConn.Execute("INSERT INTO membership_support (AccountNumber, [money], Other) VALUES('" &Session.SessionID& "', '" &money& "', '" &OtherHelp& "')")

Any help would be appreciated.

[DrBunchman]

Hi there,

In order to find out the cause of a sql error a simple method is to print your sql string to the screen with a response.write (comment out the actual update statement) then try and run it in query analyser. That way you can see whether your variables are being passed to your sql correctly.

Can you do the above and print your sql here. Also check what values your datatypes will accept. I've never heard of a Yes/No datatype but presume it is a Boolean type which will only accept True or False.

Dr B

[jeffstl]

I am working on a youth rights website, and while working on the membership application, I receive an datatype mismatch error.

I have pinpointed the error to be related to checkbox values.

All form variables are passed to the next page which then stores the forms values as session variables.

In the database, each of the database fields are set to Yes/No datatypes. Check forms checkbox saves its variable in the session as 'on' for true.

Here is the error I receive:
And here is just one of my queries, as a three have the same issues:
Any help would be appreciated.

Access Yes\No fields never seem to work right in my experience.

However, you can try this in the SQL

Whichever field is your checkbox field you need to treat it like a number rather then a string. so " & Money & " instead of '" & Money "'

This is because the Yes\No datafield interprets 0 and -1 as the yes and no.

If this doesnt work I would change the yes\no datafield to a Text field and simply store the actualy letter Y or N in that field based on what the checkbox value is in code.

If chkBox.Value = -1 then
MyValue = "Y"
end if


Then you can treat it like a string instead of a number and its easier to deal with.

[thenagus]

Okay, let me try this again (browser crashed mid post)


What I have done to get the error is the following

joinchapter.asp
<form method=post action=joinsubmit.asp>
<input type=checkbox name=money> Donate
<input type=submit>

joinsubmit.asp
Session("money") = Request.Form("money")
Details = oConn.Execute("INSERT INTO membership_support (AccountNumber, [money]) VALUES('" &Session.SessionID& "', '" &Session("money")& "')")

When I then do a Response.Write to the screen on Session("money") I get "on" as the result if the checkbox was checked.


The Access database has the field datatype for money set to true/false (boolean). However, in Access you can also select its format from Yes/No (default), True/False and On/Off (I have tried them all; no joy).

I have tried changing "on" to "true", but still receive the same error. I have used checkboxes before in a similar manner, and have not had this issue.

I have even tried to insert directly, by Request.Form("money") and by declaring it as a variable in the querystring. No success!

[jeffstl]

Okay, let me try this again (browser crashed mid post)


What I have done to get the error is the following

joinchapter.asp
<form method=post action=joinsubmit.asp>
<input type=checkbox name=money> Donate
<input type=submit>

joinsubmit.asp
Session("money") = Request.Form("money")
Details = oConn.Execute("INSERT INTO membership_support (AccountNumber, [money]) VALUES('" &Session.SessionID& "', '" &Session("money")& "')")

When I then do a Response.Write to the screen on Session("money") I get "on" as the result if the checkbox was checked.


The Access database has the field datatype for money set to true/false (boolean). However, in Access you can also select its format from Yes/No (default), True/False and On/Off (I have tried them all; no joy).

I have tried changing "on" to "true", but still receive the same error. I have used checkboxes before in a similar manner, and have not had this issue.

I have even tried to insert directly, by Request.Form("money") and by declaring it as a variable in the querystring. No success!

As I said before the Yes\No data type in access doesnt work across platforms very well...its looking for a Microsoft defined boolean data type..... It may in fact be a data type specifically meant for access forms only (rather then web or vb forms).

Either way though doesn't matter ...your error is directly related to the SQL string not being properly formatted for the corresponding data type in your table. I pretty much gaurantee that.

I would suggest changing the data type to Text in the table, and storing your value as a "Y" or an "N" instead .....depending on what is the value of the check using an IF statement.

This is the quickest solution to your problem.

If Request.Form("money") = 1 then
     Session("Money") = "Y"
End If
 
'OR if your really getting on and off which i havent seen before (shoudl be 0 or 1)
If Request.Form("money") = "on" then
     Session("Money") = "Y"
End If
 

If you simply HAVE to get the yes\no data type to work then you will need to try both the single quote and NO single quote around the SQL variable Money......... to see if it can be passed as a number or a string or what it will accept.

If it accepts niether and you still get the same error then it pretty much proves that the Access Yes\No data type does not work outside of Access originated SQL ........and I would change it to Text, then format your "Money" variable to be a "Y" or an "N" before you build your SQL string.

[thenagus]

Okay thanks, I think I have it figured out. I don't know what I have changed over the past few months in my programming technique, but apparently something has changed. I used to use checkbox values with Access. I know I used to use the add.new function, now I use "Insert into". Maybe that is why I now get the error.

Anyways, what I did was change the true/false datatype to a byte formatted number type, this was I can stick with 1 or 0 in the advent that I upgrade to sql in the future.

[jhardman]

Okay thanks, I think I have it figured out. I don't know what I have changed over the past few months in my programming technique, but apparently something has changed. I used to use checkbox values with Access. I know I used to use the add.new function, now I use "Insert into". Maybe that is why I now get the error.

Anyways, what I did was change the true/false datatype to a byte formatted number type, this was I can stick with 1 or 0 in the advent that I upgrade to sql in the future.

There's actually an article on this phenomenon in the ASP How-to section including solutions. I believe the solution that markrawlingson (the author, one of our forum experts) suggests is to add a value attribute to the checkbox, if you are using Access, then you would get the following

value="True"

. This changes the sent data from "on" to "True" which should be insertable in the access db.

Jared