Access problem

[Silvano]

Hi all,

When I try to use cfloop to loop a query that have 4 records and insert those 4 records into a new table, only first 3 records are inserted. 4th is somehow lost. I can see that the loop is executed OK and all 4 queries that are inserting all 4 records are executed but when I open database there are 3 records only...

Help?...

thx Sylvano
dsylvano@hotmail.com

 

[GunJack]

Hey Sylvano,

I'm guessing that you're outputting some info each time through the loop to verify it's running all four updates so I'll assume there's not a problem with the loop running 4 times.

Do you have any time of error trapping like <cfcatch> that might be intercepting an error message?

Also, what happens if you reverse the sort order? Does the same record not get inserted. Also, what happens if you add more records so you're looping over more records? I'm curious to see if it's just one record that's not going in or if it's possibly the first, last, etc.. that doesn't get inserted. Adding more records should make it clear whether it's a specific record or a specific position that's having trouble.

Just let me know and we'll go from there,
GJ

 

[Silvano]

Thanks for the response GunJack.
All four queries inside the loop are executed and new records were inserted fine. my mistake here was that I was copying the database file into user's folder to make it available for download and I was checking the copied database file (in the original database all the records were inserted); the file was copied before the 4th record was inserted OR after the 3rd loop/query was executed. When I finally realized what is going on here, I have enclosed the whole loop within the cftransaction tag to ensure that the whole loop is executed before the file can be accessed and copied.
Everything is working fine now but if you have a better solution I am open to suggestions
Sylvano
dsylvano@hotmail.com

 

[GunJack]

I'm not sure tha <cftransaction> will really keep it from being copied since it's only designed to roll back queries if they don't execute. It sounds like it is working with this but I suspect that you may find certain situations cause it to fail.

I would recommend using a <cflock> tag around the insert as well as around the code that copies it. This will truly ensure a copy doesn't occur while the inserts are happenning. The <cftransaction> is still a good idea to ensure the inserts are an all or none situation. I just don't know that it is a reliable means of preventing a copy.

GJ