• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

strange behaviour in coldfusion - can anyone shed light

I am using Coldfusion MX code to update some MS Access database tables.

I have written some code which checks to see if a record exists in a table and if it does then it deletes it.

What is happening is that the check confirms that the record exists but I then get an error message telling me that the record has been deleted when I try to delete it.

I have attached the code I use and a screenshot of the results.

Can anyone explain to me what is happening and how I can fix it.

<cfoutput>Status = 0<br></cfoutput>


<cfoutput>Unit=All <br></cfoutput>

<cfquery name="getexistingsubidrecord" datasource="#MM_datasource#">
            SELECT * FROM SubmitAssessorAcceptance WHERE Subid=#thesub#;
</cfquery>

            
<cfif #getexistingsubidrecord.recordcount# GT 0>
            
<cfdump var="#getexistingsubidrecord#">
            
<cfoutput>Record already exists</cfoutput>
                  
<cfquery datasource="#MM_datasource#">
                  DELETE SubmitAssessorAcceptance.* FROM SubmitAssessorAcceptance
                  WHERE Subid=#thesub#
</cfquery>

</cfif>
            
<cfoutput>Delete all</cfoutput>

Thanks
scr1.JPG
0
Nemetona
Asked:
Nemetona
  • 4
  • 4
  • 4
2 Solutions
 
gdemariaCommented:
I believe the error is syntax, I don't know access that well, but I would think the delete command would not select any columns...

DELETE FROM SubmitAssessorAcceptance  WHERE Subid=#thesub#
0
 
NemetonaAuthor Commented:
I tried that variation but it made no difference to the result.  I am still getting the "Record is Deleted" message.

Any other thoughts?
0
 
gdemariaCommented:
It seems you may have some corruption, try the steps outlined here...

http://support.microsoft.com/?kbid=308630
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
NemetonaAuthor Commented:
I have already tried compacting and repairing the database.  It made no difference.    Any idea what else might cause queries to run twice?
0
 
gdemariaCommented:
I don't think your query is running twice.   Here is evidence.   Take another table with a dummy record, create a delete statement to delete the dummy record.   Run that delete statement twice, three time, four times.   You will not get an error about a deleted record.  Attempting to delete a record that does not exists does not give you an error.

What is the ID of the record you are trying to delete?  Let's say it's record 123.   Can you update this record?   Can you select it?   Can you select/update other records in the same table?

Let's see how broad this problem is...
0
 
NemetonaAuthor Commented:
There are no problems with the database itself.  I can update, insert and delete directly in the table and also from a query within MS Access.

I have also tried creating a different coldfusion page with just the most simple command on it but I am still getting the error.

Is there any signifigance to the fact that the Coldfuion is saying I have called the code twice or is this how it would normally appear?

D:\Inetpub\wwwroot\TestingSite\test1.cfm: line 152
Called from D:\Inetpub\wwwroot\TestingSite\test1.cfm: line 139
Called from D:\Inetpub\wwwroot\TestingSite\test1.cfm: line 58
Called from D:\Inetpub\wwwroot\TestingSite\test1.cfm: line 56
Called from D:\Inetpub\wwwroot\TestingSite\test1.cfm: line 1
Called from D:\Inetpub\wwwroot\TestingSite\test1.cfm: line 152
Called from D:\Inetpub\wwwroot\TestingSite\test1.cfm: line 139
Called from D:\Inetpub\wwwroot\TestingSite\test1.cfm: line 58
Called from D:\Inetpub\wwwroot\TestingSite\test1.cfm: line 56
Called from D:\Inetpub\wwwroot\TestingSite\test1.cfm: line 1
0
 
gdemariaCommented:
Its hard to tell from the line numbers, I don't know what is at those lines or if they are inside of a loop?  Is the code in your first post the entire code block?


>   I have also tried creating a different coldfusion page with just the most simple command on it but I am still getting the error.

Good - did you try different record IDs and different tables?   What gives the error and what conditions do not?   Is it always there or just for a particular ID?


Oh, do you have any triggers or procedures in the database that run when a table is changed?
0
 
_agx_Commented:
I don't use Access either but a quick search turned up several articles that mention a bunch of reasons that error might occur. Access is a desktop db, so it's got some limitations enterprise db's don't.  

Is this db also accessed by other users or are there any triggers involved? Have you ever been able to successfully delete records from this table or any other?
0
 
_agx_Commented:
Gah.. didn't see the other comment before hitting submit. Ignore my response, it's basically the same as what gd's asking.
0
 
NemetonaAuthor Commented:
I can update the database from a different coldfusion page so I don't think the problem lies with the database.

The code does appear to be running my queries twice as at one point I have an insert query.  I checked the database before running the code and the records do not exist, I run the code and I get an error message that the record already exists.  When I check the database the records are now present.

When I put debugging statements into the code they do not appear to run more than once.  Is there any way the query can run twice without the page being called twice?
0
 
_agx_Commented:
Is there any way the query can run twice without the page being called twice?

Not likely, if queries are running twice it's probably because something in the code is calling them twice.

As far as the error it probably is related to your Access db.  With enterprise db's you wouldn't get an error like that even if you tried to delete the same record twice. The db would just run the query. But nothing would happen the 2nd time because the record for ID = x didn't exist.  

So we're back to the earlier questions:
     1) Is this db also accessed by other users, like on a network
     2) Are there any triggers on this table?
     3) Have you ever been able to successfully DELETE records from this table
     or any other? Not just update, but specifically a DELETE because that's the
     operation that generated the error.
0
 
_agx_Commented:
So what ended up being the problem? :)
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now