Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 649
  • Last Modified:

SQL Update of Memo field

How do I update an Access 2000 "Memo" field using SQL?

I am trying to edit an Access database using ASP/ADO, and every time I try to save a <textarea> form field using a SQL Update statement, I get the error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
/d2/admin/modpage.asp, line 194

If I replace the <textarea> with a textbox input, this doesn't help. If I hard-code a string (eg "test") into the script, I still get the error.

Concatenating strings doesn't help.

The relevant bit of code is:

sqlUpdateText = "UPDATE Page SET Text = '" & Request.Form("edit_text") & "' WHERE Page_ID = " & this_edit_id & " "

this_edit_id is the page numer that I want to edit. I'm sure there's no problem with this bit, as I use it for several SQL statements, which don't throw errors.

Request.Form("edit_text") is the <textarea>. -- This is where the prblem lies.

Any ideas? Thanks...

  • 2
1 Solution
Try throwing Text into brackets:

"UPDATE Page SET [Text] = '" & Request.Form("edit_text") & "' WHERE Page_ID = " & this_edit_id
apreedAuthor Commented:
That's great! Thanks very much. You've saved the rest of my hair...

Why does this work?
I couldn't find anything on the web that suggested anything like this as a solution.
Glad to help.

When in doubt, qualify your fields (Page.Text should work as well). The Access query window is a good troubleshooting tool. To use, copy-and-paste the code into the SQL view and execute. The application will try to determine the first point of failure, and highlight the relevant part of your query where possible.

Why? Have to ask Bill. Text is a reserved word, but other reserved words behave differently; the unqualified reference works in a SELECT query without issue, so...

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now