Variable in set SQL field issue help 1

[scon44]

<cfquery DATASOURCE="#Request.DataSource#">
UPDATE tb_name
SET #variables.filename#=
'#cffile.ServerFileName#.#cffile.ServerFileExt#'
WHERE VehicleID=63
</CFQUERY>

I'm getting an error.

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

I've tried putting different types of quotes around the
Basically the set field = something is giving me an error. How do I have the set field where field is a variable that changes? It's going to an Access DB.

 

[philhege]

So variables.filename contains the name of the field in the tb_name table? Have you tried simply outputting the SQL statement to see if it's really what you want?

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Eschewing obfuscation diurnally.

 

[scon44]

Hi Phil,
Thanks for the tip... Basically this sql is in the middle of a loop so the set field1 thur fieldn is what I'm trying to acomplish. But I'm getting an error and I think the variables.filename isn't been evulated before the coldfusion server passes it onto access or something like that... I don't know...

I'm going to have another stab at it now... but any other suggestions are most welcome

 

[scon44]

Ok so what I did to get around this was to use a switch statement for each field name that I would be updating. Rather mudane but it works... I wonder why you can have the field name as a variable? I didn't try cfoutput... maybe that is it?

 

[philhege]

UPDATE statements require that you name each field and its SET value individually. I'd suggest you construct the SQL statement first using CFLOOP, and then pass the statement to one CFQUERY tag.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Eschewing obfuscation diurnally.