You can try this, but I don't remember offhand if it works or not... I think
it does.
Before your CFINSERT manually check all numeric form fields for blanks and
change them to NULL if they are empty - like this:
---------------------------------------------
<cfif form.numberVar1 IS ""><cfset form.numberVar1 = "NULL"></cfif>
<cfif form.numberVar2 IS ""><cfset form.numberVar2 = "NULL"></cfif>
<cfif form.numberVar3 IS ""><cfset form.numberVar3 = "NULL"></cfif>
<cfinsert...
----------------------------------------------
The above approach sucks if you have to re-display the form values to the
user because the blank number fields will now say "NULL", but there are ways
around that.
if that doesn't work you'll have to do it manually like this:
---------------------------------------------
<cfif form.numberVar1 IS ""><cfset newVar1 = "NULL"></cfif>
<cfif form.numberVar2 IS ""><cfset newVar2 = "NULL"></cfif>
<cfif form.numberVar3 IS ""><cfset newVar3 = "NULL"></cfif>
(notice the use of newVar, that preserves the original values in the form
fields in case you need to re-display them)
<cfquery >
INSERT INTO...[you don't need any IF statements here...]
----------------------------------------------
this is the better approach anyway, CFINSERT is for people who are too lazy
to write out the column names ;)
Bryan Love Macromedia Certified Professional
Internet Application Developer / Database Analyst
Telecommunication Systems Inc.
[EMAIL PROTECTED]
"What father would hesitate to say 'if there must be trouble let it be in my
day, that my child may have peace'?"
- Thomas Paine, An American Crisis
-----Original Message-----
From: Chad Gray [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, November 13, 2001 3:50 PM
To: CF-Talk
Subject: SQL INSERT
This seems like such a beginner question, but can you use INSERT INTO with
form fields that are left blank? ( i am using <cfparam name="form.var"
default=""> for the radio buttons, and check boxes on the action page )
Im getting a "Syntax error in INSERT INTO statement". THe statement works
fine if all the fields in the form are filled out. If i submit the form
with all blanks i get this error.
The resulting SQL statement looks something like this on the action page:
SQL =
INSERT INTO products (FILENAME,DIM,CONFIG)
VALUES ('',,'')
This is with a MS Access database also.
DO i have to have to do:
INSERT INTO products (<CFIF form.var is not "">FILENAME</CFIF>)
VALUES (<CFIF form.var is not "">#form.filename#</CFIF>)
I hope not!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
