Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Nov 2007
    Posts
    682
    Thanks
    319
    Thanked 1 Time in 1 Post

    Updating records, it should work :S

    Response:
    Code:
    UPDATE images SET delete = 1 WHERE id = 325
    Error updating image 325's record.
    Syntax error in UPDATE statement.
    
    UPDATE images SET delete = 1 WHERE id = 326
    
    Error updating image 326's record.
    Syntax error in UPDATE statement.
    ASP:
    Code:
    <%
    'Get image id's to delete
    ids = request.querystring("ids")
    ids = split(ids, ",")
    
    'Create connection
    set conn=Server.CreateObject("ADODB.Connection")
    conn.Provider="Microsoft.ACE.OLEDB.12.0"
    conn.Open Server.MapPath("/nightclub_photography/data/database/jamsnaps.mdb")
    
    for each x in ids
    	check = "SELECT * FROM images WHERE id = " & x
    	set rs = conn.Execute(check)
    	
    	'Check to see if image has been sold, if so can't delete
    	if rs("sold") = 0 then
    		sql = "UPDATE images SET delete = 1 WHERE id = " & x
    		response.write sql
    		on error resume next
    		set rd = conn.Execute(sql)
    	else
    		response.write "Error: Unable to delete some images as they had sales!<br />"
    	end if
    	if err<>0 then
        	response.write("<p>Error updating image " & x & "'s record.<br />"&Err.Description&"<p>")
      	else
        	response.write("Record " & x & " was updated!<br />")
      	end if
    next
    Last edited by martynball; 07-01-2013 at 08:43 PM.

  2. #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    29,698
    Thanks
    93
    Thanked 4,947 Times in 4,908 Posts
    DELETE is a KEYWORD in SQL.

    So you must "escape" it when you use it as a field name.

    Put [ ] around it:
    Code:
        sql = "UPDATE images SET [delete] = 1 WHERE id = " & x
    (Or do what I do: *NEVER* use keywords as field names. Adapt. In this case, I probably would change the field name in the DB to "dodelete" or similar.)
    Be yourself. No one else is as qualified.

  3. Users who have thanked Old Pedant for this post:

    martynball (07-01-2013)


 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •