Leigh,
Thanks - uhhh...yes, overlooked that part didn't I?
Here's what I have so far - not all of my validation is place but I need to get
this to work first:
<cfif IsDefined(#FORM.submitted#)>
<cfquery name="newStudentInsert" datasource="abc">
<!--- START Looped insert of multiple records--->
<cfloop from="1" to="#FORM.StudentRecordCount#" index="i">
<!--- SET form vairables to field names --->
<cfset NEWstu_cert_num = FORM["CertNum"& i]>
<cfset stu_last_name = FORM["lastName"& i]>
<cfset stu_first_name = FORM["firstName"& i]>
<cfset stu_email = FORM["email"& i]>
<cfset stu_hm_phone = FORM["phone"& i]>
<cfset stu_company = FORM["Company"& i]>
<cfset course_instructor = FORM["instructor"& i]>
<cfset course_sponsor = FORM["Sponsor"& i]>
<cfset course_location = FORM["location"& i]>
<cfset course_expiration = FORM["ExpirationDateParsed"& i]>
<cfset course_date = FORM["ExamDateParsed"& i]>
<cfset lu_student_id = FORM["instructor"& i]>
<!--- search existing records to see if the certificate number exists
--->
<cfquery name="dupeCheck" datasource="abc">
SELECT
ST.stu_id AS CheckedStuID,
ST.stu_cert_num AS CheckedCertNum,
C.course_date AS CheckedCourseDate
FROM
akcescl_tbl_student ST,
akcescl_tbl_course C,
akcescl_lu_student_course LUSC
WHERE
LUSC.lu_student_id = ST.stu_id
AND
LUSC.lu_course_id = C.course_id
AND
ST.stu_cert_num = #NEWstu_cert_num#
</cfquery>
<cfif dupeCheck.recordCount GT 0>
<cfif DateCompare(course_date, dupeCheck.CheckedCourseDate,
"d") GT 0>
<!--- INSERT course record --->
<!--- 3 ---> <cfquery name="insertCourseSameStudent"
datasource="abc">
INSERT INTO
akcescl.akcescl_tbl_course (course_instructor,
course_sponsor, course_location, course_expiration, course_date)
VALUES
(
#course_instructor#,
#course_sponsor#,
#course_location#,
'#course_expiration#',
'#course_date#'
)
</cfquery>
<!--- GET latest course IDs --->
<!--- 4 ---> <cfquery name="getLatestIDs" datasource="abc">
SELECT
course_id AS MaxCourseId
FROM
akcescl_tbl_course
ORDER BY
course_id DESC
LIMIT 1
</cfquery>
<!--- INSERT student/course lookup record --->
<!--- 5 ---> <cfquery name="insertCourseLookupSameStudent"
datasource="abc">
<cfset course_id = getLatestIDs.MaxCourseId>
INSERT INTO
akcescl.akcescl_lu_student_course (lu_student_id,
lu_course_id)
VALUES
(
#dupeCheck.CheckedStuID#,
#getLatestIDs.MaxCourseId#
)
</cfquery>
</cfif>
<cfelseif dupeCheck.recordCount EQ 0>
<!--- INSERT student record --->
<!--- 6 ---> <cfquery name="insertNewStudent" datasource="abc">
INSERT INTO
akcescl.akcescl_tbl_student (stu_cert_num,
stu_last_name, stu_first_name, stu_company, stu_hm_phone, stu_email)
VALUES
(
'#NEWstu_cert_num#',
'#stu_last_name#',
'#stu_first_name#',
'#stu_company#',
'#stu_hm_phone#',
'#stu_email#'
)
</cfquery>
<!--- INSERT course record --->
<!--- 7 ---> <cfquery name="insertCourseNewStudent"
datasource="abc">
INSERT INTO
akcescl.akcescl_tbl_course (course_instructor,
course_sponsor, course_location, course_expiration, course_date)
VALUES
(
#course_instructor#,
#course_sponsor#,
#course_location#,
'#course_expiration#',
'#course_date#'
)
</cfquery>
<!--- GET latest course and student IDs
--->
<!--- 8 ---> <cfquery name="getLatestIDs" datasource="abc">
SELECT
S.stu_id,
C.course_id
FROM
akcescl_tbl_course C,
akcescl_tbl_student S
WHERE
S.stu_id = (SELECT stu_id FROM
akcescl_tbl_student S ORDER BY stu_id DESC LIMIT 1)
AND
C.course_id = (SELECT course_id FROM
akcescl_tbl_course C ORDER BY course_id DESC LIMIT 1)
</cfquery>
<!--- INSERT student/course lookup record --->
<!--- 9 ---> <cfquery name="insertCourseLookupNewStudent"
datasource="abc">
<!--- SET record results --->
<cfset lu_student_id = getLatestIDs.stu_id>
<cfset lu_course_id = getLatestIDs.course_id>
INSERT INTO
akcescl.akcescl_lu_student_course (lu_student_id,
lu_course_id)
VALUES
(
#lu_student_id#,
#lu_course_id#
)
</cfquery>
</cfif>
</cfloop>
<!--- END Loop of inserts --->
</cfquery>
</cfif>
>It might help to see some code.
>
>-Leigh
>
>
>
>
>
>____________________________________________________________________________________
>Need Mail bonding?
>Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users.
>http://answers.yahoo.com/dir/?link=list&sid=396546091
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342079
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
