MS SQL Server - mysterious delete records problem

[arulraj]

Hi
This problem is driving me crazy for a few days. This is a simple combination of delete and insert query combination. I’m using Coldfusion 4.5, SQL Server 7.0/2000. The outline of the code goes like this:


<cfif . . .>
<cfquery name=”deleteQuery” datasource=”myDataSource”>
delete from myTable
where my condition
</cfquery>
</cfif>
<!—after some processing I loop thro’ some user inputs and insert records -->

<cfloop >
insert into myTable…

</cfloop>

<!—then I go on to show the records - - >
<cfquery name=”selectQuery”>
select * from myTable
</cfquery>

<cfoutput>#selectQuery.recordCount#</cfoutput>


Now the problem. The delete query is reached only when a certain condition is fulfilled. So, after the insert query, and the recordCount reorts the correct number of records. But if I go and check up with the SQL directly, the records are not there.

How are they deleted.
Two more things that puzzle me: i) Even if the delete query is not reached, the deletion takes place. Ii) if the delete query has some error (unknown tablename) then a)if the delete query is not reached, the records are intact, b) if it is reached, the error in th query is reported.

Is this a cache problem?

Please shed some light. I have done scores of this kind of SQL operations without any hitch. That’s why this is a big mystery.

Thanks in advance.

Arulraj

 

[iza]

add debugs to know :
<cfif . . .>
<h1>hey i'm deleting records !!! btw, the variables passed to the cfif are <cfoutput>#my_var#</cfoutput> and ...</h1>
<cfquery name=?deleteQuery? datasource=?myDataSource?>
delete from myTable
where my condition
</cfquery>
</cfif>