Database duplicate problem

Guys can anyone help?
I have tried to use the INSERT...ON DUPLICATE KEY UPDATE in Coldfusion. After I run the query my database columns now get filled with the string "thisJD" in the description column, the string "thisTitle" in the Title column etc. They should contain the variable values, not the string.

Here is my code:



Thanks

The problem is definitely here:

on duplicate key update Description ="thisJD", Title = "thisTitle", Salary = "thisSalary", Location ="thisLocation", Hours ="thisHours", Contract ="thisContract", CloseDate ="thisClosDate", IntDate="thisIntDate", CurrentDate="now()"

I'm assuming that "on duplicate key" is similar to Oracle's MERGE, meaning if the record is already there do an update instead of an insert. But you aren't using variables here, you're using literal strings. Try something like this:

on duplicate key update Description ="#thisJD#", Title = "#thisTitle#", etc...

you should also be able to use <cfqueryparam> if you choose. If you have more questions about this command you'd probably have more luck in the MySQL forum. At first glance it seems to me like the update statement also needs a where clause or it will update every row in the table.

Thanks for the reply Kiteless, I do want to update all the fields. I tried inserting the # to use the variables but I get an error:
DataDirect][SequeLink JDBC Driver]Invalid parameter binding(s).
On line 63: on duplicate key update Description ="#thisJD#", Title = "#thisTitle#", Salary = "#thisSalary#", Location ="#thisLocation#"

try using standard sql single quotes, not coldfusion doublequotes

on duplicate key
update Description = '#thisJD#'
, Title = '#thisTitle#'
, Salary = '#thisSalary#'
, Location = '#thisLocation#'
, Hours = '#thisHours#'
, Contract = '#thisContract#'
, CloseDate = '#thisClosDate#'
, IntDate = '#thisIntDate#'
, CurrentDate = now()

and note, no quotes on the function now()

Thank you very much for your time guys, r937 the single quotes worked a treat.