ColdFusion Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
The Best Selling PC Migration Utility.
Go Back   Dev Shed Forums > Programming Languages - More > ColdFusion Development
Reload this Page

More ? about multiple Inserts

Discuss More ? about multiple Inserts in the ColdFusion Development forum on Dev Shed.
More ? about multiple Inserts ColdFusion Development forum discussing CFML coding practices, tips on CFML, and other CFML related topics. Find out why ColdFusion is the tool of choice for many e-commerce developers.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here
  #1  
Old March 12th, 2004, 08:56 AM
Newby Newby is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Mar 2004
Location: London, Ontario
Posts: 3 Newby User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation 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.
Reply With Quote
  #2  
Old March 12th, 2004, 09:22 AM
kiteless kiteless is offline
Moderator
Dev Shed Loyal (3000 - 3499 posts)
  
Join Date: Jun 2002
Location: Raleigh, NC
Posts: 3,480 kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level) 
Time spent in forums: 1 Week 3 Days 17 h 33 m 17 sec
Reputation Power: 42
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?
Reply With Quote
  #3  
Old March 12th, 2004, 09:52 AM
Newby Newby is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Mar 2004
Location: London, Ontario
Posts: 3 Newby User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation 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?
Reply With Quote
  #4  
Old March 12th, 2004, 12:06 PM
kiteless kiteless is offline
Moderator
Dev Shed Loyal (3000 - 3499 posts)
  
Join Date: Jun 2002
Location: Raleigh, NC
Posts: 3,480 kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level) 
Time spent in forums: 1 Week 3 Days 17 h 33 m 17 sec
Reputation Power: 42
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#.
Reply With Quote
  #5  
Old March 13th, 2004, 09:35 AM
Newby Newby is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Mar 2004
Location: London, Ontario
Posts: 3 Newby User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation 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
Reply With Quote
Reply

Viewing: Dev Shed Forums > Programming Languages - More > ColdFusion Development > More ? about multiple Inserts

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

 Free IT White Papers!
 
Accelerating Trading Partner Performance
One in five. That's how many partner transactions have at least one error. That is an amazing statistic, particularly given the extraordinary leaps in innovation across the global supply chain during the past two decades. Download this white paper to learn more.
 
Competing on Analytics
This Tech Analysis is designed to help identify characteristics shared by analytics competitors, and includes information about 32 organizations that have made a commitment to quantitative, fact-based analysis.
 
Cost Effective Scaling with Virtualization and Coyote Point Systems
An overview of the industry trend toward virtualization, how server consolidation has increased the importance of application uptime and the steps being taken to integrate load balancing technology with virtualized servers.
 
Five Checkpoints to Implementing IP Telephony
Implementation planning for IP PBX software and IP telephony has become vital as businesses replace discontinued legacy PBX phone systems. This informative whitepaper outlines five "checkpoints" for any implementation plan that will help make IP communications a successful proposition.
 
Hosted Email Security: Staying Ahead of New Threats
In the last two years, email has become a fierce battleground between the nefarious forces of spam and malware, and the heroes of messaging protection. The spam volumes increased alarmingly every month, bringing clever new forms of phishing and virus propagation attacks.
 

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 4 hosted by Hostway.