Urgent ColdFusion and Access Question

I've had this question posted for 2 days now!  Can anyone answer it?!?!?!?!?!?


I have a form that allows users to make multiple selections using checkboxes.  There are about 100 different options to select from.  The problem is that I want each selection to be inserted as individual entries in a MS Access database I've created so that I can use the count function to determine how many times each item is selected.  The columns in the database are ID (set to Auto number) and Checklist.  The form on my web page looks similar to the one below:

<cfform action="checklist_process.cfm" method="post">
      <td class="text" valign="top"><br>
         <input type="checkbox" name="checklist" value="Abuse">Abuse<br>
         <input type="checkbox" name="checklist" value="Abandonment">Abandonment<br>
         <input type="checkbox" name="checklist" value="Addiction">Addiction<br>
         <input type="checkbox" name="checklist" value="Adversity">Adversity<br>
       ( ........etc.)

On my checklist_process.cfm page, I have the simple code below:

   <cfinsert datasource="checklist" tablename="checklist">

But when more than one selection is made, the selections are inserted in the database as one entry separated by commas like below:

   1     Abuse,Addiction,Adversity

However, I need each selection to be inserted into it's "own row" as below:

   1     Abuse
   2     Addiction
   3     Adversity

How can I accomplish this task.  I've been trying to figure it out for weeks, but have not been successful :(  I need an answer QUICK because I have to finish this project for one of my clients by tomorrow!!!!!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ok, now that you've suffered enough... just kidding  ;)

OK, you have the form working and you understand that the returned form variable is returning a list of values seperated by commas... so, you just wrap the insert statement inside a loop that processes the list.  I haven't tested this code, I'm just typing it in the woindow here, you'll need to tweek it. If you need help debugging it, let me know.

<!--- ensure the variable exists so the loop doesn't fail due to missing variable --->
<cfparam name="checklist" default="">

<!--- loop through the list of values in the form variable --->
<cfloop index="curval" list="checklist">
 <!--- do the actual inserts using the cfquery operator, not cfinsert --->
 <cfquery datasource="your_datasource_name">
   insert into your_table_name ( your_column_name) values
    (<cfqueryparam value="#curval#">)
I, and many other ColdFusion experts don't necessarily monitor the web development TA...lot's of questions there I have no expertise in. You may want to post in the support area to close your original question:
lloweryAuthor Commented:
What is "curval"?  I'm getting an error at (<cfqueryparam value="#curval#">).
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

You can use it inside the loop. In the cfloop definition, the index is specified. During the loop, you should be able to access that variable.  Is it a database error? Can you post the error message?
Hello Llowery,

RCorfman is right. I modified his code a little. Check to see that it works for you.

<cfparam name="form.checklist" default="">
<cfloop index="checkboxes" list="#form.checklist#">
<cfquery name="rsQuery" datasource="YourDatasource">
INSERT INTO YourTable (YourColumn)
VALUES (<cfqueryparam value="#checkboxes#">)


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Well, that was helpfull Guzz11, removing the coments maybe isn't a good thing, but I guess uncommented code is ok in some circles.  

I do agree that adding the "form." qualifier to the variables isn't a bad thing, it does ensure the user couldn't override the values in the URL.  If you leave it unqualified, which I did, the user could call the processing page with processingform.cfm?checklist=processthis and it would be picked up instead of the form's variable, (which actually wouldn't be there).

The reality is that sometimes, I code with the unqualified version, but not typically with a form that does updates. The reason's I use it is that many of my forms do graphing. I have a post form normally from the form where the users can select options and data they want posted. I then also have a little ditty of code that builds up a url and a link back to the form with all the attributes listed in GET format so that they have a bookmarkable URL to a chart in that exact style.

Demonstrating the potential goodness of qualifying the variable as form.checklist was a good thing, stripping out the comments... well, I think that isn't so good.
lloweryAuthor Commented:
Thanks!  I'm going to give Guzz11's code a shot.
lloweryAuthor Commented:
Well Guzz11, your modified code worked perfectly.  I do have to do a LITTLE tweeking because the hosting service that I am using no longer uses datasource names in their query on their new MX 7 servers.  They give some sample code to remedy this change so we'll see!  Hopefully, now that I have it working on their old platform, I will be able to make a smooth transition to the new platform.

Is there a way to split the points since both you and RCorfman assisted me in solving this problem?
Yes, you can split the points.

Notice above where you would put in comments, there is an option to 'split points'. You can pick that then allocate the points as apprpriate.

Here is some help on how to grade appropriately.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.