I am using Cold Fusion to develop a

[calista]

I am using Cold Fusion to develop a questionnaire and process the results. I am trying to insert the responses into a database. I have one form variable, #form.Level# that is causing the insert to fail. I tested it to make sure it was numeric, and that the field in the database was numeric. The insert is shown below:

cfif IsNumeric(#Form.Level#)>
	<cfoutput>The level is numeric.</cfoutput>
<cfelse>
	<cfoutput>The level is text.</cfoutput>
</cfif>
<CFQUERY NAME=&quot;RecordResults&quot; DATASOURCE=&quot;Question&quot;>
	INSERT INTO ATable
	(
	SurveyID,
	QNumber,
	Answer,
	Level,
	Comments
	)
	values
	(
	'#form.SurveyID#',
	'#form.QNumber#',
	'#form.Answer#',
	'#form.Level#',
	'#form.Comments#'
	)
</CFQUERY>

Thanks in advance for any assistance!

 

[iqof188]

There's two things:

1. Is SurveyId an autonumber? If it is, don't include it in your query since it will be set automatically. Query should be:

<CFQUERY NAME=&quot;RecordResults&quot; DATASOURCE=&quot;Question&quot;>
INSERT INTO ATable
(SurveyID, QNumber, Answer, Level, Comments)
VALUES
(#form.SurveyID#', '#form.QNumber#', '#form.Answer#',
'#form.Level#', '#form.Comments#')
</CFQUERY>

2. Make sure you use single quotes only for alphanumeric database fields. For example, if SurveyId, QNumber and/or Level are numeric, remove the single quotes. Then your query should be:

<CFQUERY NAME=&quot;RecordResults&quot; DATASOURCE=&quot;Question&quot;>
INSERT INTO ATable
(SurveyID, QNumber, Answer, Level, Comments)
VALUES
(#form.SurveyID#', #form.QNumber#, '#form.Answer#',
#form.Level#, '#form.Comments#')
</CFQUERY>

Hope this helps...





<webguru>iqof188</webguru>

 

[calista]

I tried that, and it doesn't seem to work. QNumber and Level are both numeric. QNumber processes correctly, Level does not, I appreciate the input. Thanks.

 

[GunJack]

Hey Calista,

Try this

<CFQUERY NAME=&quot;RecordResults&quot; DATASOURCE=&quot;Question&quot;>
INSERT INTO ATable
( Level )
values ( 7 )
</CFQUERY>

If this doesn't work, then I suspect the problem is with your database field or possibly even corruption. If it does work, replace &quot;values (7 )&quot; with &quot;values (#form.Level# )&quot; and try again. If this doesn't work, then try outputting #form.level# just before the insert <cfquery> tag. Surround it with single quotes so you can see any possible leading or trailing spaces like this <cfoutput>'#form.level#'</cfoutput>. Either your db field is the problem or your variable &quot;level&quot; has some value which is giving the insert a problem. CF will say it's numeric even if it is a string with whitespace characters.

Hope this helps,
GJ