Inserting to empty date field

[createmedia]

Hello All,

I have a form where updating a field with a null value works...(thanks to the
forum)... but inserting doesn't seem to work with same technique....
I always still get a "data tyype mismatch" when the the form is empty and the
date field is empty.....
The current code is (with Access 2003 db and date field is long date) :

<CFQUERY name="AddProject" datasource="caproject">
INSERT INTO CAprojectDB(Company,Practice,CaClientNum, ProjDueDate)
VALUES (
<cfqueryparam
value="#Tempcompany#"
cfsqltype="CF_SQL_VARCHAR"
maxlength="50">,
<cfqueryparam
value="#Temppractice#"
cfsqltype="CF_SQL_VARCHAR"
maxlength="50">,
<cfqueryparam
value="#Tempclientnumber#"
cfsqltype="CF_SQL_VARCHAR"
maxlength="50">,
<CFIF Form.projectduedate NEQ "">
<cfqueryparam
value="#CreateODBCDate(Form.projectduedate)#"
cfsqltype="CF_SQL_DATE"
maxlength="50">
<CFELSE>
<cfqueryparam
value=""
cfsqltype="CF_SQL_DATE"
maxlength="50"
null="Yes">
</CFIF>
)
</CFQUERY>

Thanks so much!

[Dan Bracuk]

after <cfelse>, all you need is one word, null.

[createmedia]

Thanks Dan,

But, I still get "Data type mismatch" when sending an empty field...

