I think your approach is a bit too simple. I don't know what your form
looks like, or what this test USERS NEQ "No Users" means, but it should
probably look like this.
=======================================
<CFIF USERS NEQ "No Users">
<cfquery name="qryPeople" datasource="#dsn#">
SELECT UserID, AppID
FROM qryAppToEmp2
WHERE UserID IN (#form.Users#)
AND AppID IN (#form.appID#)
ORDER BY UserID
</cfquery>
<!--- Create a structure with a list of appIDs for each UserID
found. --->
<cfset stUserApps = StructNew()>
<cfoutput query="qryPeople" group="UserID">
<cfset stUserApps[qryPeople.UserID] = "">
<cfoutput> <cfset stUserApps[qryPeople.UserID]=
ListAppend(stUserApps[qryPeople.UserID],AppID)>
</cfoutput>
</cfoutput>
<cfloop list="#form.Users#" index="i" delimiters=",">
<cfloop list="#form.appid#" index="p" delimiters=",">
<cfif NOT(StructKeyExists(stUserApps,i) AND
ListFind(stUserApps[i],p))>
<!--- Only insert if the appID is not in the
list for that user --->
<cfquery datasource="#dsn#" name="addUser">
INSERT INTO tblAppIAUsers
(
AppID,
Listing_ID
)
VALUES
(
#p#,
#i#
)
</cfquery>
</cfif>
</cfloop>
</cfloop>
</cfif>========================================
Pascal Peters
Macromedia Certified Instructor
Certified ColdFusion (5.0) Advanced Developer
Certified Web Developer
LR Technologies, Belgium
Tel +32 2 639 68 70
Fax +32 2 639 68 99
Email [EMAIL PROTECTED]
Web www.lrt.be
-----Original Message-----
From: Jones, Becky [mailto:[EMAIL PROTECTED]]
Sent: vrijdag 16 november 2001 14:29
To: CF-Talk
Subject: insert question
i have a list of users that you can choose from on a form to assign to
an
application(s).
if the user is already assigned to a certain app however, i dont want
that
user to re-insert into the database. (infact, the dbase wont let that
happen
because of duplicate indexes) but if the second user isnt already
assigned
to the app...i do want him to insert. but it doesnt seem to be working
out.
any ideas?
my qryPeople is this:
<CFIF USERS NEQ "No Users">
<cfquery name="qryPeople" datasource="#dsn#">
SELECT *
FROM qryAppToEmp2
WHERE UserID IN (#form.Users#)
AND AppID IN (#form.appID#)
</cfquery>
</cfif>
my insert:
<cfif isdefined("qryPeople.recordcount") AND qryPeople.RecordCount IS 0
OR
qryPeople.UserID neq "No Users">
<cfloop list="#form.Users#" index="i" delimiters=",">
<cfloop list="#form.appid#" index="p" delimiters=",">
<cfquery datasource="#dsn#" name="addUser">
INSERT INTO tblAppIAUsers
(
AppID,
Listing_ID
)
VALUES
(
#p#,
#i#
)
</cfquery>
</cfloop>
</cfloop>
</cfif>
*************************************************
This e-mail, including any attachments, is intended for the
receipt and use by the intended addressee(s), and may contain
confidential and privileged information. If you are not an intended
recipient of this e-mail, you are hereby notified that any unauthorized
use or distribution of this e-mail is strictly prohibited.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
