SQL Update error: 80020009 1

[ro6er]

Hi, I'm having some issues with using update on my form page. It might be something simple but i've been looking at it for days now and can't figure it out. I've tried loads of things which i'll now list. Here is the code for the first form page:

<form method="post" enctype="multipart/form-data" action="upload.asp">

<%Session("ID") = rsBuckles("ID")%>

  <input type="file" size="40" name="file1"><br />
  <input type="submit" value="Upload!">
</form>

Here is the second part:

<!--#include file="database.asp"-->
<!--#include file="cls_CBUpload.asp" --><%
  dim Upload, File, Item, iTimer
  iTimer = Timer()
  set Upload = new cls_CBUpload
  Response.Write("File count: " & Upload.Files.Count)
  Response.Write("<br /><br />")
  for each File in Upload.Files
    set File = Upload.Files(File)

    Response.Write(Server.Mappath("/buckles/buckleimages/" & File.Name) & "<br />")
    Response.Write(File.Size & " bytes<br />")
    Response.Write(File.FormName & "<br />")
    Response.Write(File.ContentType & "<br />")
Response.Write ("<br><br>")

'Add the image name to the database
mySQL = "UPDATE "&Session("edit")&" SET Image = '"&File.Name&"'" _
		& " WHERE ID = '"&Session("ID")&"'"

Set RS = Conn.Execute(mySQL)  
	
    if(File.SaveToFile(Server.Mappath("/buckles/buckleimages/" & File.Name))) then
      Response.Write("Saved file...<br />")
    end if
    Response.Write("<br />")
  next
  for each Item in Upload.Form
    Response.Write(Item & ": " & Upload.Form(Item) & "<br />")

  next
  set Upload = nothing
  Response.Write(Timer - iTimer)

%>

i know the data is being past across ok as I checked with the following code on the second part:

mySQL = "UPDATE "&Session("edit")&" SET Image = '"&File.Name&"'" _
		& " WHERE ID = '"&Session("ID")&"'"
response.write mySQL 
response.end 
Rs.Open mySQL, Conn 
Conn.Close 
Set Rs=Nothing

Which brings back

UPDATE flagsnposters SET Image = 'test.jpg' WHERE ID = 23

Which seems fine.

The database table looks like this:
ID Autonumber
Description Memo
Subcat Text
Image Text
catagories Text
price Text

The exact error message is:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in UPDATE statement.
/buckles/admin/upload.asp, line 23


Browser Type:
Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; SV1)

Page:
POST 29628 bytes to /buckles/admin/upload.asp

POST Data:
error '80020009'
Exception occurred.

/iisHelp/common/500-100.asp, line 223

As well line 23 is:

Set RS = Conn.Execute(mySQL)

I've also tried running the update statement directly in the database which seems to work fine

Any idea will be greatly recieved.

Thanks,

Roger

 

[DotNetGnat]

try this:

mySQL = "UPDATE "&Session("edit")&" SET Image = '"&File.Name&"'" _
& " WHERE [ID] = '"&Session("ID")&"'"

with square braces around ID

-DNG

 

[DotNetGnat]

one other thing: if your ID is an integer then do this:

mySQL = "UPDATE "&Session("edit")&" SET Image = '"&File.Name&"'" _
& " WHERE [ID] = " & Session("ID")

-DNG

 

[ro6er]

Thanks DNG, Nope still doesn't process. I also tried it without apostrophes around "&Session("ID")&" and still no luck.

 

[ro6er]

Just tried

mySQL = "UPDATE "&Session("edit")&" SET Image = '"&File.Name&"'" _
        & " WHERE [ID] = " & Session("ID")

and still nothing

I can get this sql to work ok:

mySQL = "Select * from "&Session("edit")&" where ID = "&Session("ID")&""

 

[DotNetGnat]

how about this:

mySQL = "UPDATE "&Session("edit")&" SET [Image] = '"&File.Name&"'" _
& " WHERE [ID] = " & Session("ID")

-DNG

 

[DotNetGnat]

both ID and Image are reserved words...so just wrap them with square braces...

-DNG

 

[ro6er]

Thats the one, thanks. I'm going to get a tattoo of all the reserved words now.

Roger

 

[DotNetGnat]

get tattoed here

http://www.aspfaq.com/show.asp?id=2080

-DNG