Solved

Dynamic inserting

Posted on 2001-06-11
20
230 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
 
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
Network it in WD Red

There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now