Access data type mismatch for empty form field - Coldfusion Database Access

[createmedia]

Hello Everyone;

I'm using an MS Access DB on a CFMX (CF 6) site...and updating records in a db
from a form ..the CFM for code looks like :

<cfquery name="ProjectTable" datasource="caproject">
UPDATE CAprojectDB
SET CaClientNum = '#Form.CaClientNum#',
DateAdded = '#Form.DateAdded#',
CaJobNum = #Form.CaJobNum#,
QntyOrdered = #Form.QntyOrdered#,
DieCut = '#Form.diecut#',
OnHold = '#Form.OnHold#',
ProjDueDate = '#Form.ProjDueDate#',
UpdateDate = '#Form.UpdateDate#',
notes = '#Form.notes#',
Size = '#Form.Size#',
ArtIntoCp = '#Form.ArtIntoCp#'
WHERE ProjectID = #Form.ID#
</cfquery>

However, when the form is submitted and some of the fields are emtpy I get a
"Data type mismatch in criteria expression. " error. I've tried adding code to
the Insert fields...such as

<cfif isdefined("form.Size")>
Size = '#Form.Size#',
</cfif>

But, doesn't seem to do much.... I want to be able to update fields to empty
strings also... and can't really just check for non-empty strings....
Any help you can give is GREATLY appreciated!

Thanks so much,
John

[The ScareCrow]

When defining th columns in the database, you need to also set if the field can
be NULL or not.
In ms access, click on the column and look at the bottom of the screen, you
will see the "allow null"
Although I would have expected a different error message for this.
Ensure you have the correct data types set for each field.
EG. "DateAdded" if this is a dtate then it should be a date/time datatype
and you should set it with code like DateAdded =
#CreateODBCDate(Form.DateAdded)#,

Another thing that could be causing problems is that you have a couple of
columns in the database named with reserved words
"notes" and "size". If you can't change these then enclose in

To determine if the form field contains any data, you should check if it is
defined (as you have done) but also check the length
<cfif isdefined("form.Size") And Len(form.Size) NEQ "">
Size = '#Form.Size#',
</cfif>

But as you just want to insert an empty string anyway, there is no need to
check the length as it will do this anyway.

Ken

[createmedia]

Thanks Ken,

Renamed the "notes" and "size" fields....Good catch! The text fields are all
set to "Allow Zero Length" .. the Date fields seem to be the problem... They
don't have an "Allow Zero Length" property... just a "Required" property .. of
which all are set to "No"

Experimenting with one Date field called UpdateDate : <<UpdateDate =
'#Form.UpdateDate#',>> by sending the value in the form as an empty field or
string... Query like :

<cfquery name="ProjectTable" datasource="caproject">
UPDATE CAprojectDB
SET CaClientNum = '#Form.CaClientNum#',
UpdateDate = '#Form.UpdateDate#',
ArtIntoCp = '#Form.ArtIntoCp#'
WHERE ProjectID = #Form.ID#
</cfquery>

But, still getting the error : "Data type mismatch in criteria expression" .
The field in the MS Access DB is a Date/Time field...and the form is sending
the value :
<input type="text" size="15" name="UpdateDate"
value="<cfoutput>#DateFormat(dateInfo.UpdateDate)# </cfoutput>">

I want to allow an empty string to allow for an empty date field...but still
can't figure out why I get the Data type mismatch error...

Thanks again!!!

[paross1]

Maybe something like this?

<cfquery name="ProjectTable" datasource="caproject">
UPDATE CAprojectDB
SET CaClientNum = '#Form.CaClientNum#',
<CFIF Form.UpdateDateUpdateDate NEQ "">
UpdateDate = '#Form.UpdateDate#'
<CFELSE>
UpdateDate = NULL
</CFIF>,
ArtIntoCp = '#Form.ArtIntoCp#'
WHERE ProjectID = #Form.ID#
</cfquery>

Phil

[createmedia]

Thanks Ken!

this is what worked!

<CFIF Form.UpdateDate NEQ "">
UpdateDate = #CreateODBCDate(Form.UpdateDate)#,
<CFELSE>
UpdateDate = NULL,
</CFIF>

small typo but it worked!! Allows Null date values now!
You da man! :-)

[paross1]

Ken? You're welcome anyway. :D

Phil