0

I'm coding this new feature for a client. I need to give a user rights to the following:

  • A District
  • An operation center
  • Projects in that ops center

Here is a screenshot of my add_a_user page that shows the district > Ops Center > projects layout:

enter image description here

There are currently 8 districts in my district table, but only "1" and "4" have ops centers right now. That is why you only see those in this screenshot. So there can be more than 1 ops center, in each district, and many projects tied to an individual ops center.

So my table structure in MS SQL server is like this:

  • Districts / Columns:

    • [district_id]
    • [district_name]

  • Operation_centers / Columns:

    • operation_center_id
    • operation_center_name
    • oc_district_id (links this ops center to the district it belongs to)

  • Projects / Columns:

    • project_name_id
    • project_name
    • project_oc_id (links the project to the ops center it belongs to)

I also have 3 other tables that store the rights for the users:

  • district_access

    • district_access_id (district access id)
    • district_user_nm (user who has right to this district)
    • district_role (holds the value for their role - Admin,manager,editor,user)

  • ops_access / Columns:

    • ops_access_id (ops center access id)
    • ops_user_nm (user who has access to this ops center)

  • Project_access / Columns:

    • project_access_id (project access id)
    • project_user_nm (user who has access to this project)

I named the radio buttons dynamically like this:

f_district_access_#district_id# (district access)
f_da_#district_id# (district role)
f_ops_#operation_center_id# (ops center access)
f_projects_#getdistricts.project_name_id# (project access)

On my action page I'm trying to use this cfloop (and I do a cfloop for the ops centers and project also):

<CFQUERY  datasource="#datasource#" name="get_district_count">
    SELECT MAX(district_id) AS DC
    FROM   districts
</CFQUERY>
<cfset DC = get_district_count.DC>
<!-- Insert district access-->
<CFLOOP from="1" to="#DC#" index="dis">
    <cfset districtname = "f_district_access_" & dis>
    <cfif structKeyExists(form,"#districtname#")>
        <cfquery datasource="#datasource#" name="submit_districts">
            INSERT into district_access
            (
                district_access_id,
                district_user_nm,
                district_role
            )
            Values(
                #form["f_district_access_" & dis]#,
                #NEWID#,
                #form["f_da_" & dis]#
            )
        </cfquery>
    </cfif>
</CFLOOP>

Now I'm getting this error:

Element F_DA_4 is undefined in a Java object of type class coldfusion.filter.FormScope.

So my questions are, am I over complicating this and how can I simplify this?

Community
  • 1
  • 1
sartis
  • 47
  • 7
  • If you can help it, you'd be better off using a loop to build a bulk insert rather than looping and inserting for every record. Fewer connections to your database will always be good. – Shawn Jan 14 '19 at 21:02
  • That said, you may not want to do the looping like you are. If you have a user who is in District 14, and nothing else, you'll do 14 loops before you even get to his district. There's also a lot of injection potential in that query and loop. I'll take a look at the query. – Shawn Jan 14 '19 at 21:07
  • The error above is due to the fact that the code uses structKeyExists() to verifies "District" radio button exists - BUT ... doesn't do the same for the district access buttons i.e. F_DA_X. That said, I agree the action page should be more selective. Rather than using a query to find out the max id available, the action page should only process the id's available in the form. – SOS Jan 14 '19 at 21:23
  • What version of SQL Server? – Shawn Jan 14 '19 at 21:54
  • @shawn....MS SQL Server 2017 – sartis Jan 14 '19 at 21:56
  • 1
    Consider something like FormUtils. https://coldfusion.adobe.com/2018/10/make-form-processing-simpler-with-brian-koteks-formutils/ (Disclaimer, I didn't write the original, but I recently created a more modern version) – James A Mohler Jan 15 '19 at 20:04

0 Answers0