Basic SQL

[James@nps]

I have this bit of code:

<cfif day eq '_1'>
<cfquery name = "update" dataSource = "KFS">
UPDATE tbl_Legends
SET _1 = '#description#'
WHERE Month = '#month#' and Year = '#year#'
</cfquery>

I currently have 31 if statements to make this work. It would be cleaner to
use a loop. My trouble is mssql (or coldfusion?) dose not allow the use of a
variable name for the column. I would like to do this.

<cfif day eq #x#>
<cfquery name = "update" dataSource = "KFS">
UPDATE tbl_Legends
SET #x# = '#description#'
WHERE Month = '#month#' and Year = '#year#'
</cfquery>

I am sure this is somthing easy but I have googled for hours and only find
stored procedures as a solution. Any help would be great.

[Dan Bracuk]

Try this, but with better syntax.

switch(day){
case "1":
x="field1";
break;
etc

<cfquery name = "update" dataSource = "KFS">
UPDATE tbl_Legends
SET #x# = '#description#'
WHERE Month = '#month#' and Year = '#year#'
</cfquery>



Originally posted by: James@nps
I have this bit of code:

<cfif day eq '_1'>
<cfquery name = "update" dataSource = "KFS">
UPDATE tbl_Legends
SET _1 = '#description#'
WHERE Month = '#month#' and Year = '#year#'
</cfquery>

I currently have 31 if statements to make this work. It would be cleaner to
use a loop. My trouble is mssql (or coldfusion?) dose not allow the use of a
variable name for the column. I would like to do this.

<cfif day eq #x#>
<cfquery name = "update" dataSource = "KFS">
UPDATE tbl_Legends
SET #x# = '#description#'
WHERE Month = '#month#' and Year = '#year#'
</cfquery>

I am sure this is somthing easy but I have googled for hours and only find
stored procedures as a solution. Any help would be great.

[locriann@gmail.com]

That query works fine for me, but your CFIF statement will error every
time. Use IS instead of EQ when you're not dealing with numbers or
true/false values.

[James@nps]

I will try this, hope it works, but you are using the same update statement I
did.

SET #x# = '#description#'

The variable #x# crashes the code every time, not sure running it in a switch
first will help that. I will give this a try just dont see the diffrence.

[mxstu]

>My trouble is mssql (or coldfusion?) dose not allow the use of a variable

>name for the colum
> SET #x# = '#description#'

You can use a cf variable for a column name in most situations, but the result
may also depend on the variable values. You say it is not working. What is
the exact error message you are receiving (including the sql statement sent to
the database)?

On a side note, "Month" and "Year" are the names of ms sql functions so they
are not a great choice for column names and neither is "_1" because it starts
with an underscore (IMO).

[Dan Bracuk]

If variable names can't be used in this situation, you can always try a
variable clause
update tablename
#preservesinglequotes(setclause)#,
or make the entire sql a variable.

I've done the last one. It works.

[mxstu]

Well, from what I've seen so far it shouldn't be neccessary to use
PreserveSingleQuotes here, but without seeing the actual error it is hard to
tell. One downside to using PreserveSingleQuotes() is that it can can open up
the risk of sql injection.

[James@nps]

Thanks guys, I have to put this on hold for a day or two. It is working fine
but I wanted to clean to make it easier to update and to just be coded better.
I think I will try to put the sql into a variable sounds like that should work.
Thanks for the quick reply, the lesson on variables, and ideas on how to get my
code to work. Not sure how long threads stay on the boards here so if this one
is still up in a few days I will post an update. Thanks again!

[mxstu]

Keep in mind that utting the entire sql string in a variable doesn't always
make things easier, and can sometimes makes syntax problems harder to identify.
Another downside is that you cannot cfqueryparam which takes advantage of bind
variables (which ms sql should supports) which can enhance performance. Just
another thing to keep in mind...