Multiple database inserts with 1 form

I am setting up a survey web site where one group of people can create survey questions and then select which questions to put on their survey. I want to be able to show all of their questions on one page with check boxes (already done) and then submit all of the questions they selected to the survey table. So there would be one record submitted for every question they select
The survey table is named surveytbl and it has the following fields: sid (survey id an autonumber field), surveyname, questid (question ID), deptid (department ID), surveydate.  I use #database# to dynamicaly identify my database.
I also want to do the same thing when people fill out the survey it will show all the questions for the survey on one page but when the questions get answered each answer will get put into a table with the question ID, the user ID, the survey ID and of course the answer.
I think the same soulution with work for both I just don't know where to start.  I use Coldfusion and html, I don't really use javascript or anything like that.
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.

You might try useing a <cfloop> or a counter (<cfset qestionNum = qestionNum + 1>) then use this questionNum to name the fields.

Are they going to be able to have different formats?
if so then you will need to send data about what format, multiple choice or text...
have hidden fields at the top of the form that sends the sid, and uid.  then you can use the sid, and uid over and over again in yourinsert statments.

I assume you have the servey questions in a table.  you will need to make sure they can't modify a survey after someone has taken it, because then the questions could be out of order, or different.

I would have completely different templates to use to allow the users to create questions, and add them to survey defenitions.
You might mark the survey definitions with complete flags, so they won't be taken till the user has entered them exactly as they want.

Hope that is what you were asking, if not please add more detail, and witch part you are on.
shanksrAuthor Commented:
The questions can be different formats but in all cases every question only has one answer so the format doesn't matter at that point. Currently I have this to display my questions but I don't know what to with it.
<CFSET i = i+1>
                  <TD WIDTH="25"><DIV ALIGN="RIGHT">
                        <INPUT TYPE="hidden" NAME="qnumber" VALUE="#i#">
                        <INPUT TYPE="HIDDEN" NAME="qid" VALUE="#qid#">
                        <INPUT TYPE="checkbox" NAME="checkbox#i#" VALUE="checkbox">
                  <TD WIDTH="79">#qtype#</TD>
                  <TD NOWRAP>#qquestion#</TD>
shanksrAuthor Commented:
Also, yes I have a questions table (questiontbl) and a survey table (surveytbl) and an answer table (answertbl) as well as other tables to hold user information and what not...
The questiontbl has qid (question ID) and qtype (for the type) and qquestion (for the actuall question) as well as other fields related to question specifics to facilitate different question types... multiple choice, range questions and so on.
The surveytbl has sid, surveyname, questid (relational to questiontbl.qid), deptid (so I know who created it), and surveydate
The answertbl has aid, qid (for the question it is the answer to, relational to questiontbl.qid), answer, adate, and alumniid (the person who is answering the question).

I don't know how to process multiple "records" on one submission. I think that is my problem here.
I do realize that I could have them submit the questions one at a time but I think this is a better way of doing it.
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

try sending the number of questions, then loop through a cfloop that number of times with a insert each time.
shanksrAuthor Commented:
If I am displaying 20 questions for instance and they only want 5 questions on the survey how do I know they only selected 5 questions?
shanksrAuthor Commented:
Moderator, can you close this question and restore my point?  I was able to figure it out on my own.

We can close the question, but your points will be refunded only if you post the solution that you used
shanksrAuthor Commented:
When I created the survey, I displayed all the questions with a CFOUTPUT and with a check box <input type="checkbox" name="question" value="#questionID#>. Then when they submit the questions for the survey all the question ID's are put into one field like this qid="3,5,12,32". Then when I want to display the survey I would loop through the qid field to display the list of questions. Then I display the questions I set the answer <input type="(depends on the question)" name="answer#qid#"> the value will be what the user chooses. This then gets submitted to the database, however it is not that easy to dynamically submit a list like this to a database. What I had to do is use a ColdFusion fuction named evaluate, the whole submission thing looks like this.
<CFSET myList = #form.questionlist#>
        <CFLOOP INDEX="i" LIST="#myList#" DELIMITERS=",">
      <CFIF isdefined("Form.answer#i#")>
                    <CFSET answer = evaluate("form.answer#i#")>
                     <CFSET alumniid =>
            <CFSET qid = i>
            <CFSET sid = #form.sid#>
            <CFQUERY NAME="addanswer" DATASOURCE="#database#">
                  INSERT INTO answertbl (qid, answer, alumniid, asurveyid)
                  VALUES ('#qid#', '#answer#', '#alumniid#', '#sid#')

That is it!  The real tricky part was the evaluate fuction which I found here on Experts Exchange!!!! You just have to know exactly what to look for (this is usually the problem).

Thanks to everyone who helped.
Thanks for posting your solution, as a note although it is not the best way to be doing things.

Really what you should be doing instead of evaluate is the following:

Instead of <CFSET answer = evaluate("form.answer#i#")>

Use <CFSET answer = Form['answer' & i]>

This will be significantly more efficient .

Also anywhere that you have # inside a cftag (other than an attribute i.e. inside double quotes) it should be removed.

<CFSET myList = #form.questionlist#>  should be <CFSET myList = form.questionlist>
<CFSET sid = #form.sid#> should be <CFSET sid = form.sid>

BUT this one should keep the # <CFQUERY NAME="addanswer" DATASOURCE="#database#">

Finally you shol\uld use cfqueryparams when inserting into the databse.

THese things will make your code more secure and run faster and more efficiently.

To get your points refunded you need to post here:
PAQ'd, 200 points refunded.
Community Support Moderator

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
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.