SQL error in DELETE statement

[stillwaiting]

I am working on a page that deletes a record from a database. Everything seems to be right to me and I can't figure out what is wrong. The SQL is
identical to another similar page I created which works. Here is the error CF is giving me.
--------------------------------------------
Error Occurred While Processing Request
Error Executing Database Query.
Syntax error or access violation message from server: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near ''johndoe' WHERE id = 9' at line 1"

The error occurred in C:\CFusionMX\wwwroot\picturesbyjulie\deleting_img. cfm: line 9

7 : <cfquery name="delete_img" datasource="picsbyjulie" username="root" password="******">
8 : DELETE FROM '#getusertable.user_table#'
9 : WHERE id = '#URL.id#' <----ERROR HERE
10 : </cfquery>
11 : <cflocation url="edit_pictures.cfm?f=#getusertable.firstname#& l=#getusertable.lastname#&message=img_deleted">

SQL DELETE FROM 'johndoe' WHERE id = 9 <--This is the correct value that should be passed with the URL.
DATASOURCE picsbyjulie
VENDORERRORCODE 1064
SQLSTATE 42000
--------------------------------------------

Here is the complete action page.

--------------------------------------------
<cfif IsDefined("URL.f") AND IsDefined("URL.l") AND IsDefined("URL.id") AND #URL.f# NEQ "" AND #URL.l# NEQ "" AND #URL.id# NEQ "">
<cfquery name='getusertable' datasource="picsbyjulie" username="root" password="*********">
SELECT firstname, lastname, user_table
FROM picsbyj.user_cf
WHERE firstname='#URL.f#' AND lastname='#URL.l#'
</cfquery>
<cfquery name="delete_img" datasource="picsbyjulie" username="root" password="*********">
DELETE FROM '#getusertable.user_table#'
WHERE id = '#URL.id#'
</cfquery>
<cflocation url="edit_pictures.cfm?f=#getusertable.firstname#& l=#getusertable.lastname#&message=img_deleted">
<cfelse>
<cflocation url="edit_pictures.cfm?f=#getusertable.firstname#& l=#getusertable.lastname#&message=failed">
</cfif>
-------------------------------------------

Thanks,
stillwaiting

[Dan Bracuk]

Get rid of the quotes around the tablename.

Originally posted by: Newsgroup User
I am working on a page that deletes a record from a database. Everything seems
to be right to me and I can't figure out what is wrong. The SQL is
identical to another similar page I created which works. Here is the error CF
is giving me.
--------------------------------------------
Error Occurred While Processing Request
Error Executing Database Query.
Syntax error or access violation message from server: "You have an error in
your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near ''johndoe' WHERE id = 9' at line 1"

The error occurred in C:\CFusionMX\wwwroot\picturesbyjulie\deleting_img. cfm:
line 9

7 : <cfquery name="delete_img" datasource="picsbyjulie" username="root"
password="******">
8 : DELETE FROM '#getusertable.user_table#'
9 : WHERE id = '#URL.id#' <----ERROR HERE
10 : </cfquery>
11 : <cflocation
url="edit_pictures.cfm?f=#getusertable.firstname#& l=#getusertable.lastname#&mess
age=img_deleted">

SQL DELETE FROM 'johndoe' WHERE id = 9 <--This is the correct value that
should be passed with the URL.
DATASOURCE picsbyjulie
VENDORERRORCODE 1064
SQLSTATE 42000
--------------------------------------------

Here is the complete action page.

--------------------------------------------
<cfif IsDefined("URL.f") AND IsDefined("URL.l") AND IsDefined("URL.id") AND
#URL.f# NEQ "" AND #URL.l# NEQ "" AND #URL.id# NEQ "">
<cfquery name='getusertable' datasource="picsbyjulie" username="root"
password="*********">
SELECT firstname, lastname, user_table
FROM picsbyj.user_cf
WHERE firstname='#URL.f#' AND lastname='#URL.l#'
</cfquery>
<cfquery name="delete_img" datasource="picsbyjulie" username="root"
password="*********">
DELETE FROM '#getusertable.user_table#'
WHERE id = '#URL.id#'
</cfquery>
<cflocation
url="edit_pictures.cfm?f=#getusertable.firstname#& l=#getusertable.lastname#&mess
age=img_deleted">
<cfelse>
<cflocation
url="edit_pictures.cfm?f=#getusertable.firstname#& l=#getusertable.lastname#&mess
age=failed">
</cfif>
-------------------------------------------

Thanks,
stillwaiting

[stillwaiting]

Duh.
<img src="http://img35.echo.cx/img35/2365/bonk4tt.gif" alt="Hammer 2" />

Whenever it is something difficult, I figure it out. When it is so simple, that's another story. grrrr.

Thanks Dan

> Get rid of the quotes around the tablename.