Solved

Dynamic inserting

Posted on 2001-06-11
20
232 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
cloud web Service looking for a home... 3 100
Using Route 53, Record Sets & Health Checks 2 Node Exchange 2016 environment 2 97
Problem to Eclipse 16 118
Problem to get function 52 100
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…
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 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

14 Experts available now in Live!

Get 1:1 Help Now