Multiple Checkbox Insert Handling

[Guest]

Every so often, I need to refresh my knowledge regarding how to handle multiple check box selections, and how they get handled on an action page to insert correctly in a DB.

In a helpdesk application, the admin assigns projects to technicians. He may assign one project to several technicians.

The first action page inserts the data from the form into the DB with time and date stamps, due dates, etc.

Here is where the selected checkboxes do not get passed.

The second action page depends on the fist, populating a junction table using:

<cfquery name="get_project_ID" datasource="#Request.BaseDSN#">
   SELECT MAX (project_ID) as lastID
   FROM main_projects
</cfquery>

    <cfloop index="i" list="#tech_ID#">
        <cfquery name="TheQuery" datasource="#Request.BaseDSN#">
            INSERT
            INTO     junction_project_tech
                    (junc_tech_ID, junc_project_ID)
            VALUES     (
                        <cfqueryparam cfsqltype="cf_sql_integer" value="#i#">,
                        #get_project_ID.lastID#
                    )
        </cfquery>
    </cfloop>

Any help would be greatly appriciated-

newportweb

[-__cfSearching__-]

T

The second action page

How may <form>'s are involved?

Here is where the selected checkboxes do not get passed.

What do you mean by "do not get passed"? It would help to see your form code, or at least the section dealing with checkboxes.

<cfquery name="get_project_ID" datasource="#Request.BaseDSN#">

   SELECT MAX (project_ID) as lastID
   FROM main_projects
</cfquery>

That is not a thread safe way to retrieve a newly inserted record. What is your database type?

[Guest]

Thanks for your quick reponse. I hope you don't cringe easily...

1 form:

<cfquery name="get_tech" datasource="#Request.BaseDSN#">
SELECT *
FROM lookup_tech

</cfquery>   
<form action="project_action.cfm" method="post">

<p><strong>Description:</strong></p>
<p><textarea cols="120" rows="10" name="project_desc" wrap="hard" class="inputtext"></textarea></p>

<p><strong>Assigned Technicians</strong></p>
<cfoutput query="get_tech">
    <tr>
       
<td width="75%" class="tddynamic">#tech_lname# <div class="sbfield"><strong>Due Date:</strong> <input type="text" name="due_date" size="8" class="sbfield"> </td>
<td width="25%" class="tddynamic"><INPUT Type="Checkbox" Name="tech_ID" Value="#tech_ID#"</td>
</tr>
</cfoutput>

<cfoutput>
<input type="hidden" name="assign_date" value="#DateFormat(CreateODBCDate(Now()), "mm/dd/yyyy")#">
<input type="hidden" name="assign_time" value="#TimeFormat(CreateODBCTime(Now()), "hh:mm tt")#"></cfoutput>

<input type="submit" name="" value="Submit Project" class="formbutton">

</td>
</tr></form>

Action1

<CFPARAM Name="tech_ID" Default=0>
<cfquery name="create_project" datasource="#Request.BaseDSN#">

INSERT INTO main_projects (

project_desc,

assigned,

assign_date,

assign_time,

due_date,

assigned_tech1,

assigned_tech2,

assigned_tech3,

assigned_tech4,

tech_1_hours,

tech_2_hours,

tech_3_hours,

tech_4_hours

)

VALUES  (

'#project_desc#',

1,

'#Form.assign_date#',

'#Form.assign_time#',

'#FORM.due_date#',

(insert checkbox values from form here),

0,

0,

0,

0

)</cfquery>

<cflocation url="project_action2.cfm?tech_ID=#Form.tech_ID#" addtoken="No">

Action2: You have seen.

DB: MS Access- I know, I know. I'm getting to SQL Server soon.

Thanks again....

[-__cfSearching__-]

I do not know if you have control over the structure, but a few questions first 🙂

1) Why two action pages? It seems like both inserts should happen as a single unit.  By seperating them you create the possibility of data problems. For example what would your application do if the first step completed but the second one failed?

2) If you already have a junction table, what's the purpose of these columns?

       assigned_tech1,

       assigned_tech2,

       assigned_tech3,

      assigned_tech4,

[Guest]

Hello Again

Been the one-man-band for 12 years- design, develop maintain sites and apps. Five more years = retire.

Luckily (or unluckily) I have forstalled porting 30 or so Access DB's (at my day job) and about 20 others (side work) to SQL Server.

So, yes I control the works. I gave each tech a field which I realize now is not the correct form. I know I should be querrying the junction table.

So, what would the correct, safe action page look like? Reason for 2-

1- insert form data

2- use the inserted data to populate the junction table?

Thanks again

[-__cfSearching__-]

So, what would the correct, safe action page look like?

Since you plan to upgrade, let us approach it as if you were using MS SQL now.  ( To start with, get in the habit of using cfqueryparam in all of your queries 😉  But back to the question ...

Put all the inserts queries on the same page and wrap them in a <cftransaction> to maintain data integrity. So if you are running running multiple inserts, and one of them fails, you do not have to worry about cleanup. The cftransaction will cause the queries to be treated like a single unit. ie Either they all succeed or they all fail. Nothing in between. 

Now there is still the issue of grabbing the new "project_ID". I am assuming it is an autonumber/identity column (?). With MS SQL, you could use cfquery's "result" attribute to return the new ID automatically, which.is much safer than using SELECT MAX(...). Unfortunately, that feature is not supported for MS Access. However, there is and old MS Access trick you can use within a <cftransaction>.

Directly after your insert, run a SELECT @@IDENTITY to get the last project_id value created. Then use the returned value within your junction table loop. Conceptually, something like this. Once you get things working, you can improve it by eliminating the loop.

<cftransaction>

    <!--- create project --->
    <cfquery name="create_project" datasource="#Request.BaseDSN#">
       INSERT INTO main_projects ... etc...
    </cfquery>

    <!--- grab the new record id --->
    <cfquery name="getNewProject" datasource="#Request.BaseDSN#">
       SELECT @@IDENITY AS NewProject_ID
    </cfquery>

    <!--- insert assigned techs ... --->
    <cfloop index="i" list="#form.tech_ID#">
       <cfquery name="TheQuery" datasource="#Request.BaseDSN#">
            INSERT INTO junction_project_tech ... etc...

        </cfquery>
    </cfloop>
</cftransaction>

Five more years = retire.

Hope you have a nice beach house picked out. Or whatever is your equivalent of sun, sand and worry free relaxation 😉

[Guest]

Thanks- I will try it Monday when return to my day job.

Nicaragua is all the rage these days.

[-__cfSearching__-]

Enjoy your weekend!

SELECT @@IDENITY AS NewProject_ID

Gah. Typo alert. That should be @@IDENTITY

To start with, get in the habit of using cfqueryparam in all of your queries 😉

Primarily because MS SQL is vulnerable to certain types of sql injection. Access is not. cfqueryparam helps protect queries against sql injection (among other features). So you may want to review existing applications for security issues before switching over.

Message was edited by: -==cfSearching==-