Something like this might help Becky, it will just skip the insert if a
corresponding record already exists.
<cfloop list="#form.Users#" index="i" delimiters=",">
<cfloop list="#form.appid#" index="p" delimiters=",">
<CFQUERY DATASOURCE="#request.dsn#" NAME="qlogin">
BEGIN TRANSACTION
IF NOT EXISTS (select AppID from tblAppIAUsers where Listing_ID = #i# and
AppID = #p#)
BEGIN
INSERT INTO tblAppIAUsers
(AppID,Listing_ID)
VALUES
(#p#,#i#)
END
COMMIT TRANSACTION
</CFQUERY>
</cfloop>
</cfloop>
Doing the loops inside the query/stored proc is beyond me atm, but it will
be possible with cursors if it's really necessary.
Cheers, Craig.
-----Original Message-----
From: Jones, Becky [mailto:[EMAIL PROTECTED]]
Sent: 16 November 2001 13: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.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
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
