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
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,438
    Thanks
    75
    Thanked 4,372 Times in 4,337 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.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • 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
    •