Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Dynamic inserting

Posted on 2001-06-11
20
Medium Priority
?
249 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Understanding Web Applications

Without even knowing it, most of us are using web applications on a daily basis. Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We often confuse these web applications tools for websites.  So, what is the difference?

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

Simplify Your Workload with One Tool

How do you combat today’s intelligent hacker while managing multiple domains and platforms? By simplifying your workload with one tool. With Lunarpages hosting through Plesk Onyx, you can:

Automate SSL generation and installation with two clicks
Experience total server control

Question has a verified solution.

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

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

610 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