:-(

[LL@Work]

This should do it:
<CFIF Form.projectduedate NEQ "">
<cfqueryparam
value="#CreateODBCDate(Form.projectduedate)#"
cfsqltype="CF_SQL_DATE"
maxlength="50">
<CFELSE>
<cfqueryparam
value=NULL
cfsqltype="CF_SQL_DATE"
maxlength="50"
null="Yes">
</CFIF>

[createmedia]

Thanks but still does gives me the error when empty...
Data type mismatch in criteria expression.

I'm using :
<CFIF Form.projectduedate NEQ "">
<cfqueryparam
value="#CreateODBCDate(Form.projectduedate)#"
cfsqltype="CF_SQL_DATE"
maxlength="50">,
<CFELSE>
<cfqueryparam
value=NULL
cfsqltype="CF_SQL_DATE"
maxlength="50"
null="Yes">,
</CFIF>

Rechecked the Access 2003 db field .. and it is a Long Date
<CFIF Form.projectduedate NEQ "">
<cfqueryparam
value="#CreateODBCDate(Form.projectduedate)#"
cfsqltype="CF_SQL_DATE"
maxlength="50">,
<CFELSE>
<cfqueryparam
value=NULL
cfsqltype="CF_SQL_DATE"
maxlength="50"
null="Yes">,
</CFIF>
This does not seem like it should be this difficult....
Oh yes, also tried :
<CFIF Form.projectduedate NEQ "">
<cfqueryparam
value="#CreateODBCDate(Form.projectduedate)#"
cfsqltype="CF_SQL_DATE"
maxlength="50">,
<CFELSE>
NULL
</CFIF>

Thanks for all your help!

[Dan Bracuk]

Originally posted by: createmedia
Thanks Dan,

But, I still get "Data type mismatch" when sending an empty field...

:-(

Could you show us the sql that was sent to your db when this happened?

[createmedia]

Sure, thanks.

<CFQUERY name="AddProject" datasource="caproject">
INSERT INTO CAprojectDB(Company,Practice,CaClientNum, ProjDueDate, DateAdded,
CaJobNum, QntyOrdered, ProjectSize, DieCut, Comments, ArtIntoCp, UpdateDate)
VALUES (
<cfqueryparam
value="#Form.company#"
cfsqltype="CF_SQL_VARCHAR"
maxlength="50">,
<cfqueryparam
value="#Form.practice#"
cfsqltype="CF_SQL_VARCHAR"
maxlength="50">,
<cfqueryparam
value="#Form.clientnumber#"
cfsqltype="CF_SQL_VARCHAR"
maxlength="50">,
<CFIF Form.projectduedate NEQ "">
<cfqueryparam
value="#CreateODBCDate(Form.projectduedate)#"
cfsqltype="CF_SQL_DATE"
maxlength="50">,
<CFELSE>
<cfqueryparam
value=NULL
cfsqltype="CF_SQL_DATE"
maxlength="50"
null="Yes">,
</CFIF>
<CFIF Form.dateadded NEQ "">
<cfqueryparam
value="#CreateODBCDate(Form.dateadded)#"
cfsqltype="CF_SQL_DATE"
maxlength="50">,
<CFELSE>
<cfqueryparam
value=NULL
cfsqltype="CF_SQL_DATE"
maxlength="50"
null="Yes">,
</CFIF>
<cfqueryparam
value="#Form.cajobnumber#"
cfsqltype="CF_SQL_VARCHAR"
maxlength="50">,
<cfqueryparam
value="#Form.quantityordered#"
cfsqltype="CF_SQL_VARCHAR"
maxlength="50">,
<cfqueryparam
value="#Form.Size#"
cfsqltype="CF_SQL_VARCHAR"
maxlength="50">,
<cfqueryparam
value="#Form.diecut#"
cfsqltype="CF_SQL_VARCHAR"
maxlength="50">,
<cfqueryparam
value="#Form.notes#"
cfsqltype="CF_SQL_VARCHAR"
maxlength="50">,
<CFIF Form.datein NEQ "">
<cfqueryparam
value="#CreateODBCDate(Form.datein)#"
cfsqltype="CF_SQL_DATE"
maxlength="50">,
<CFELSE>
<cfqueryparam
value=NULL
cfsqltype="CF_SQL_DATE"
maxlength="50"
null="Yes">,
</CFIF>
<CFIF Form.datehold NEQ "">
<cfqueryparam
value="#CreateODBCDate(Form.datehold)#"
cfsqltype="CF_SQL_DATE"
maxlength="50">
<CFELSE>
<cfqueryparam
value=NULL
cfsqltype="CF_SQL_DATE"
maxlength="50"
null="Yes">
</CFIF>
)
</CFQUERY>

[MikerRoo]

What version of CF are you using?
Also, is quantityordered a string or a number?

Anyway, try the attached.



<CFQUERY name="AddProject" datasource="caproject">
INSERT INTO
CAprojectDB
(
Company,
Practice,
CaClientNum,
ProjDueDate,
DateAdded,
CaJobNum,
QntyOrdered,
ProjectSize,
DieCut,
Comments,
ArtIntoCp,
UpdateDate
)
VALUES
(
<cfqueryparam value="#Form.company#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">,
<cfqueryparam value="#Form.practice#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">,
<cfqueryparam value="#Form.clientnumber#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">,

<CFIF Trim (Form.projectduedate) IS NOT "">
<cfqueryparam value=#CreateODBCDate (Trim
(Form.projectduedate))# cfsqltype="CF_SQL_DATE">,
<CFELSE>
<cfqueryparam value="" null="Yes"
cfsqltype="CF_SQL_DATE">,
</CFIF>

<CFIF Trim (Form.dateadded) IS NOT "">
<cfqueryparam value=#CreateODBCDate (Trim (Form.dateadded))#
cfsqltype="CF_SQL_DATE">,
<CFELSE>
<cfqueryparam value="" null="Yes"
cfsqltype="CF_SQL_DATE">,
</CFIF>

<cfqueryparam value="#Form.cajobnumber#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">,
<cfqueryparam value="#Form.quantityordered#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">,

<!--- ***** Should the previous have been:
<cfqueryparam value=#Form.quantityordered#
cfsqltype="CF_SQL_INTEGER">,
???
*****
--->

<cfqueryparam value="#Form.Size#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">,
<cfqueryparam value="#Form.diecut#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">,
<cfqueryparam value="#Form.notes#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">,

<CFIF Trim (Form.datein) IS NOT "">
<cfqueryparam value=#CreateODBCDate (Trim (Form.datein))#
cfsqltype="CF_SQL_DATE">,
<CFELSE>
<cfqueryparam value="" null="Yes"
cfsqltype="CF_SQL_DATE">,
</CFIF>

<CFIF Trim (Form.datehold) IS NOT "">
<cfqueryparam value=#CreateODBCDate (Trim (Form.datehold))#
cfsqltype="CF_SQL_DATE">
<CFELSE>
<cfqueryparam value="" null="Yes"
cfsqltype="CF_SQL_DATE">
</CFIF>
)
</CFQUERY>

[createmedia]

Thanks Mike,
The qntyOrdered IS a number so used the "Integer" tag...but still gives same
error(Date type mismatch) when Dates fields are emtpy... Using Access 2003 db
also....
Can it be this difficult to send an empty value to a date field?...(Apparently
so)...


Am using Coldfusion MX

And used :



<CFQUERY name="AddProject" datasource="caproject">
INSERT INTO
CAprojectDB
(
Company,
Practice,
CaClientNum,
ProjDueDate,
DateAdded,
CaJobNum,
QntyOrdered,
ProjectSize,
DieCut,
Comments,
ArtIntoCp,
UpdateDate
)
VALUES
(
<cfqueryparam value="#Form.company#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">,
<cfqueryparam value="#Form.practice#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">,
<cfqueryparam value="#Form.clientnumber#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">,

<CFIF Trim (Form.projectduedate) IS NOT "">
<cfqueryparam value=#CreateODBCDate (Trim
(Form.projectduedate))# cfsqltype="CF_SQL_DATE">,
<CFELSE>
<cfqueryparam value="" null="Yes"
cfsqltype="CF_SQL_DATE">,
</CFIF>

<CFIF Trim (Form.dateadded) IS NOT "">
<cfqueryparam value=#CreateODBCDate (Trim (Form.dateadded))#
cfsqltype="CF_SQL_DATE">,
<CFELSE>
<cfqueryparam value="" null="Yes"
cfsqltype="CF_SQL_DATE">,
</CFIF>

<cfqueryparam value="#Form.cajobnumber#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">,
<cfqueryparam value=#Form.quantityordered#
cfsqltype="CF_SQL_INTEGER">,
<cfqueryparam value="#Form.Size#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">,
<cfqueryparam value="#Form.diecut#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">,
<cfqueryparam value="#Form.notes#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">,

<CFIF Trim (Form.datein) IS NOT "">
<cfqueryparam value=#CreateODBCDate (Trim (Form.datein))#
cfsqltype="CF_SQL_DATE">,
<CFELSE>
<cfqueryparam value="" null="Yes"
cfsqltype="CF_SQL_DATE">,
</CFIF>

<CFIF Trim (Form.datehold) IS NOT "">
<cfqueryparam value=#CreateODBCDate (Trim (Form.datehold))#
cfsqltype="CF_SQL_DATE">
<CFELSE>
<cfqueryparam value="" null="Yes"
cfsqltype="CF_SQL_DATE">
</CFIF>
)
</CFQUERY>

