How to increment count id for each insert and every loop iteration?

Question

I have for loop that should increment count_id for every query and each loop iteration. Here is example of my code:

qryCode = queryExecute("SELECT max(display_order) AS maxdisplay FROM type_ref",{},{datasource: application.datasource}); // Result set of the query is: 58 
qryItems = queryExecute("SELECT DISTINCT type_id, label, type_shortcode FROM types gtr WHERE item_id = :item_id",{item_id: {cfsqltype: "cf_sql_numeric",value: arguments.item_id}},{datasource: application.datasource}); 

// Result set of qryItems:
TYPE_ID  LABEL    TYPE_SHORTCODE
1        2012-1   HOA
2        2012-1   HOC
5        2012-1   HOR

local.display_count = qryCode.maxdisplay;

for ( row in qryItems ) {
    local.sqlInsert &= " INSERT INTO type_ref (display_order) VALUES (?) ";
    qryParams.append({cfsqltype="CF_SQL_NUMERIC", value: display_count+1});
    local.sqlInsert &= " INSERT INTO type_ref (display_order) VALUES (?) ";
    qryParams.append({cfsqltype="CF_SQL_NUMERIC", value: display_count+2});
    display_count++;
}

The code above will increment first two values correct (59 & 60) but for the second iteration it will start from 60 instead of 61. The code should produce count_id's int his order: 59,60,61,62,63,64. There are three records in qryItems. The qryCode has max value of 58. The first query in first iteration should start from 58 + 1 = 59. The next one should be 58 + 2 = 60. In the second iteration the first count_id should be 61 and so on. I'm not sure why the code I have above starts second iteration from 60 instead of 61. I do have this line that should increase the count_id at the end of each iteration: display_count++;.

Answer 1

It's because you're doing 2 inserts per iteration, therefore you should increment display_count by 2 instead of 1. So your for loop should look like this instead.

for ( row in qryItems ) {
    local.sqlInsert &= " INSERT INTO type_ref (display_order) VALUES (?) ";
    qryParams.append({cfsqltype="CF_SQL_NUMERIC", value: display_count+1});
    local.sqlInsert &= " INSERT INTO type_ref (display_order) VALUES (?) ";
    qryParams.append({cfsqltype="CF_SQL_NUMERIC", value: display_count+2});
    display_count +=2;
}