Skip navigation
kt03
kt03
Currently Being Moderated

query

Dec 3, 2012 1:35 PM

I have the queries that get values from other two tables and then insert those values into the final tables.  My code below did not inserted the correct record.

Thanks

 

<!---list all  purNo, padNo where purNo = 3215--->
<Cfquery name="getDetails" datasource="#dsn#">
select purNo, padNo
    from tbl_details
    where purNo = 3215
</Cfquery>


<!---list all cusNO where purNo = 3215--->
<Cfquery name="getCust" datasource="#dsn#">
select cusNO
    from tbl_customer
    where purNo = 3215
</Cfquery>

 

From the form, I have one text box for user to enter new date then hit submit.  Once submit button is clicked, I would like to get the results from above two queries to insert into tblOrder.

 

<cfset note ="new date changed from" />

 

<cfloop query="getDetails">
<cfloop query="getCust">

<cfquery name="insert" datasource="#dsn#">

         insert into tblOrder (purNo, padNo, cusNO, note)

            values

            (

          <cfqueryparam value ="#getDetails.purNo#" />,

           <cfqueryparam value ="#getDetails.padNo#" />,

           <cfqueryparam value = "#getCust.cusNO#" />,

           <cfqueryparam value="#note# #getDetails.old_ate# &'to' & #form.new_date#" />

            )

           

        </cfquery>

    </cfloop>
</cfloop>

366 Views   2 Replies   Latest reply: BKBK, Dec 11, 2012 3:25 AM
 
Replies
  • Dan Bracuk
    Currently Being Moderated
    1. Dan Bracuk,
    Dec 3, 2012 2:45 PM   in reply to kt03
    Report

    Instead of nested loops, use this type of syntax.

     

    insert into tblOrder

    (field1, field2, etc)

    select somefield, someotherfield, etc
    from some other tables

    where some conditions are met

     
    |
    Mark as:
  • BKBK
    Currently Being Moderated
    2. BKBK,
    Dec 11, 2012 3:25 AM   in reply to kt03
    Report

    You could do a join, enabling you to use just one query. Something like

     

    <!--- space appended --->

    <cfset note ="new date changed from " />

     

    <Cfquery name="getCustDetails" datasource="#dsn#">

        select tbl_details.purNo, tbl_details.padNo, tbl_customer.cusNO

        from tbl_details, tbl_customer

        where tbl_details.purNo = tbl_customer.purNo

        and tbl_details.purNo = 3215

    </Cfquery>

     

    <cfloop query="getCustDetails">

     

    <!--- note: 'old_date' replaces 'old_ate' and " to " replaces "to" --->

    <cfset note = note & getCustDetails.old_date & " to " & form.new_date>

     

    <cfquery name="insert" datasource="#dsn#">

    insert into tblOrder (purNo, padNo, cusNO, note)

        values

        (

      <cfqueryparam value = "#getCustDetails.purno#" cfsqltype = "cf_sql_varchar" />,

       <cfqueryparam value = "#getCustDetails.padNo#" cfsqltype = "cf_sql_varchar" />,

       <cfqueryparam value = "#getCustDetails.cusNO#" cfsqltype = "cf_sql_varchar" />,

       <cfqueryparam value = "#note#" cfsqltype = "cf_sql_varchar" />

        )

    </cfquery>

     

    </cfloop>

     
    |
    Mark as:
Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points