asked on

How to do sql inserts using recordcount?

     <cffunction name="trantables" access="public">
            <cfargument name="OrderID" required="yes">
<cfargument name="Status" required="yes">
            <cfquery name="qryWBmast" datasource="#Session.connString#">
SELECT tblOrder.CompanyID AS custno, CONVERT(varchar(12),tblOrder.dtRecvd,101) AS sodate, CONVERT(varchar(12),tblOrder.dtRecvd,101) AS ordate,
                  tblCompany.discount AS disc, tblOrder.ordamt, tblOrder.VendorPO AS ponum,
tblOrder.OrderID AS wppsono, tblOrder.Plant AS defloc, CONVERT(varchar(12), GETDATE(), 101) AS adddatetime
FROM tblOrder INNER JOIN
tblCompany ON tblCompany.CompanyID = tblOrder.CompanyID
WHERE (tblOrder.OrderID = '#arguments.OrderID#')
            </cfquery>
            <cfquery name="qryWBtran" datasource="#Session.connString#">
SELECT tblPartOrder.PartID AS item, tblParts.itmdesc AS description, tblCompany.discount AS disc,
                  tblPartOrder.Price, tblPartOrder.quantity AS qtyord, tblPartOrder.extprice, tblPartOrder.SourceLineNo,
tblPartOrder.OrderID AS wppsono, tblOrder.Plant AS locid, CONVERT(varchar(12), GETDATE(), 101) AS adddatetime
FROM tblPartOrder INNER JOIN
tblParts ON tblParts.PartID = tblPartOrder.PartID INNER JOIN
tblOrder ON tblOrder.OrderID = tblPartOrder.OrderID INNER JOIN
tblCompany ON tblCompany.CompanyID = tblOrder.CompanyID
WHERE (tblOrder.OrderID = '#arguments.OrderID#')
            </cfquery>
<cfquery name="qryInsertWBmast" datasource="#Session.connString#">
      INSERT INTO WBmast
(custno, sodate, ordate, disc, ordamt, ponum, sostat, wppsono, defloc, adddatetime) VALUES
('#qryWBmast.custno#','#qryWBmast.sodate#','#qryWBmast.ordate#','#qryWBmast.disc#','#qryWBmast.ordamt#','#qryWBmast.ponum#','#arguments.status#','#qryWBmast.wppsono#','#qryWBmast.defloc#','#qryWBmast.adddatetime#')
</cfquery>
<cfquery name="qryInsertWBtran" datasource="#Session.connString#">
      INSERT INTO WBtran
(item, descrip, disc, price, qtyord, extprice, source_is_lineno, wppsono, locid, adddatetime) VALUES
('#trim(qryWBtran.item)#','#trim(qryWBtran.description)#','#qryWBtran.disc#','#qryWBtran.price#','#qryWBtran.qtyord#','#qryWBtran.extprice#','#qryWBtran.SourceLineNo#','#qryWBtran.wppsono#','#qryWBtran.locid#','#qryWBtran.adddatetime#')
</cfquery>
      </cffunction>

dawes4000

ASKER

Sorry, fat fingered the submit. The function above is used to insert into two DB tables WBmast (master), WBtran (detail), a one to many relationship. I thought that by the nature of cfquery the "qryInsertWBtran" query would insert each row of deatil, shouldn't it? It doesn't, so my thought would be to use a loop record count to to the job. But I can not get that to Work.

ASKER CERTIFIED SOLUTION

duncancumming

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dawes4000

ASKER

cffloop also works.

<cfloop query="qryWBtran">
<cfquery name="qryInsertWBtran" datasource="#Session.connString#">
INSERT INTO WBtran
(item, descrip, disc, price, qtyord, extprice, source_is_lineno, wppsono, locid, adddatetime) VALUES
('#trim(qryWBtran.item)#','#trim(qryWBtran.description)#','#qryWBtran.disc#','#qryWBtran.price#','#qryWBtran.qtyord#','#qryWBtran.extprice#','#qryWBtran.SourceLineNo#','#qryWBtran.wppsono#','#qryWBtran.locid#','#qryWBtran.adddatetime#')
</cfquery>
</cfloop>