Inserting Dynamic Form Fields into Database

I am trying to write records to a database using variables to define the column and row information. Here is the code and the error message gernerated. Let me know if I need to further elaborate on what is happening. thanks

<Cfif isdefined('sched')>
<cfquery name="getFormNames" datasource="ssscontactlist">
select * from tempFormNames
</cfquery>
<cfset columns = 'weekBeginDate,opco,region,name,mon,tue,wed,thur,fri,sat,sun'>
<cfloop query="getFormNames">
<cfquery name="postSched" datasource="ssscontactlist">
INSERT INTO techsched(#columns#)
values(#now()#,"#formNames#")
</cfquery>

</cfloop>


</cfif>


Error message

Error Executing Database Query.
General error: Column count doesn&apos;t match value count at row 1

The error occurred in action.cfm: line 9

7 : <cfquery name="postSched" datasource="ssscontactlist">
8 : INSERT INTO techsched(#columns#)
9 : values(#now()#,"#formNames#")
10 : </cfquery>
11 :



SQL INSERT INTO techsched(weekBeginDate,opco,region,name,mon,tue,wed,thur,fri,sat,sun) values({ts '2004-12-24 10:08:55'},"Giant'', ''giant-c field tech south'', ''#form.OCjones#'', ''#form.Mgiant-c field tech south#'', ''#form.Tgiant-c field tech south#'', ''#form.Wgiant-c field tech south#'', ''#form.THgiant-c field tech south#'', ''#form.Fgiant-c field tech south#'', ''#form.Sgiant-c field tech south#'', ''#form.SUgiant-c field tech south#")

This is the problem:

values(#now()#,"#formNames#")

Whatever "formNames" is, it isn't a list that has the same data types and number of elements that you have in your "columns" variable that is feeding the INSERT INTO statement.

If you want to use form fields, you'll either need to loop over them and output each one, something like this:

values( #now()# <cfloop list="#form.fieldNames#" index="thisField">, '#form[thisField]#'</cfloop>

(note that this assumes all the values are strings...if they aren't you'll need to check whether to put the single quotes around the variable or not)

or explicitly declare them like this:

values( #now()#, '#form["firstName"]#', '#form["lastName"]#'....etc....)

or

values( #now()#, '#form.firstName#', '#form.lastName#'....etc....)

thanks for the good information.....there were actually more problems with that code and I have corrected them. I am now having this problem what do you suggest?

here is the code...

<cfquery name="postSched" datasource="ssscontactlist">
10 : INSERT INTO techsched(#columns#)
11 : values('#DateFormat(Now(),"yyyy-mm-dd")# #TimeFormat(Now(),"h:m:s tt")#',#formNames#)
12 : </cfquery>

#formNames# is a string containing .......

'Giant','South','#form.OCbaxter#', '#form.MSouth#', '#form.TSouth#', '#form.WSouth#', '#form.THSouth#', '#form.FSouth#', '#form.SSouth#', '#form.SUSouth#'


But I get the following error....

Error Executing Database Query.
Syntax error or access violation: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near &apos;Giant&apos;&apos;,&apos;&apos;South&apos;&apos;,&apos;&apos;#form.OCbaxter#&apos;&apos;, &apos;&apos;#form.MSouth#&apos;&apos;,

The error occurred in E:\webnet\webportal\uploads\ssscontactlist\OnCallTech\sched2\action.cfm: line 11

9 : <cfquery name="postSched" datasource="ssscontactlist">
10 : INSERT INTO techsched(#columns#)
11 : values('#DateFormat(Now(),"yyyy-mm-dd")# #TimeFormat(Now(),"h:m:s tt")#',#formNames#)
12 : </cfquery>
13 :


Here is what cf is inserting into the database....

INSERT INTO techsched(weekBeginDate,opco,region,name,mon,tue,wed,thur,fri,sat,sun) values('2004-12-26 9:18:54 AM',''Giant'',''South'',''#form.OCbaxter#'', ''#form.MSouth#'', ''#form.TSouth#'', ''#form.WSouth#'', ''#form.THSouth#'', ''#form.FSouth#'', ''#form.SSouth#'', ''#form.SUSouth#'')

Notice the string now has double quotes instead of single that are in the string that was passed

Do you know how to prevent this from happening?

Thanks

Do the values in your form have single quotes in the strings which are causing the double quotes? If not then it looks like a MySQL JDBC driver problem because this definitely does not happen with the Oracle, SQL Server, or Access JDBC drivers.

The string I am passing contains single quotes and looks exactly like this....
'Giant','South','#form.OCbaxter#',.....

I am going to try alternative methods.....I am working with #from.fieldnames# now but I am getting weird values returned.....for some reason the first 24 field values that are returned are duplicated....for example...MHEISEY = Off,working should only be OFF....these values are from a drop down box in the form and contain working, off, and on call. The remaining 124 fields return the correct values....if i explicitly pass the form field values they are 100% correct....thank you

Try using the PreserveSingleQuotes() function. That will keep your single quotes from getting escaped (with single quotes).

that prevented the single quotes from getting escaped.....now the string is not being evaluated by cf and is written to the database as is. any ideas?

You can't pass CF a string like this:

'Giant','South','#form.OCbaxter#',.....

And expect it to evaluate "form.OCbaxter" as a CF variable. You can try wrapping the whole thing in an evaluate() function. Remember that evaluate() is very slow though (in any language) so if you can avoid using it, you should.

thanks for the info...i was already working on another way to handle this....it does require that I use the evaluate() function when inserting to the database....at least that has been the only way i can get the value of the fields from the submitted form.....I don't forsee the slowness of the evaluate function causing any problems at this point but I may reconsider once I get it fully functional........

currently, I am getting all the field values inserted to my DB exactly how I want them but the first several fields are returning duplicate values...... see example below....the remaining fields all have the correct values.....my fields are all generated from the one <cfloop> and then a nested <cfoutput>......not sure what would cause this to happen

MHEISEY = working,working
THEISEY = working,working
WHEISEY = working,working
THHEISEY = working,working
FHEISEY = working,working
SHEISEY = Off,Off
SUHEISEY = Off,Off
MARVELO-CRUZ = working,working
TARVELO-CRUZ = working,working
WARVELO-CRUZ = working,working
THARVELO-CRUZ = working,working
FARVELO-CRUZ = working,working
SARVELO-CRUZ = Off,Off
SUARVELO-CRUZ = Off,Off
MTHOMSEN = working,working
TTHOMSEN = working,working
WTHOMSEN = working,working
THTHOMSEN = working,working
FTHOMSEN = working,working
STHOMSEN = Off,Off
SUTHOMSEN = Off,Off

I don't see what is duplicated.

MHEISEY = working,working
THEISEY = working,working
WHEISEY = working,working
THHEISEY = working,working
FHEISEY = working,working
SHEISEY = Off,Off
SUHEISEY = Off,Off
MARVELO-CRUZ = working,working
TARVELO-CRUZ = working,working
WARVELO-CRUZ = working,working
THARVELO-CRUZ = working,working
FARVELO-CRUZ = working,working
SARVELO-CRUZ = Off,Off
SUARVELO-CRUZ = Off,Off
MTHOMSEN = working,working
TTHOMSEN = working,working
WTHOMSEN = working,working
THTHOMSEN = working,working
FTHOMSEN = working,working
STHOMSEN = Off,Off
SUTHOMSEN = Off,Off



should be like......

MJONES = working
TJONES = working
WJONES = working
THJONES = working
FJONES = working
SJONES = Off
SUJONES = Off
MLINDEN = working
TLINDEN = working
WLINDEN = working
THLINDEN = working
FLINDEN = working
SLINDEN = Off
SULINDEN = Off
MSTOLNIS = working
TSTOLNIS = working
WSTOLNIS = working
THSTOLNIS = working
FSTOLNIS = working
SSTOLNIS = Off
SUSTOLNIS = Off
OCJONES = On Call
MEASTERNPA = jones

i found the mistake....lol...i had the technician in the database twice so therefore it generated multiple fields with the same names.....fixed.....thanks for all your help....i may have some more questions later....