Find specific column names AND their values.

[walkeraj00]

Okay, so I'm working on this project that tracks communities and how they're
doing. The communities are judged based on their indicators. There are two
types of these "sustainable" and "essential". Originally, the code did not
make allowances for N/A's, so it just counted them as "no", which threw off the
score. I've been brought in to fix this, but I'm trying to figure out the best
way to do it.

The user opens up a community for editing, which is basically just a large
form with values pulled from the database. Then they select all of these
indicators, which are then put into the database with code like this:


<cfquery name="Update" datasource="database" dbtype="Oracle80">
UPDATE DATABASE.REPORT
SET COMMUNITY = '#Form.Community#',
AGREEMENT = '#Form.Agreement#',
AGREEMENT_DATE = '#DateFormat(Form.AgreementDate,"dd-mmm-yy")#',
AGREE_EXP_DATE = '#DateFormat(Form.AgreeExpDate,"dd-mmm-yy")#',
STAFF = '#Form.Staff#',
<!---etc--->
F_E1 = '#Form.F_E1#',
F_E2 = '#Form.F_E2#',
F_E3 = '#Form.F_E3#',
F_E5 = '#Form.F_E5#',
F_E6 = '#Form.F_E6#',
F_E4 = '#Form.F_E4#',
F_S1 = '#Form.F_S1#',
F_S2 = '#Form.F_S2#',
F_S3 = '#Form.F_S3#',
F_S4 = '#Form.F_S4#',
F_S5 = '#Form.F_S5#',
F_COMMENTS = '#Form.FinanceComments#',
A_E1 = '#Form.A_E1#',
A_E2 = '#Form.A_E2#',
A_E3 = '#Form.A_E3#',
A_E4 = '#Form.A_E4#',
A_E5 = '#Form.A_E5#',
A_E6 = '#Form.A_E6#',
A_E8 = '#Form.A_E8#',
A_S1 = '#Form.A_S1#',
A_S2 = '#Form.A_S2#',
A_S3 = '#Form.A_S3#',
A_COMMENTS = '#Form.AcctComments#',
T_E1 = '#Form.T_E1#',
T_E2 = '#Form.T_E2#',
T_E3 = '#Form.T_E3#',
T_E4 = '#Form.T_E4#',
T_COMMENTS = '#Form.TaxComments#',
<!---etc--->
WHERE COMM_ID = #Form.CommID#
</cfquery>

So, basically, the fields I'm interested in are [<letter for section>_<'E' for
essential, 'S' for sustainable><arbitrary number>].

Further complicating things, <arbitrary number> is not always in order.

So... I ask you this:

what's the best way to go through the entire database entry for one community,
look at each of the names, ensure that the second character is an underscore,
pull the character after that, and then increment a variable based on whether
its value is "yes" or "no"?

[walkeraj00]

I'm pretty sure it has something to do with query.ColumnList, am I right? Would I need to start another query to do this with the code as it is now?

[vkunirs]

Hi

If you want too get the column names then you can use the getColumns function.
This Returns an array of strings containing the names of the columns in the query.