Sorry for the delay- been running some traingings. Here's the real scoop (more complicated then my example) Teachers can schedule students from the grade and subject that the admin has set up. If the grade or subject has not been chosen by the admin than they can't schedule that student(s) This is what I have working: 1st query runs check. 2nd query (stored proc) runs insert It works ok now until I try to add one more loop before the stored procedure. I need it to have the ability to insert several valid subjects. Right now mine only allows the insert of one subject. It's when I try to do another loop that it becomes the insert all the students no matter if they can be scheduled/ or insert no students problem... Hope I'm making sense. j
<!---Check to see that the grade and subject are valid. The variable StudentGrade is a conctenation of the StudentId + Grade of student TeacherType is the subject The table ElementaryCourseList is a reference table the table ClassChanges is the table that shows which grade subjects can change ---> <CFINCLUDE TEMPLATE="../qry_Globals.cfm"> <CFPARAM NAME="GradeList" DEFAULT=""><!------> <CFSET CLIENT.msgWrongClass= ""> <CFPARAM NAME="GroupName" DEFAULT="N/A"> <CFPARAM NAME="Attributes.GroupName" DEFAULT=""> <CFPARAM NAME="attributes.TeacherType" DEFAULT="no"> <CFSET #GRADE#=#Right(StudentGrade,2)#> <!------> <CFOUTPUT>Grade:#GRADE#//</CFOUTPUT> <!---Stored procedure that inserts the teachers schedule---> <CFLOOP LIST="#GradeList#" INDEX="StudentGrade"> <CFLOOP INDEX="x" FROM=1 TO="#ListLen(TeacherType)#"> <CFQUERY NAME="CheckStatus" DATASOURCE="#Request.maindsn#"> SELECT cc.SchoolID, ecl.CourseID, cc.Grade, ecl.TeacherType, S.StudentID FROM Students S, ElementaryCourseList ecl INNER JOin ClassChanges cc on ecl.CourseID =cc.CourseID WHERE S.StudentID='#LEFT(StudentGrade, 12)#' and ((cc.SchoolID= #CLIENT.SchoolID#) AND (cc.CourseID=ecl.CourseID)AND (cc.Grade='#Right(StudentGrade, 2)#') AND (ecl.TeacherType='#ListGetAt(Attributes.TeacherType, x)#')) ORDER BY cc.CourseID, cc.Grade </CFQUERY> <CFSET StudentID=CheckStatus.StudentID> </CFLOOP> </CFLOOP> <CFOUTPUT> <CFLOOP INDEX="x" FROM=1 TO="#ListLen(CheckStatus.StudentID)#"> <CFSTOREDPROC PROcEDURE="TeacherClasslistAdd" DATASOURCE="#Request.maindsn#"> <CFPROCPARAM TYPE="IN" DBVARNAME="SchoolID" VALUE="#SchoolID#" CFSQLTYPE="CF_SQL_VARCHAR"> <CFPROCPARAM TYPE="IN" DBVARNAME="CLASSLINK" VALUE="#CLASSLINK#" CFSQLTYPE="CF_SQL_INTEGER"> <CFPROCPARAM TYPE="IN" DBVARNAME="StudentID" VALUE="#CheckStatus.StudentID#" CFSQLTYPE="CF_SQL_VARCHAR"> <CFPROCPARAM TYPE="IN" DBVARNAME="GroupName" VALUE="#GroupName#" CFSQLTYPE="CF_SQL_VARCHAR" > <CFPROCPARAM TYPE="IN" DBVARNAME="TeacherType" CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#ListGetAt(Attributes.TeacherType, x)#"> </CFSTOREDPROC> </CFLOOP> </CFOUTPUT> </CFOUTPUT> -----Original Message----- From: Adrian Lynch [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 19, 2002 1:27 PM To: CF-Talk Subject: RE: Run a check before an insert LEAVE OUT THE TV???? No way, throw some eggs in there and you've got some pancakes :OD Show us some code Ade -----Original Message----- From: Janine Jakim [mailto:[EMAIL PROTECTED]] Sent: 19 September 2002 17:55 To: CF-Talk Subject: Run a check before an insert Ok I need to make a check on one table before I do an insert into another table. (Kind of similar to a login where the 1st query checks the username/2nd query checks the group). But I want to be able to have the 1st table be able to check for several rows in the first query and only insert the matches into the second query. So an example is: I am restricted to only ordering food items. I order sugar, milk, a tv and flour. I want the first query to look at the order then send to the 2nd query the order for sugar, milk and sugar, leaving out the tv. (and maybe sending a message to the user...sorry you can't order that...) I can't seem to get my loops right on this one- I either seem to be able to add all or nothing. Thanks in advance, Janine ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
