need help looping a query to insert data

Discussion in 'Programming' started by dcttack, Jun 13, 2009.

0
  1. #1
    I need to loop a query and insert into a data base.

    I am new to colfusion and am not totally certian this is the best approach

    First I have dynamicly generated a form of options (this is from a database query)

    Then I need to insert thre values into a new table

    values
    modelid
    optionid
    optionassigned

    This is the code that I have so far

    Queries

    <cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">

    <cfloop index="variables.cnt" from="1" to="#final_loop_cnt#">
    <cfset variables.OptionID = Evaluate("form.OptionID#variables.cnt#")>
    <cfset variables.OptionAssigned = Evaluate("form.OptionAssigned#variables.cnt#")>

    <CFQUERY datasource="#Request.DSN#">
    INSERT options_assigned
    (ModelID, OptionID, OptionAssigned)
    values (#FORM.ModelID#, 'evaluate(OptionID#variables.cnt#)', #FORM.OptionAssigned#)
    </CFQUERY>
    </CFLOOP>

    </cfif>

    <cfquery name="qModelList" datasource="#Request.DSN#">
    SELECT *
    FROM models
    </cfquery>
    <cfquery name="qOptionsValues" datasource="#Request.DSN#">
    SELECT *
    FROM options
    </cfquery>
    <cfquery name="qModelsAddOptions" datasource="#Request.DSN#">
    SELECT *
    FROM models
    WHERE ModelName = '#URL.ModelName#'
    </cfquery>


    Form

    <form name="form1" id="form1" method="post">
    <table border="0" cellpadding="0" cellspacing="0">
    <cfoutput query="qOptionsValues">
    <tr>
    <td>#qOptionsValues.OptionID#</td>
    <td>#qOptionsValues.OptionName#</td>
    <td><input type="radio" name="OptionAssigned#variables.cnt#" value="1">
    standard
    <input type="radio" name="OptionAssigned#variables.cnt#" value="2">
    optional&nbsp;
    <input type="radio" name="OptionAssigned#variables.cnt#" value="3">
    N/A
    <td>&nbsp;</td>
    </tr>
    </cfoutput>
    </table>
    <input name="submit" type="submit" value="Add Equipment" />
    <input type="hidden" name="ModelID" value="<cfoutput>#qModelsAddOptions.ModelId#</cfoutput>" />
    <input type="hidden" name="MM_InsertRecord" value="form1" />
    </form>

    I would like to have radio groups but check boxes would work

    Could someone please help I may be over thinking this or looking in the wrong direction What am I missing?

    Thank you
     
    dcttack, Jun 13, 2009 IP
  2. d_dimarco

    d_dimarco Peon

    Messages:
    17
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Do you still need a solution to this?
     
    d_dimarco, Jun 17, 2009 IP
  3. dcttack

    dcttack Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Yes I have been trying for days with no luck getting it to work. It would be great if you have any suggestions
     
    dcttack, Jun 17, 2009 IP
  4. d_dimarco

    d_dimarco Peon

    Messages:
    17
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    I'll take a look at it. It appears you are missing the variable hashes among other things.
     
    d_dimarco, Jun 18, 2009 IP
  5. d_dimarco

    d_dimarco Peon

    Messages:
    17
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    <cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">



    <cfloop index="i" from="1" to="#form.count#">
    <cfset optionID = "optionID#i#">
    <cfset optionAssigned = "optionAssigned#i#">

    <CFQUERY name="option_insert" datasource="#request.dsn#">
    INSERT into options_assigned
    (ModelID, OptionID, OptionAssigned)
    values (#FORM.ModelID#,#evaluate(optionID)#,#evaluate(optionAssigned)#)
    </CFQUERY>
    </CFLOOP>
    </cfif>


    <cfset options = "option 1, option 2, option 3">

    <html>

    <head>

    <title>Untitled</title>

    </head>

    <cfset count = 0>

    <body>

    <cfform name="form1" id="form1" method="post" action="#cgi.script.name#">

    <table border="0" cellpadding="0" cellspacing="0">

    <cfloop index = "i" list = "#options#">

    <cfoutput>



    <tr>

    <td>#count#</td>

    <td>#i#</td>

    <td><input type="radio" name="OptionAssigned#count#" value="1">

    standard

    <input type="radio" name="OptionAssigned#count#" value="2">

    optional&nbsp;

    <input type="radio" name="OptionAssigned#count#" value="3">

    N/A

    <td>&nbsp;</td>

    </tr>

    <cfset count = count + 1>

    <input type="hidden" name="count" value="#count#">

    <input type="hidden" name="optionID#count#" value="#count#">

    </cfoutput>

    </cfloop>

    </table>

    <input name="submit" type="submit" value="Add Equipment" />

    <input type="hidden" name="ModelID" value="<cfoutput>#1#</cfoutput>" />

    <input type="hidden" name="MM_InsertRecord" value="form1" />

    </cfform>

    </body>

    </html>


    This is real quick but should point you in the right direction. Replace the 'count' variables with your actual query result variables and then change the options var. Let me know if you need more help.
     
    d_dimarco, Jun 18, 2009 IP