loop over your date list doing a different insert each iteration.
<cfloop list=datelist index=date>
<cfquery....>
INSERT INTO table
(field1,field2,field3)
value1,value2,date
</cfquery>
</cfloop>
Also, the CFTRANSACTION tag is useful when you have complex, multipart
database transactions. It allows you to do several inserts or other
database transactions, and if any one of them fail, they all fail. Helps
prevent problems, where you might be inserting data first into Table A, then
related data into Table B. With out a transaction block, the insert into
table A could be successful, but then the insert into Table B fails for
whatever reason, you would now have incomplete data only in table A. If
they where both within a transaction block, then the failure of the insert
into Table B, would cancel the insertion into Table A, maintaining the data
integrity.
--------------
Ian Skinner
Web Programmer
BloodSource
www.BloodSource.org
Sacramento, CA
-----Original Message-----
From: mayo [mailto:[EMAIL PROTECTED]
Sent: Monday, October 13, 2003 9:33 AM
To: CF-Talk
Subject: RE: multiple inserts, no solution
Jeremy,
when I insert one date at a time there is no problem. The problem comes
when the user needs to insert multiple dates for a particular clas.
classTitle: Guitar for Beginners (text box)
classTime: 12:00-2:00PM (select list)
classDate:
user chooses Oct 1, Oct 8, Oct 15, Oct 22
OK now, with one click the user should be able to enter this class.
On the database end I should have four records
classDatesID (primary key)
classID (foreign key)
classDate
classTime
I haven't been able to loop through the inserts correctly. Presently it
tries to stuff all the dates into the same field as seen below:
>INSERT INTO classDates
> ( classID, classDay, classSession, classDates, classTime )
>VALUES ( '5', 'Mon', '1', '2003,2003,2003,2003/10,10,10,10/1,8,15,22',
>'12:00-2:00' )
I'll look into the transaction tag but I thought it had more to do with
multiple insertions by multiple people and ensuring that one transaction was
finished before starting another.
thx,
Gil
-----Original Message-----
From: Jeremy Brodie [mailto:[EMAIL PROTECTED]
Sent: Monday, October 13, 2003 11:55 AM
To: CF-Talk
Subject: Re:multiple inserts, no solution
Gilbert,
1) Use the CreateODBCDate function
i.e CreateODBCDate(createdate(form.year,form.month,form.day)) This will
fix the date formating problem
2) Think about using a seperate lookup table associating classes with
times. This way you can CFLoop around the infoirmation and pull the dates
into the database correctly using the CreateODBCDate function. This way you
can have as many dates as needed for each class.
You should be able to loop over the list and insert the transaction, as
long as you have CFTRANSACTION tags and a CFTRY/CFCATCH block around the
insert
To pull the items from the database for the dates later all you'll need to
do is use a inner join.
3) Are you calling CFSET statement correctly? <cfset
#FORM.month##getClassDate# ... outputs at 101 102 103. If you use <cfset
#evaluate("FORM.month#getClassDate#")> This will output the variable you
passing month1 correctly.
In my pervious example it would be
CreateODBCDate(createdate(evaluate("form.year#getClassDate#"),evalaute("form
.month#getClassDate#",evalaute("form.day#getClassDate#"))
Jeremy Brodie
Intelix
an Edgewater Technology Solutions Company
web: http://www.edgewater.com <http://www.edgewater.com>
phone:(703) 815-2500
nasdaq symbol: EDGE
>BACKGROUND INFO:
>
>I'm creating an admin form allowing a user to enter class schedules.
>
>Class Title, Class Description, Class Time, Class Price are all simple
>inserts.
>
>The problem comes in when entering the class dates. There are sometimes
one
>class, sometimes two, four or six. Often times they will be one week
apart,
>but not always.
>
>example: Class 1 will meet at 2PM on Tuesday the 1st, the 8th, the 15th,
the
>22nd.
>
>The admin page allows the user to put in the dates desired by clicking on
>the appropriate calendar day.
>
>
>PROBLEM:
>
>One "click" should allow the user to input 2 or more class dates.
>
>A straight loop <cfloop from="1" to="#classQty#"> tries to input multiple
>class days into the classDate field.
>
>example of error below:
>
>INSERT INTO classDates
> ( classID, classDay, classSession, classDates, classTime )
>VALUES ( '5', 'Mon', '1', '2003,2003,2003,2003/10,10,10,10/1,8,15,22',
>'12:00-2:00' )
>
>
>SOLUTION ???:
>
>I had thought that I could do something like the following
>
><cfoutput>
><cfloop from="1" to="#FORM.number#" index="getClassDate">
><cfset
>date=#FORM.month##getClassDate#&"/"&#FORM.day1##getClassDate#&"/"&#FORM.yea
r
>1##getClassDate#>
>
> <cfquery name="" datasource="">
>
> INSERT INTO classDates
> (
> classID,
> classDay,
> classSession,
> classDates,
> classTime
> )
> VALUES
> (
> '#FORM.classID#',
> '#FORM.classDay#',
> '#FORM.classSession#',
> '#date#',
> '#FORM.classTime#'
> )
>
></cfquery>
></cfloop>
></cfoutput>
>
>I see why it doesn't work. So, how does one do multiple inserts of this
>kind?
>
>
>Gilbert Midonnet
>
>
>
>
>
>
>
>
>
>
>
>
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
