#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Location
    London, Ontario
    Posts
    3
    Rep Power
    0

    Question 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 "">
    '#FORM.FName#'
    <cfelse>
    NULL
    </cfif>
    ,
    <cfif IsDefined("FORM.MName") AND #FORM.MName# NEQ "">
    '#FORM.MName#'
    <cfelse>
    NULL
    </cfif>
    ,
    <cfif IsDefined("FORM.LName") AND #FORM.LName# NEQ "">
    '#FORM.LName#'
    <cfelse>
    NULL
    </cfif>
    )
    </cfquery> </cfif>

    <!--- Query the new tables to get a sessionid for the student who was just created.--->
    <cfquery name="getStudentID" datasource="courseChoose">
    Select StudentID
    FROM students
    WHERE students.lname = '#form.Lname#'
    AND students.mname = '#form.mname#'
    AND students.fname = '#form.fname#'
    </cfquery>

    <!---Check to make sure that the majorID is present--->

    <cfif isdefined("form.majorid") and #form.majorid# NEQ "">
    <cfoutput>#form.MajorID#</cfoutput>
    <cfelse>
    Try again, please, kind sir.
    </cfif>



    <!--- 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,
    joincourseandMajor.courseid
    FROM courses, joincourseandMajor
    WHERE courses.courseid=joincourseandMajor.courseid
    AND joincourseandMajor.majorid= #form.majorid#
    </cfquery>

    <!--- 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>--->

    <body>
    <cfoutput>#totalRow#</cfoutput>


    <table border="1">


    <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--->


    <cfoutput query="ShowCourses">

    <tr>
    <td>#courseNumber# </td>

    <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 --->

    <td>#Coursename#</td><td>#coursedesc#</td></tr></cfoutput>

    <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> --->
    </table></body>

    </html>


    The action page has the insert Query:

    INSERT INTO StudentsandCourses (StudentID, CourseID, Priority) VALUES (
    <cfif IsDefined("session.StudentID") AND #session.StudentID# NEQ "">
    #Session.StudentID#
    <cfelse>
    NULL
    </cfif>
    ,
    <cfif IsDefined("FORM.CourseID") AND #FORM.CourseID# NEQ "">
    #FORM.CourseID#
    <cfelse>
    NULL
    </cfif>
    ,
    <cfif IsDefined("FORM.Priority") AND #FORM.Priority# NEQ "">
    '#FORM.Priority#'
    <cfelse>
    NULL
    </cfif>
    )
    </cfquery> --->

    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.
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,275
    Rep Power
    968
    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?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Location
    London, Ontario
    Posts
    3
    Rep Power
    0
    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?
  6. #4
  7. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,275
    Rep Power
    968
    You can just treat form.courseid as a list of values, since all the checkboxes are named the same (courseid).

    So

    <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.
    </cfloop>
    </cfif>

    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#.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Location
    London, Ontario
    Posts
    3
    Rep Power
    0
    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.
    Thanksagain

IMN logo majestic logo threadwatch logo seochat tools logo