[MikerRoo]

CFMX 6 or MX7?

And, no it's normally not this difficult to insert nulls. In fact many of
the earlier suggestions should have worked.

What is the EXACT type of the table coulumns?
What are the settings of the "Required" and "Index" fields in the design view
for each column?

What is the EXACT error message (including failed line and generated SQL)?

[createmedia]

Ok.. let's see.

CFMX 6 on the server.

All date fields in MS Access 2003 are "Long Date", Required= No, Indexed=No,
No default values, No validation rules or text,

Complete Error Message is when Project Date(Form.projectduedate) or any other
date field is sent empty :

Error Executing Database Query.
Data type mismatch in criteria expression.

The error occurred in
E:\websites\nmvinc.com\projects\colourpress\editac tion.cfm: line 184

182 : <CFELSE>
183 : <cfqueryparam value="" null="Yes"
cfsqltype="CF_SQL_DATE">
184 : </CFIF>
185 : )
186 : </CFQUERY>




--------------------------------------------------------------------------------

SQL INSERT INTO CAprojectDB ( Company, Practice, CaClientNum, ProjDueDate,
DateAdded, CaJobNum, QntyOrdered, ProjectSize, DieCut, Comments, ArtIntoCp,
UpdateDate ) VALUES ( (param 1) , (param 2) , (param 3) , (param 4) , (param 5)
, (param 6) , (param 7) , (param 8) , (param 9) , (param 10) , (param 11) ,
(param 12) )
DATASOURCE caproject
VENDORERRORCODE -3030
SQLSTATE 22018

