Jim,
Don't know what DB you're using, but I implemented the following for a similar
situation once. This works with MS SQL Server.
<cftry>
<cfquery name="updStats" datasource="#attributes.DSN#">
SET NOCOUNT ON
UPDATE ListStats
SET Hits = MsgsIn + 1
WHERE ListID = #attributes.ListID#
SELECT RowsAffected = @@ROWCOUNT
SET NOCOUNT OFF
</cfquery>
<cfif updStats.RowsAffected EQ 0>
<cfquery name="insStats" datasource="#attributes.DSN#">
INSERT INTO ListStats (ListID, MsgsIn)
VALUES (#attributes.ListID#, 1)
</cfquery>
</cfif>
<cfcatch type="Database">
<!--- Ignore DB errors --->
</cfcatch>
</cftry>
In case it's not clear what is going on, the first query tries to update a log record
based on the ListID key. @@ROWCOUNT returns how many rows were affected by the
operation. Then if the rowcount is zero that means we need to insert a record, which
the 2nd query takes care of.
Of course this would make a nice stored proc. I'm not sure if there are any drawbacks
with this method (only one I can think of is if ROWCOUNT returns an incorrect value
because of a parallel-executing query in the same DB connection). Also obviously the
cfcatch could have some more useful error handling :)
Cheers,
-Max
-----------------------------
Maxim Paperno, CTO
World Design Group, Inc.
[ CF Development and Hosting since 1996 ]
<http://www.WorldDesign.com/>
______________________________________________________________________
Why Share?
Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation · $99/Month · Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
