Here's the function to parse a comma-delimited string:
CREATE FUNCTION dbo.fnc_ParseDelimited
(
@delimitedList VarChar(8000)
)
RETURNS @tblSample TABLE (CounterID Int, FieldValue Varchar(100))
BEGIN
Declare @CounterID VarChar(4)
Declare @FieldValue Varchar(100)
Declare @tmpTable Table (CounterID Int Primary Key, FieldValue
VarChar(100))
Set @CounterID = 1
While CharIndex(',', @delimitedList) > 0
Begin
Set @FieldValue =
LTrim(RTrim(subString(@delimitedList, 1, charIndex(',', @delimitedList)-1)))
Insert Into @tmpTable Select @CounterID, @FieldValue
Set @delimitedList =
LTrim(RTrim(subString(@delimitedList, (charIndex(',', @delimitedList) + 1),
Len(@delimitedList))))
Set @CounterID = @CounterID + 1
End
If LTrim(RTrim(@delimitedList)) != ''
Insert Into @tmpTable Select @CounterID, @delimitedList
Insert Into @tblSample Select * From @tmpTable
Return
END
-----Original Message-----
From: Jake Munson [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 27, 2002 1:47 PM
To: CF-Talk
Subject: Inserting a delimited list into a database
I have a form that returns a comma delimited list of
numbers from some checkboxes that each have the same name.
I want to be able to insert a new record in a database for
each checkbox that was checked in my form. I figured that
if I used a list loop based on my checkbox list, that I
could have a SQL query insert a record for each value in
the list. But it keeps giving me an error on the insert
query. Is it not possible to put a cfquery into a loop?
Is there a better way to do this? Below is my code. Below that is the
error I get.
<CFLOOP INDEX="ServerID" LIST="#servers#" DELIMITER=","> <cfquery
datasource="Software Updates" name="serverUpdate"> INSERT INTO update
(log_id, server) VALUES (#logQuery.id#,#ServerID#) </cfquery> </CFLOOP>
"[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
statement.
The error occurred while processing an element with a
general identifier of (CFQUERY), occupying document position..."
I thought that maybe the problem was that I didn't have a cfoutput around
the #ServerID#, but that wasn't the problem either.
Thanks for the help.
-Jake
__________________________________________________
Do You Yahoo!?
Yahoo! Greetings - Send FREE e-cards for every occasion!
______________________________________________________________________
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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
