Solved

Dynamic inserting

Posted on 2001-06-11
20
237 Views
Last Modified: 2013-12-24
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
Comment
Question by:fremen_usul
  • 11
  • 9
20 Comments
 
LVL 4

Expert Comment

by:CF_Spike
ID: 6180929
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
 
LVL 1

Author Comment

by:fremen_usul
ID: 6181608
I will try that..

Usul
0
 
LVL 1

Author Comment

by:fremen_usul
ID: 6187153
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
Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

 
LVL 4

Expert Comment

by:CF_Spike
ID: 6187204
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
 
LVL 4

Expert Comment

by:CF_Spike
ID: 6187208
You might also need to put the word form inside ## in the collection attribute of the cfloop tag.

Spike
0
 
LVL 1

Author Comment

by:fremen_usul
ID: 6187396
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
 
LVL 4

Expert Comment

by:CF_Spike
ID: 6187422
Are you using a method of post or get on the form page?

Also which version of ColdFusion server are you using?

Spike
0
 
LVL 1

Author Comment

by:fremen_usul
ID: 6187512
Post.....

And 4.0.1
0
 
LVL 4

Expert Comment

by:CF_Spike
ID: 6187567
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
 
LVL 1

Author Comment

by:fremen_usul
ID: 6187931
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
 
LVL 4

Expert Comment

by:CF_Spike
ID: 6187945
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
 
LVL 1

Author Comment

by:fremen_usul
ID: 6187978
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
 
LVL 4

Expert Comment

by:CF_Spike
ID: 6188105
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
 
LVL 1

Author Comment

by:fremen_usul
ID: 6188153
4.5.1 Fixes this????
0
 
LVL 1

Author Comment

by:fremen_usul
ID: 6188154
If 4.5.1 fixes this.. What code should I use??

Thx,  Usul
0
 
LVL 4

Accepted Solution

by:
CF_Spike earned 50 total points
ID: 6188233
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
 
LVL 1

Author Comment

by:fremen_usul
ID: 6191976
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
 
LVL 4

Expert Comment

by:CF_Spike
ID: 6193029
No problem,

Let me know if it doesn't work.

Spike
0
 
LVL 1

Author Comment

by:fremen_usul
ID: 6222281
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
 
LVL 1

Author Comment

by:fremen_usul
ID: 7565970
Cleaning up old questions.. :-)
0

Featured Post

Create the perfect environment for any meeting

You might have a modern environment with all sorts of high-tech equipment, but what makes it worthwhile is how you seamlessly bring together the presentation with audio, video and lighting. The ATEN Control System provides integrated control and system automation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question