What would cause a cfquery to be skipped?

[skythrock]

So I'm debugging this program that essentially checks whether or not a student has passed or failed a number of exams.  The code is structured so as to copy information from the "active student" table to a "student history" table before deleting the info from the "active student" table.  However, the code seems to be skipping the query that copies the information and moves straight to the query that deletes the information.  I am simply at a loss to understand why a query would be skipped.  This queries are literally right next to each other in the code, embedded in the same cfif statement.  Initially, I suppose I'm simply asking what in the world would cause one query to be skipped but not the query directly beneath it.  Thanks for any assistance!

-- Levi Throckmorton

[ilssac]

Well there is "skipped", which would seem to be rather magical and strange based on what I imagine your code looks like (hint?).

And there is "not executed".  That woud mean that ColdFusion ran the <cfquery...> block.  But that is just a simple copy of the SQL inside the block and passing it on to the database driver which will then send it to the database server.

It is then up to the driver and database to properly do their jobs before the instructions in the SQL actually results in the desired effects.

So my first suggestion is to check out the database and see what it is doing with this query when it receives it and why it might not be honoring the instructions.  Maybe permissions problems?

[skythrock]

You're correct, the proper term for what is happening is better said as "not executed."

Here is the code that is not working:

<cfquery datasource="ITCourse">
                    UPDATE STUDENT
                    SET CourseGrade = 'Pass'
                    WHERE UserName = '#user.UserName#'
                </cfquery>
       
                <cfquery datasource="ITCourse">
                    INSERT INTO History_Student_Exams
                        (Student_UserName,
                         Exam_Name,
                         Date_Taken,
                         Grade,
                        Pledge,
                        Pledge_name,
                        Datepledged,
                        Exam_Start_Date,
                        Semester)
                    SELECT Student_UserName, Exam_Name, Date_Taken, Grade, Pledge, Pledge_Name, Date_Pledged, Exam_Start_Date, Semester
                    FROM Student_Scheduled_Exam, Semester
                    WHERE Student_UserName = '#user.UserName#'
                    and start_date = #createodbcdate(now())#
                    and end_date >= #createodbcdate(now())#
                </cfquery>
               
                <cfquery datasource="ITCourse">
                    DELETE FROM Student_Scheduled_Exam
                    WHERE STUDENT_UserName = '#user.UserName#'
                </cfquery>

So the INSERT INTO query is not being executed, but the DELETE query immediately afterwards is being executed.

[Adam Cameron.]

And does the SELECT in the INSERT statement match any records?

--

Adam

[Reed_Powell-ttnmOb]

I'm more concerned about that SELECT statement, since it has two tables listed in the FROM but no code to join them.  Try putting a RESULT="X" in the INSERT <CFQUERY> and a <CFDUMP var="#x#"><cfabort> after that query, and see what it says it is up to.

-reed

[Dan_Bracuk · Accepted solution]

Your problem is being caused by this line:

and start_date = #createodbcdate(now())#

[skythrock]

Thanks all for your assistance, the problem was indeed the start_date in the insert query.