Getting form fields

[nitai_co]

Hi all

I am coming from a different language to CF so please forgive if this is
obvious to some of you.

I need to insert data into a SQL DB and I dnot want to write the name of the
field and the values, but would like to have the code be generated dynamically.

So in the "other" language I could do something like:

>formvariables name="myform" contains="f_">

#nameoffield# #valueoffield#
</formvariables>

This would give me the fields that are named with "f_field1", "f_field2".

Now trying to do the same with CF. So I started with:

<cfloop index="LoopCount" list="#form.fieldnames#" delimiters=",">
<cfif #LoopCount# CONTAINS "f_">
#LoopCount# #form["#LoopCount#"]#
</cfif>
</cfloop>

Success, I got the name and field values too.

Ok, now I am trying to insert this into a SQl statement:

insert into mydb
(<cfloop index="LoopCount" list="#form.fieldnames#" delimiters=","><cfif
#LoopCount# CONTAINS "f_">#LoopCount#,</cfif></cfloop>)
values(<cfloop index="LoopCount" list="#form.fieldnames#" delimiters=","><cfif
#LoopCount# CONTAINS "f_">#form["#LoopCount#"]#,</cfif></cfloop>)

So this generates the SQL code dynamically. The only problem I have so far is
that I dont know how to get rid of the last "," in both loops. Also is this a
"recommended" way of dealing with form data?

Also, one more thing, is there a list of what is available inside a #form#
tag? I looked all over Macromedia documentation and found nothing.

TIA.

[mxstu]

I typically do not process entire forms this way. The reason being that it
gives you no control over the data being inserted. If a field was added to the
form, but not to the database table, the query would fail. Also, If the data
being inserted was of mixed types, it would also be difficult to format
correctly .. and to use cfqueryparam (as is recommended) because it requires a
datatype. That's just my two cents ;-)

You can use CFDUMP on your action page to display the contents of the form
structure.

<cfdump var="#form#">

[nitai_co]

Thank you for your answer.

I am just wondering why one would like to type a lot of SQL code when there
are about 40 or more fields in the DB while coding.

Of course, when you want to code the <cfqueryparam> with each insert/update
then one has to do it this way.

Another language, another way of coding, I guess :-)

[nitai_co]

Still, ist there a way to get rid of the last "," iny my original post?

TIA.

[mxstu]

There are a few ways to do it. Here is one method ...

Also, the posted code contain a lot of extra pound signs where they are not
needed. You could actually change this statement

<cfif #LoopCount# CONTAINS "f_">
#LoopCount# #form["#LoopCount#"]#
</cfif>

to

<cfif LoopCount CONTAINS "f_">
#LoopCount# #form[LoopCount]#
</cfif>



<!--- I believe you're just looking for fields that start with "f_" --->
<!--- so I replaced the CONTAINS check with a substring EQ check --->
<cfloop from="1" to="#ListLen(form.fieldnames)#" index="i">
<cfset currField = ListGetAt(form.fieldNames, i)>
<cfif Left(currField, 2) eq "f_">
#currField#
<cfif i neq ListLen(form.fieldNames)>,</cfif>
</cfif>
</cfloop>

[mxstu]

I am just wondering why one would like to type a lot of SQL code when there are
about 40 or more fields in the DB while coding.

In many cases there is no way around it. I've rarely inserted into a table
with more than 20 columns, where all of the columns had the same data types.
Imagine a dynamic insert where some of the values are varchars and must have
single quotes while others are numeric and do not need single quotes, and
others are dates that must be handled correctly with CreateODBCDate()
functions, etc... it can get a bit kludgey. IMO it's not worth it ;-)