Passing a variable to a query

7 replies
1006 views
Translate

<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

Database access

This topic is closed to new replies. Start a new post to keep the conversation going.

X

xSistCorrect answer

Inspiring

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

J

JaredJBlackburn

Inspiring

When you use (#evaluate(thisColumn)#)value('#i#'), CF is concatenating 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.

Translate

A

Anonymous

It's value s, not value.

Translate

X

xSistAuthorCorrect answer

Inspiring

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

Translate

J

JaredJBlackburn

Inspiring

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.

Translate

X

xSistAuthor

Inspiring

... 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. "

The column "SubCat_1, SubCat_2 ..." positively exist in the table "maintank"
ThankYou

Translate

X

xSistAuthor

Inspiring

Translate

A

Anonymous

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

Translate

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.