• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

Dynamic inserting

Here is a form that I am dynamically creating.

<cfquery name="getquesans" datasource="parser">
SELECT tblanswers.answerid, tblanswers.answer, tblanswers.questionid, tblquestions.questionid, tblquestions.question, tblquestions.dealer_id, tblquestions.jobid
FROM tblanswers INNER JOIN tblquestions ON tblanswers.questionid = tblquestions.questionid
where tblquestions.jobid = #session.survey_id#
</cfquery>
<cfquery name="getquestions" datasource="parser">
SELECT *
FROM tblquestions
where jobid = #session.survey_id#
</cfquery>

  <!--- --->  <cfform name="form" action="action_survey1.cfm" method="post">
            <table cellpadding="1" cellspacing="1" bgcolor="Silver" width="55%"><tr><td>
            <table cellpadding="4" cellspacing="1" bgcolor="white" width="100%">
            <tr>                    
              <td style="font-family: Verdana; font-size: xx-small;">
               <cfoutput><input type="hidden" name="total" value="#getquestions.recordcount#"></cfoutput>
               
               <!--- --->
               <cfloop query="getquestions">
                    <cfoutput>
                         #question#<br>
                    <cfset quesid = #questionid#>
                         <!--- --->
                         <cfloop query="getquesans">
                              <!------>
                              <cfif quesid is #questionid#>
                                   &nbsp;<input type="radio" name="#questionid#" value="#answerid#">#answer#<br>
                              </cfif>
                         </cfloop>  
                    </cfoutput>
               </cfloop>

     <div align="center"><input type="submit" value="Submit Survey"><br><br></div>

Then the action1 page...

<cfquery name="getquestions" datasource="parser">
SELECT *
FROM tblquestions
where jobid = #session.survey_id#
</cfquery>
<!--- HERE'S WHAT WE WANT TO INSERT INTO THE DATABASE --->

<cfloop query="getquestions">
     <cfquery name="survey_insert" datasource="parser">
     INSERT INTO tblrelated ([userid],  [answerid], [questionid], [surveyid])
     VALUES ('#trim(session.user_id)#', '#trim(form.questionid)#', '#trim(questionid)#', '#trim(session.survey_id)#')
     </cfquery>
</cfloop>

Now I can insert everything but the answer. I cant figure out a way to have the FORM varable totally dynamic. ANy help?

Thx,

Usul
0
fremen_usul
Asked:
fremen_usul
  • 11
  • 9
1 Solution
 
CF_SpikeCommented:
Something like this should work. Don't bother with the select query on the action page.

<!--- First remove all the form fields that aren't questions (you may need to put the form in single quotes, I can't remember off the top of my head) --->
<CFSCRIPT>
     StructDelete(Form,fieldnames);
     StructDelete(Form,'total');
</CFSCRIPT>

<!--- Loop over the form structure inserting the relevant question and answer --->
<CFLOOP COLLECTION="form" ITEM="i">

     
    <cfquery name="survey_insert" datasource="parser">
         INSERT INTO tblrelated ([userid],  [answerid], [questionid], [surveyid])
             VALUES ('#trim(session.user_id)#', '#trim(form[i])#', '#trim(i)#', '#trim(session.survey_id)#')
    </cfquery>


</CFLOOP>

Spike
0
 
fremen_usulAuthor Commented:
I will try that..

Usul
0
 
fremen_usulAuthor Commented:
Here is my error...

Error Occurred While Processing Request
Error Diagnostic Information
Error resolving parameter FORM


ColdFusion was unable to determine the value of the parameter. This problem is very likely due to the fact that either:

You have misspelled the parameter name, or
You have not specified a QUERY attribute for a CFOUTPUT, CFMAIL, or CFTABLE tag.

The error occurred while evaluating the expression:


    StructDelete(Form,fieldnames);
    StructDelete(Form,'total');





The error occurred while processing an element with a general identifier of (CFSCRIPT), occupying document position (23:1) to (23:10) in the template file F:\employees\mlubrano\911event\action_survey1.cfm

The specific sequence of files included or processed is:
F:\EMPLOYEES\MLUBRANO\911EVENT\ACTION_SURVEY1.CFM      .
 

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
CF_SpikeCommented:
You need to put the word form in quotes. Like this:

<CFSCRIPT>
    StructDelete('Form',fieldnames);
    StructDelete('Form','total');
</CFSCRIPT>

<!--- Loop over the form structure inserting the relevant question and answer --->
<CFLOOP COLLECTION="form" ITEM="i">

   
   <cfquery name="survey_insert" datasource="parser">
        INSERT INTO tblrelated ([userid],  [answerid], [questionid], [surveyid])
            VALUES ('#trim(session.user_id)#', '#trim(form[i])#', '#trim(i)#', '#trim(session.survey_id)#')
   </cfquery>

Spike
0
 
CF_SpikeCommented:
You might also need to put the word form inside ## in the collection attribute of the cfloop tag.

Spike
0
 
fremen_usulAuthor Commented:
Now here is the error...

Error Diagnostic Information
Cannot convert given value to a struct. Please check the validity of the expression

The error occurred while evaluating the expression:


   StructDelete('Form',fieldnames);
   StructDelete('Form','total');





The error occurred while processing an element with a general identifier of (CFSCRIPT), occupying document position (23:1) to (23:10) in the template file F:\employees\mlubrano\911event\action_survey1.cfm

The specific sequence of files included or processed is:
F:\EMPLOYEES\MLUBRANO\911EVENT\ACTION_SURVEY1.CFM      .


