The fastest fix, from the example you provided, is to put pounds around the
evaluate function otherwise you are trying to put a large string into you join
column.
But better yet may be something like this:
<cfloop query="FindAttributes">
<cfif form["attribute_" & FindAttributes.attributeID] EQ "true">
<cfquery datasource="user020">
INSERT INTO
tbl_020publicProfilesAttribes
(profileID,attributeID)
VALUES (
#InsertProfileDetails.profileID#,
# form["attribute_" &
FindAttributes.attributeID]#
</cfquery>
</cfif>
</cfloop>
While writing this to show how to eliminate the need for the evaluate functions
by using structure and array notation I noticed that the form variable in the
If statement was different then the one in the insert statement. One has an
underscore "_" and the other does not. Is this not an error?
--------------
Ian Skinner
Web Programmer
BloodSource
www.BloodSource.org
Sacramento, CA
"C code. C code run. Run code run. Please!"
- Cynthia Dunning
....-----Original Message-----
....From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED]
....Sent: Thursday, July 14, 2005 4:13 PM
....To: CF-Talk
....Subject: Re: 2 Primary Keys
....
....My problem is that i have a form on a dating site where when users
....sign up i create a row for them in my profile table. When collating
....their profile I also ask them what type of dates they'd like to meet,
....some of which are love, casual, friendship, travel mate. Each of
....these attributes are held in an attributes table. When i have taken
....all the user info i create the rows in the two tables. For the
....profile info it's pretty straight forward, but for the attributes i
....am finding it quite hard.
....
....i'll show you what i am doing.
....
.... <!--- Insert profile details --->
.... <cfquery name="InsertProfileDetails" datasource="user020">
.... SET NOCOUNT ON
.... INSERT INTO tbl_020publicProfiles (profileText,
....profileTitle, screenName, userID, acceptImages, dateSubmitted,
....location, age, gender, genderSeeking, confirmationID, profileIPAddress)
.... VALUES ('#Form.profileText#', '#Form.title#',
....'#Form.screenName#', #CheckUser.userID#, #Form.acceptImages#,
....#UKtodayDate#, #Form.location#, '#Form.age#', '#Form.gender#',
....'#Form.genderSeeking#', '#confirmationID#', '#cgi.REMOTE_ADDR#')
.... SELECT @@IDENTITY as profileID
.... SET NOCOUNT OFF
.... </cfquery>
....
.... <!--- find all date types --->
.... <cfquery name="FindAttributes" datasource="user020">
.... SELECT attributeID
.... FROM tbl_020publicAttributes
.... </cfquery>
....
.... <!--- roll through FindAttributes query inserting the values
....into the table --->
.... <cfoutput query="FindAttributes">
.... <cfif evaluate('form.attribute' & '_' &
....FindAttributes.attributeID) eq 'true'>
.... <cfquery name="InsertProfilesAttributes"
....datasource="user020">
.... INSERT INTO tbl_020publicProfilesAttributes (profileID,
....attributeID)
.... VALUES (#InsertProfileDetails.profileID#, evaluate
....('form.attribute_' & 'FindAttributes.attributeID'))
.... </cfquery>
.... </cfif>
.... </cfoutput>
....
....As you can see i'm having problems with putting the attributes values
....in as i am finding that you can't use the CF attribute function in an
....SQL statement.
....
....I am also wondering if there is any easier way to all this then the
....way i am doing it. Anyone have any ideas or am i doing it right?
....
....Any help at this point would be really helpful.
....
....Thanks,
....
....Stuart
....
....
....
....On 14 Jul 2005, at 23:47, Aaron DC wrote:
....
....> It sounds like you are enforcing a 1 to 1 restriction but storing the
....> link in a 1 to many or many to many db design. If table1 can only ever
....> be linked to 1 table2 record and vice versa, wouldnt it be easier to
....> store the table1ID in the table2 as a field (or vice versa)? All your
....> queries would have 1 less join, etc.
....>
....> Aaron
....>
....> Saturday (Stuart Kidd) wrote:
....>
....>
....>> Hi Jordan,
....>>
....>> Yes, more than one field needs to be a primary key. I'm using MS-SQL
....>> (on shared hosting).
....>>
....>> Stuart
....>>
....>>
....>> On 14 Jul 2005, at 23:07, Jordan Michaels wrote:
....>>
....>>
....>>
....>>
....>>> Saturday (Stuart Kidd) wrote:
....>>>
....>>>
....>>>
....>>>
....>>>
....>>>> Hi guys,
....>>>>
....>>>> I want to add a row in a join table which i don't want to be
....>>>> duplicated ever.
....>>>>
....>>>> So i have my tableID, table1ID, table2ID - if table1ID = 24 and
....>>>> table2ID = 35 i don't want there to ever be a reoccurence of
....>>>> them. I
....>>>> guess i have to set both of those fields (table1ID, table2ID) to
....>>>> primary keys, but how can i do that? Do i do it in ms-sql or via
....>>>> Coldfusion somehow? If i do it via MS-SQL then won't it pop an
....>>>> error
....>>>> up in the code if it happens?
....>>>>
....>>>> Thanks,
....>>>>
....>>>> Saturday
....>>>>
....>>>>
....>>>>
....>>>>
....>>>>
....>>>>
....>>> Are you talking about compound keys? Where more then one field is
....>>> the
....>>> primary key? This can be done in most databases - even access if I
....>>> remember correctly.
....>>>
....>>> -JM
....>>>
....>>> --
....>>> Warm regards,
....>>> Jordan Michaels
....>>> Vivio Technologies
....>>> http://www.viviotech.net/
....>>> [EMAIL PROTECTED]
....>>>
....>>>
....>>>
....>>>
....>>>
....>>
....>>
....>>
....>
....>
....
....
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211937
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
