Passing a variable to a query

[xSist]

<cfset ctr=1>
<cfloop list="#query1#" index = "i" delimiters = ",">
<cfset thisColumn = "subcat_"&#ctr#>
<cfquery name="insertNodes" datasource="#dsn#">
INSERT into maintank
(.....)value('#i#')
</cfquery>
<cfset ctr=ctr+1>
</cfloop>

keep getting "subcat_1 does not exist".
evaluate()? - ThankYou

[jdeline]

I see where subcat_1 is constructed; where is it (or thisColumn) used?

[JaredJBlackburn]

Please post the error that you're getting. I'm curious if it's a CF error complaining that a variable doesn't exist or a SQL error complaining that a column doesn't exist.

[xSist]

.... clarifying, sorry

if I use
<cfquery ...
(#evaluate(thisColumn)#)value('#i#')

I get the error : "Variable subcat_1 is undefined. "

using :
<cfquery ...
(#thisColumn#)value('#i#')

I get the error : " Error Executing Database Query. Syntax error in INSERT
INTO statement. "
ThanYou

[xSist]

.... found and duly noted. It should be:
INSERT into maintank
(#Evaluate("subcat_#i#")#)value('#i#')

BUT

#i# should be one of the elements of the list
"1036592|Apparel & Accessories,1036682|Departments"
comma delimited
and I get the error: "Invalid token '|' found on line ..."
It looks like a CF error ...

[JaredJBlackburn]

When you use (#evaluate(thisColumn)#)value('#i#'), CF is concatinating the
strings 'thisColumn' and '1' and then trying to evaluate the resulting string
'thisColumn1' as if it were a CF variable. Since you have no CF variable by
that name, it's giving you an error.

The second way appears to be closer to what you want. Here, the CF server
isn't crashing, but the SQL server is returning an error when it tries to
handle your cfquery call. First, if you haven't already, turn on your debug
output and see what SQL statement you're actually sending to the SQL server.
The error message indicates that it's a syntax error which is probably just
some type-o in the resulting SQL.

[jdeline]

It's values, not value.

[xSist]

ThankYou "jdeline" and "JaredJBlackburn".
Got it to work, but it was much worse than what you have read.
I swear I didn't drink and worls soccer Italy is ahead of Ghana so far.
I am under heavy loads or full moons.
All the best.
ContiW