March 12th, 2004, 09:56 AM
More ? about multiple Inserts
I have seen this topic reviously, but I am not sophisticated enough to follow the instructions there. Can someone please help with the situation? It is an Access database.
I have a form page with a table that is created with a query. The fields that I need to pass are CourseID (a checkbox) and priority, a text input.
I also pass a StudentID as a hidden value.
Here is the code for the form page:
<!--- Create an insert qry from the form submitted by student --->
<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
<cfquery name="enterStudent" datasource="courseChoose">
INSERT INTO Students (FName, MName, LName) VALUES (
<cfif IsDefined("FORM.FName") AND #FORM.FName# NEQ "">
<cfif IsDefined("FORM.MName") AND #FORM.MName# NEQ "">
<cfif IsDefined("FORM.LName") AND #FORM.LName# NEQ "">
<!--- Query the new tables to get a sessionid for the student who was just created.--->
<cfquery name="getStudentID" datasource="courseChoose">
WHERE students.lname = '#form.Lname#'
AND students.mname = '#form.mname#'
AND students.fname = '#form.fname#'
<!---Check to make sure that the majorID is present--->
<cfif isdefined("form.majorid") and #form.majorid# NEQ "">
Try again, please, kind sir.
<!--- Get the course info fron the courses table and the join table --->
<cfquery name="ShowCourses" datasource="coursechoose">
SELECT DISTINCT courses.courseid, courses.coursename, courses.courseNumber, courses.coursedesc, joincourseandMajor.majorid,
FROM courses, joincourseandMajor
AND joincourseandMajor.majorid= #form.majorid#
<!--- set the student session id --->
<cfset session.studentid = "#getstudentid.studentid#">
<!--- Set the total rows for the loop... I am just trying this
<cfoutput><cfset totalRow = #showcourses.recordcount#></cfoutput>--->
<form action="courseList3.cfm" method="post" name="tryit">
<!--- Add the column Headers --->
<tr><TD colspan="2">Course Number and Priority</TD><TD>Course Name</TD><TD>Course Description </TD></tr>
<!--- Get the output for the rows and inputs--->
<td><input name="courseid" value="#courseid#" type="checkbox">
<!--- Get info for the set priority input --->
<input type="text" width="10" align="middle" name="priority" value=""> </td>
<!--- Information about the courses --->
<tr><td><input type="submit" value="Choose Courses"></td><td></td><td></td></tr>
<input type="hidden" <cfoutput>value="#session.studentid#"</cfoutput> name="session.studentid" >
<input type="hidden" <cfoutput>value="#showcourses.recordcount#"</cfoutput> name="recordcount">
</form><!--- </cfif> --->
The action page has the insert Query:
INSERT INTO StudentsandCourses (StudentID, CourseID, Priority) VALUES (
<cfif IsDefined("session.StudentID") AND #session.StudentID# NEQ "">
<cfif IsDefined("FORM.CourseID") AND #FORM.CourseID# NEQ "">
<cfif IsDefined("FORM.Priority") AND #FORM.Priority# NEQ "">
I have tried various loops, but I can't seem to get it right. The code works for a single INSERT.
Thanks for any help.
March 12th, 2004, 10:22 AM
Out of all that, I am still not sure what you are trying to do. If you only have 1 record to insert based on the form fields, where does the issue of doing "multiple inserts" come into play?
March 12th, 2004, 10:52 AM
Thanks for the reply, Kiteless. Sorry about the poor question.
When the form page loads, there are several options of courses and priorities populated from a query. The user selects the courses with the check box and assigns a priority. The action page then inserts the records into a table that holds the courseID's, the priority, and the studentID.
As long as only one course checkbox and priority are selected, the form works. Otherwise, there is an error
Does that help?
March 12th, 2004, 01:06 PM
You can just treat form.courseid as a list of values, since all the checkboxes are named the same (courseid).
<cfif isDefined( 'form.courseid' )>
<cfloop index='thisCourseID' list='#form.courseID#'>
...insert query goes here, make sure you use #thisCourseID# to get the current course ID as the loop iterates.
One question though, how do you know which priority goes with each course id? You probably want to change the input for the priority so it contains the course id:
<input type="text" width="10" align="middle" name="priority#courseID#" value="">
Now in your insert query, if you are inserting courseID 12, you can get it's priority as #form.priority12#.
March 13th, 2004, 10:35 AM
Thank you so much, kiteless. I will try that as soon as I get back to work Our server is down right now for electrical upgrades.