cfinsert vs. SQL insert and NULL values

[snix1]

Hi! I have a form that users fill out and the data is stored in the database (SQL server). All fields are not required. When I use <cfquery> with the SQL insert into tablename(field1,field2, etc.) values (...), the record is stored with blank fields when the user entered nothing instead of explicit nulls (which I want).

When I use <cfinsert tablename=&quot;tname&quot; datafields=&quot;field1,field2&quot;, the explicit nulls are stored for any field on the form for which the user entered nothing. However, if the user enters an explicit blank in the field, it is stored as a blank and not a null.

I have all form fields defined in the action template using <cfparam>. I want explicit null values for fields not entered. Do I need to Ltrim every form field before using cfinsert? What's the difference here?

Thanks and I hope this makes sense.

 

[Komil13]

hi,
i had the same problem when i started coding with coldfusion. i always use the sql insert statement between the <cfquery>. i never use the cfinsert, cfupdate, or the cfdelete because of the same problem that u are having.

for the users entering blank in the field, use trim() function. it strips all spaces surrounding the string in the field. if there is a blank, then it will store a null in the data field in the db.

hope that helps.

 

[danielhai]

in your query are you having:

sp_storedprocedure @FieldName=''?

If so, the field will be blank unless you have:

sp_storedprocedure @FieldName=null

 

[snix1]

Hi all. Thanks for your input. I tried using the cfquery followed by the insert into... with the Trim function:

<cfquery name=&quot;storeit&quot; datasource=&quot;mysource&quot;>
insert into mytable(field1,field2,field3)
values('#Trim(field1)#','#Trim(field2)#','#Trim(field3)#')

When I leave the fields in the form blank, I do not get an explicit <NULL> in the field. I get a blank. If I query the table to return all rows where a field is not null, I get this blank field too. I hope this makes sense...I think I am still doing something wrong.

danielhai: I am not using a stored procedure. Do you recommend this? Must one then check the field to ensure it is blank and then set the field to null?

Thanks!

 

[tleish]

To insert a NULL value, you insert NULL without single qoutes around it. You could loop through the form variables, check to see if they are blank, and set them to NULL if they are not.

[COLOR=666666]<!--- Loop through a list of fields and change them to null if they are blank --->[/color]
<cfloop list=&quot;field1,field2,field3&quot; index=&quot;i&quot;>
<cfscript>
FORM

[

i

]

= Trim(FORM

[

i

]

);
if( Len(FORM

[

i

]

) )
FORM

[

i

]

= &quot;'#FORM

[

i

]

#'&quot;; [COLOR=666666]// Add Single Quotes around values (Assuming they are string)[/color]
else
FORM

[

i

]

= &quot;NULL&quot;; [COLOR=666666]// Set field to equal NULL[/color]
</cfscript>
</cfloop>

[COLOR=666666]<!--- Intert Query --->[/color]
<cfquery name=&quot;storeit&quot; datasource=&quot;mysource&quot;>
insert into mytable(field1,field2,field3)
values(#FORM.field1)#,#FORM.field2#,#FORM.field3#')
</cfquery> - tleish

 

[snix1]

Thanks, tleish. I had thought to strip the blanks from the FORM fields and then use cfinsert, which seems to work o.k. I'll try your solution, though.

Thanks to everyone who responded!

 

[snix1]

Hi tleish:

I tried your solution and I'm getting an error and I'm not sure why. Here is a stripped down version of the template:
THE FORM:
<cfform name=&quot;testform&quot; action=&quot;testaction.cfm&quot; method=&quot;post&quot;>
<table>
<tr><td>Last Name:</td><td><cfinput name=&quot;last_name&quot; size=&quot;20&quot; type=&quot;text&quot;> </td></tr>
<tr><td>First Name:</td><td><cfinput name=&quot;first_name&quot; size=&quot;20&quot; type=&quot;text&quot;></td></tr>
<tr><td><input type=&quot;submit&quot; value=&quot;submit&quot;></td></tr>
</table>
</cfform>

The action:

<cfloop list=&quot;first_name,last_name&quot; index=&quot;i&quot;>
<cfscript>
FORM = Trim(FORM);
if (Len(FORM))
FORM = &quot;'#FORM#'&quot;;
else
FORM = &quot;NULL&quot;;
</cfscript>
</cfloop>
<cfquery name=&quot;storeit&quot; datasource=&quot;icesa&quot;>
insert into reg3(email,first_name,last_name,reg_id)
values('email@here.com',#FORM.first_name#,#FORM.last_name#,123)
</cfquery>


Get the error:
ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near 'Ron'.



The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (18:1) to (18:43).

When I print out the FORM variables, they look o.k. with the quotes around or just null without quotes.

 

[tleish]

The script needs FORM

[

i

]

instead of just FORM.

<cfloop list=&quot;first_name,last_name&quot; index=&quot;i&quot;>
<cfscript>
FORM

[

i

]

= Trim(FORM

[

i

]

);
if (Len(FORM

[

i

]

))
FORM

[

i

]

= &quot;'#FORM

[

i

]

#'&quot;;
else
FORM

[

i

]

= &quot;NULL&quot;;
</cfscript>
</cfloop> - tleish

 

[snix1]

Sorry about that...I didn't surround my code with the code, /code attribute, so the brackets were stripped..

This is what I really have:

<cfscript>
      FORM[i] = Trim(FORM[i]);
	  if (Len(FORM[i]))
	     FORM[i] = &quot;'#FORM[i]#'&quot;;
	  else
	     FORM[i] = &quot;NULL&quot;;	 
   </cfscript>

 

[tleish]

The problem appears to be in the SQL script somwhere.

Double check the values of the fields in the DB. Make sure you are passing strings to strings and numbers to numbers.

Also, output the query to the screen and then try running it seperate against the DB.

[COLOR=666666]<!--- Output Query to the Screen --->[/color]
<cfoutput>
cfquery name=&quot;storeit&quot; datasource=&quot;icesa&quot;>
insert into reg3(email,first_name,last_name,reg_id)
values('email@here.com',#FORM.first_name#,#FORM.last_name#,123)
/cfquery>
</cfoutput> - tleish