Date/Time: 06/13/01 12:14:34
 
0
 
CF_SpikeCommented:
Are you using a method of post or get on the form page?

Also which version of ColdFusion server are you using?

Spike
0
 
fremen_usulAuthor Commented:
Post.....

And 4.0.1
0
 
CF_SpikeCommented:
The problem with the code I gave you is that it relies on the form being a structure. I think this only happened from 4.5 onwards.

The following code should work instead:



<!--- Loop over the form.fieldnames list inserting the relevant question and answer --->
<CFLOOP LIST="#form.fieldnames#" INDEX="i">
<!--- this is a list of excluded form fields which
you don't want to add to the db --->
<CFSET exclusionlist = "total">

<CFSET thisField = i>
<CFSET thisValue = Evaluate('form.' & i)>
   <cfquery name="survey_insert" datasource="parser">
        INSERT INTO tblrelated ([userid],  [answerid], [questionid], [surveyid])
            VALUES ('#trim(session.user_id)#', '#trim(thisValue)', '#trim(thisField)#', '#trim(session.survey_id)#')
   </cfquery>


</CFLOOP>

Spike
0
 
fremen_usulAuthor Commented:
Here is the error...

Oone thig I noticed, was it was inserting the word "Total" in the database. All the database field are "number" datatypes..

ODBC Error Code = 07001 (Wrong number of parameters)


[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Hint: The cause of this error is usually that your query contains a reference to a field which does not exist. You should verify that the fields included in your query exist and that you have specified their names correctly.

SQL = "INSERT INTO tblrelated ([userid], [answerid], [questionid], [surveyid]) VALUES (3, 3, TOTAL, 9)"

Data Source = "parser"

SQL = "INSERT INTO tblrelated ([userid], [answerid], [questionid], [surveyid]) VALUES (3, 3, TOTAL, 9)"


The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (45:3) to (45:52) in the template file F:\employees\mlubrano\911event\action_survey1.cfm
0
 
CF_SpikeCommented:
My mistake,

I forgot to use the exclusion list paramater that i set-up.

<!--- Loop over the form.fieldnames list inserting the relevant question and answer --->
<CFLOOP LIST="#form.fieldnames#" INDEX="i">
<!--- this is a list of excluded form fields which
you don't want to add to the db --->
<CFSET exclusionlist = "total">
<CFIF NOT ListFindNoCase(exclustionlist,i)>

<CFSET thisField = i>
<CFSET thisValue = Evaluate('form.' & i)>
  <cfquery name="survey_insert" datasource="parser">
       INSERT INTO tblrelated ([userid],  [answerid], [questionid], [surveyid])
           VALUES ('#trim(session.user_id)#', '#trim(thisValue)', '#trim(thisField)#', '#trim(session.survey_id)#')
  </cfquery>

</CFIF>

</CFLOOP>

Spike
0
 
fremen_usulAuthor Commented:
Error Diagnostic Information
An error has occurred while processing the expression:

   form.18



--------------------------------------------------------------------------------

Invalid parser construct found on line 1 at position 5. ColdFusion was looking at the following text:

.
Invalid expression format. The usual cause is an error in the expression structure.

The error occurred while evaluating the expression:


 thisValue = Evaluate('form.' & i)




The error occurred while processing an element with a general identifier of (CFSET), occupying document position (46:1) to (46:41) in the template file F:\employees\mlubrano\911event\action_survey1.cfm

The specific sequence of files included or processed is:
F:\EMPLOYEES\MLUBRANO\911EVENT\ACTION_SURVEY1.CFM      
 
0
 
CF_SpikeCommented:
It appears that the form has fields which have numbers for names. This will be difficult to deal with in CF 4.01.Try prefixing the name of the fields with id_ or something like that if they are numbers.

Spike
0
 
fremen_usulAuthor Commented:
4.5.1 Fixes this????
0
 
fremen_usulAuthor Commented:
If 4.5.1 fixes this.. What code should I use??

Thx,  Usul
0
 
CF_SpikeCommented:
If you can use 4.5x instead then the original code I gave you should work.

<CFSCRIPT>
   StructDelete('Form',fieldnames);
   StructDelete('Form','total');
</CFSCRIPT>

<!--- Loop over the form structure inserting the relevant question and answer --->
<CFLOOP COLLECTION="#form#" ITEM="i">

   
  <cfquery name="survey_insert" datasource="parser">
       INSERT INTO tblrelated ([userid],  [answerid], [questionid], [surveyid])
           VALUES ('#trim(session.user_id)#', '#trim(form[i])#', '#trim(i)#', '#trim(session.survey_id)#')
  </cfquery>
0
 
fremen_usulAuthor Commented:
I will be moving the files over the a 4.5 server this weekend. I will test and if correct I will add another 50 points for a total of 100... :-)

Thanks,

Usul
0
 
CF_SpikeCommented:
No problem,

Let me know if it doesn't work.

Spike
0
 
fremen_usulAuthor Commented:
Tested the code and give a error...

Error Diagnostic Information

An error occurred while evaluating the expression:


   StructDelete('Form',fieldnames);
   StructDelete('Form','total');




Error near line 24, column 4.
--------------------------------------------------------------------------------

Cannot convert given value to a struct. Please check the validity of the expression


The error occurred while processing an element with a general identifier of (CFSCRIPT), occupying document position (23:11) to (23:20) in the template file D:\911event\action_survey1.cfm.


Date/Time: 06/23/01 22:21:57

0
 
fremen_usulAuthor Commented:
Cleaning up old questions.. :-)
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 11
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now