I need help doing a loop over the following recordsets to insert into another table. Hopefully the code/markup will explain it better.

<!--- select all kits associated with a service instruction from the form --->
<cfquery name="getKits" datasource="#dsn#">
SELECT kit
FROM ACFT_KIT
WHERE SERV_INSTR = '#form.mySERV_INSTR#'
ORDER BY kit
</cfquery>

<!---If this is first requisition posted today reset counter table to reflect todays date and start serial number at 1--->
<cfquery name="updSERNO" datasource="#dsn#">
update serno_ctr
set serno = '1',
upd_date = to_char(sysdate, 'dd-mon-yyyy')
where upd_date != to_char(sysdate, 'dd-mon-yyyy')
</cfquery>

<!---Select the serial number from counter table--->
<cfquery name="nexSerno" datasource="#dsn#">
SELECT serno
FROM serno_ctr
</cfquery>

<!---Increment the serial number--->
<cfset nextSERNO = #nexSerno.serno# + 1>

<!---
This is where I need the help. I need to loop over the INSERT query to insert seperate records for each kit retrieved in the getKits query and increment the serno for each new record with a final UPDATE query into the serno table with the last records serial number. The basic query if there were only one kit would look like this
--->
<cfquery name="insKITS" datasource="pbl_tracking">
INSERT INTO ACFT_PARTS_TBL (JDATE, PBL_CD, MATL_TYPE, BUNO, JON, SERV_INSTR, PROJECT, UNIT, QTY, DROP_LOCATION, PART_NO, serno)
VALUES('#form.jdate#', 'BV', 'KIT', '#form.myBUNO#', '#getJON.myJON#', '#form.mySERV_INSTR#',  '#getJON.myPROJECT#', 'EA', '1', 'STA9', '#getKits.kit#', '#nextSERNO#')
</cfquery>

<!---only one record is in the serno table, no need to use where--->
<cfquery name="updSERNO" datasource="#dsn#">
update serno_ctr
set serno = '#nextSERNO#'
</cfquery>

Thanks for your help.
Dave

I don't see a getJON query so I'm guessing you have access to it in your actual template...

But from what I can tell from what you provided this should work:

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel.  Covey

Thanks Lyndon,
I had it stuck in my head that a CFLOOP was going to be needed. Your solution worked out great.

Oh BTW, in my example
<cfset nextSERNO = nexSerno.serno + 1>

should be
<cfset nextSERNO = nexSerno.serno>

or you will be skipping a number every time this code runs.

Sorry I just saw that...

Good luck.
 

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel.  Covey

I caught it and already made the change.

<cfset nextSERNO = nexSerno.serno>

<cfoutput query="getKits">

<cfset nextSERNO = nextSERNO + 1>
  
<cfquery name="insKITS" datasource="pbl_tracking">.....
</cfoutput>

Again, thanks for your assistance.

Quote:

<cfset nextSERNO = nexSerno.serno>

<cfoutput query="getKits">
<cfset nextSERNO = nextSERNO + 1>
....

Since CF is multi-threaded, it is possible multiple requests could end up with the same serial numbers. The more concurrent users in the application, the higher the probability that will happen. If that is undesirable, you need to implement some sort of locking to avoid it.

The vast majority of the time, the database is the best place for this kind of logic, not CF.  Databases are better equipped to handle low data level locking. Check your database's documentation for information on transaction levels and locking.  

HTH

 

----------------------------------
http://cfsearching.blogspot.com/

cfSearching - that's a great point, easily overlooked.  That deserves a star.

Thanks :)

----------------------------------
http://cfsearching.blogspot.com/