cftransaction & stored procedure

[LongHorn]

Hello,
I have a stored procedure that will insert the next id into the table when doing insert. I want to insert 3 records into the same table and put this insert statement inside the CFTRANSACTION tag, will the database know to grab the next id 3 times, or will it insert the same next id into the table, which would cause a key violation error.

Example:

<CFTRANSACTION>
<CFLOOP nIndx=&quot;record&quot; FROM=&quot;1&quot; TO=&quot;3&quot;>
<CFQUERY NAMe=&quot;insertaffil&quot; DATASOURCE =&quot;#DSNAME#&quot;>
INSERT into nsr_affil
(affil_id,area_id, site_id, affil_typ, created)
VALUES
(#record# ,
#attributes.area_id#, #attributes.site_id#,
'AS',{^#DateFormat(Now(),&quot;yyyy/mm/dd&quot#})
</CFQUERY>
</CFLOOP>
</CFTRANSACTION>

Thanks,
Longhorn

 

[Guest_imported]

First off, what DBMS are you using?

The other problem is that this is _NOT_ a stored procedure, but a onetime query.

 

[LongHorn]

I use MS Visual Fox Pro 6.

Let me modify my query a little bit. The affil_id is confusing, but believe me, there is a stored procedure used to insert the next id whenever you do an insert.
I used the loop to demonstrate the insertion of more than one records, this is not the real query I use in my app.



<CFTRANSACTION>
<CFLOOP nIndx=&quot;record&quot; FROM=&quot;1&quot; TO=&quot;3&quot;>
<CFQUERY NAMe=&quot;insertaffil&quot; DATASOURCE =&quot;#DSNAME#&quot;>
INSERT into nsr_affil
(area_id, site_id, affil_typ, created)
VALUES
(#attributes.area_id#, #attributes.site_id#,
'AS',{^#DateFormat(Now(),&quot;yyyy/mm/dd&quot#})
</CFQUERY>
</CFLOOP>
</CFTRANSACTION>

Thanks,
Longhorn