Please try the following:
Check the ColdFusion documentation to verify that you are using the correct
syntax.
Search the Knowledge Base to find a solution to your problem.


Browser Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR
1.1.4322)
Remote Address 201.121.218.199
Referrer [url]http://www.nmvinc.com/projects/colourpress/jobentry.cfm[/url]
Date/Time 01-Mar-06 11:38 AM

Stack Trace (click to expand)

Thanks for your help!

[MikerRoo]

Yeah, nulls weren't handled too well in CF6 but that may not be the problem.

That error message doesn't necessarily implicate the date fields.
There could be a field that is being passed as string but is really something
else, like a number. (These fields are not always converted correctly.)

Also replace
<cfqueryparam value=#Form.quantityordered# cfsqltype="CF_SQL_INTEGER">
with
<cfqueryparam value=#Trim (Form.quantityordered)#
cfsqltype="CF_SQL_INTEGER">

First, try replacing CF_SQL_DATE with CF_SQL_TIMESTAMP (might get lucky).

Next, using the same data every time...

(1) comment out all optional fields like so:
<CFQUERY name="AddProject" datasource="caproject">
INSERT INTO
CAprojectDB
(
Company
<!--- Comment out as many fields as possible and reintro them one
by one.
Take care to handle trailing commas!
Practice,
CaClientNum,
ProjDueDate,
DateAdded,
CaJobNum,
QntyOrdered,
ProjectSize,
DieCut,
Comments,
ArtIntoCp,
UpdateDate
--->
)
VALUES
(
<cfqueryparam value="#Form.company#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">

<!--- Comment out as many fields as possible and reintro them one
by one.
Take care to handle trailing commas!
<cfqueryparam value="#Form.practice#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">,
<cfqueryparam value="#Form.clientnumber#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">,

<CFIF Trim (Form.projectduedate) IS NOT "">
<cfqueryparam value=#CreateODBCDate (Trim
(Form.projectduedate))# cfsqltype="CF_SQL_DATE">,
<CFELSE>
<cfqueryparam value="" null="Yes"
cfsqltype="CF_SQL_DATE">,
</CFIF>

<CFIF Trim (Form.dateadded) IS NOT "">
<cfqueryparam value=#CreateODBCDate (Trim (Form.dateadded))#
cfsqltype="CF_SQL_DATE">,
<CFELSE>
<cfqueryparam value="" null="Yes"
cfsqltype="CF_SQL_DATE">,
</CFIF>

<cfqueryparam value="#Form.cajobnumber#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">,
<cfqueryparam value=#Trim (Form.quantityordered)#
cfsqltype="CF_SQL_INTEGER">,
<cfqueryparam value="#Form.Size#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">,
<cfqueryparam value="#Form.diecut#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">,
<cfqueryparam value="#Form.notes#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">,

