?
Solved

How to do sql inserts using recordcount?

Posted on 2009-12-17
3
Medium Priority
?
282 Views
Last Modified: 2013-12-16
     <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>
0
Comment
Question by:dawes4000
  • 2
3 Comments
 

Author Comment

by:dawes4000
ID: 26076617
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.
0
 
LVL 16

Accepted Solution

by:
duncancumming earned 2000 total points
ID: 26091744
You just to loop round the query.  Otherwise it will just refer to the first row of your select query.  You may also want to do this on the insert Master query too (unless the master select will only ever return 1 row).



<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>      

	<cfoutput 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>
	</cfoutput>
      </cffunction>

Open in new window

0
 

Author Comment

by:dawes4000
ID: 26096867
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>
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
The purpose of this video is to demonstrate how to automatically show related posts at the bottom of a blog post in WordPress. This will be demonstrated using a Windows 8 PC. Plugin “Yet Another Related Posts Plugin” will be used. Go to your…
The purpose of this video is to demonstrate how to integrate Mailchimp with Facebook. This will be demonstrated using a Windows 8 PC. Mailchimp and Facebook will be used. Log into your Mailchimp account. : Click on your name. Go to Account Setti…
Suggested Courses

839 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