INSERT INTO 2

[beckyh]

On my Insert page which receives info from the entry form, I have an INSERT statement. If the user doesn't fill in every field, the SQL doesn't work. So I added cfparams with a default value="". Shouldn't think take of those empty fields?

 

[ecobb]

It should work, either use a cfparam or IsDefined() to determine if the field is used in the query.

<cfquery name="create_incident" datasource="#Foley#">
INSERT INTO dbo.Incidents(Date<cfif IsDefined("Form.Sales")>,Sales</cfif>)
VALUES(<cfqueryparam value="#Form.Date#"cfsqltype="CF_SQL_DATE"><cfif IsDefined("Form.Sales")>,<cfqueryparam value="#Form.Sales#" cfsqltype="CF_SQL_BIT"></cfif>)
</cfquery>

Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher

 

[TruthInSatire]

Not a double post this time ECOBB Just wanted to elaborate on a problem using default = "".
If you use default = "" you still may end up with a problem if your field isn't set up to accept 0 length strings. I wouldn't recomend you do that.

I normally use this in the "values" part of the insert

<cfif isDefined("form.fieldName") and len(trim(form.fieldName))>
'#form.fieldName#'
<cfelse>
NULL
</cfif>

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)

 

[r937]

you still may end up with a problem if your field isn't set up to accept 0 length strings

indeed, or if the column is numeric and the database is one which, unlike, say, mysql, actually enforces data type compatibility


you should not ever be able to insert a zero length string into a numeric field

rudy

http://r937.com/

SQL Consulting

 

[beckyh]

It was indeed the numeric fields that were causing problems. As soon as I entered a value in the cfinput, the SQL statement works. Thanks!

 

[ecobb]

Crap! I ALWAYS forget my "Len(Trim())"! (-:



Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher

 

[TruthInSatire]

Ecobb. Just add it to your title.

len(trim("Beer Consumption Analyst"))

oddly enough that returns 24. Anyone for a case?

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)