<CFIF Trim (Form.datein) IS NOT "">
<cfqueryparam value=#CreateODBCDate (Trim (Form.datein))#
cfsqltype="CF_SQL_DATE">,
<CFELSE>
<cfqueryparam value="" null="Yes"
cfsqltype="CF_SQL_DATE">,
</CFIF>

<CFIF Trim (Form.datehold) IS NOT "">
<cfqueryparam value=#CreateODBCDate (Trim (Form.datehold))#
cfsqltype="CF_SQL_DATE">
<CFELSE>
<cfqueryparam value="" null="Yes"
cfsqltype="CF_SQL_DATE">
</CFIF>
--->
)
</CFQUERY>

(2) Reintroduce the fields until the query breaks again.

I'll post an alternative approach to all of this later, after work.

Good luck.

[MikerRoo]

Is this problem still not solved?

Try Inserting Nulls, Old School...
(That is, Leave null columns out of the SQL all together!)

Use the attached code:


<!--- Process the inputs first. --->
<CFSCRIPT>
function zProcessDateInputs (sDateInput)
{
var zRezStruct = StructNew ();
sDateInput = Trim (sDateInput);

if (IsDate (sDateInput))
{
zRezStruct.dtVal = CreateODBCDate (sDateInput);
zRezStruct.bOK = true;
}
else
{
zRezStruct.dtVal = CreateODBCDate (Now ()); //-- Default.
This may be ignored.
zRezStruct.bOK = false;
}

return zRezStruct;
}

zDateAdded = zProcessDateInputs (Form.DateAdded );
zDateHold = zProcessDateInputs (Form.DateHold );
zDateIn = zProcessDateInputs (Form.DateIn );
zProjectDueDate = zProcessDateInputs (Form.ProjectDueDate);
</CFSCRIPT>

<CFQUERY name="AddProject" datasource="AccFoo"> <!--- ****caproject --->
INSERT INTO
CAprojectDB
(
Company
, Practice
, CaClientNum
<CFIF zProjectDueDate.bOK >, ProjDueDate </CFIF>
<CFIF zDateAdded.bOK >, DateAdded </CFIF>
, CaJobNum
, QntyOrdered
, ProjectSize
, DieCut
, Comments
<CFIF zDateIn.bOK >, ArtIntoCp </CFIF>
<CFIF zDateHold.bOK >, UpdateDate </CFIF>
)
VALUES
(
<cfqueryparam value="#Trim (Form.company)#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">
, <cfqueryparam value="#Trim (Form.practice)#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">
, <cfqueryparam value="#Trim (Form.clientnumber)#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">

<CFIF zProjectDueDate.bOK>
, <cfqueryparam value=#zProjectDueDate.dtVal#
cfsqltype="CF_SQL_DATE">
</CFIF>

<CFIF zDateAdded.bOK>
, <cfqueryparam value=#zDateAdded.dtVal#
cfsqltype="CF_SQL_DATE">
</CFIF>

, <cfqueryparam value="#Trim (Form.cajobnumber)#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">
, <cfqueryparam value=#Trim (Form.quantityordered)#
cfsqltype="CF_SQL_INTEGER">
, <cfqueryparam value="#Trim (Form.Size)#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">
, <cfqueryparam value="#Trim (Form.diecut)#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">
, <cfqueryparam value="#Trim (Form.notes)#"
cfsqltype="CF_SQL_VARCHAR" maxlength="50">

<CFIF zDateIn.bOK>
, <cfqueryparam value=#zDateIn.dtVal#
cfsqltype="CF_SQL_DATE">
</CFIF>

<CFIF zDateHold.bOK>
, <cfqueryparam value=#zDateHold.dtVal#
cfsqltype="CF_SQL_DATE">
</CFIF>
)
</CFQUERY>

[createmedia]

Actually MikerROO the previous code worked!!!

Thank you soo much!!!
I think that the "Trim" function made a big difference....

Thanks again... hopefully I'll be able to help someone else.. :-)