insert error

[mxstu]

Dan Bracuk,

We're not talking about an OLE connection here. The OP's code and error messages on this and their other thread indicate they are using ODBC.

[Abdlah]

The data types are as follows:
User_id = text
First_name = text
Middle_name = text
Last_name = text
Gender = Yes/No
Occupation = text
DOB = Date/time
Cnsmr_Password = text
Cnsmr_PasswordHint = text
Date_created = Date/time

I see you mention that my Gender field may be yes/no and not text. Why won't
access (coldfusion) accept yes/no? TIA

[mxstu]

It will accept it, but when you put single quotes around the value, it will be
treated as "text" and not boolean (yes/no , true/false). So use something like

VALUES ( ....., #YesNoFormat(Form.gender)#, .... )

Although I think you can also use 0/1 or true/false (without single quotes).

[Abdlah]

Yes that gender definition as yes/no was the problem. So a big thanks. But, I am wondering why yes/not is not acceptable since this is what is being submitted? Thanks again:D

[mxstu]

You're welcome.

It is acceptible. You just needed to remove the single quotes. Single quotes
cause the access db engine to see the values as "text" and not boolean. Since
inserting a "TEXT" value into a "YES/NO" column is not allowed, the db engine
throws a data type mismatch error.

So use:
INSERT INTO yourTable (yourYesNoColumn) VALUES (YES )

.. and do not use ...
INSERT INTO yourTable (yourYesNoColumn) VALUES ('YES' ) <--- Note the single
quotes