Help! - Syntax error in UPDATE statement

Results 1 to 2 of 2

Thread: Help! - Syntax error in UPDATE statement

  1. #1
    Join Date
    Dec 1969

    Default Help! - Syntax error in UPDATE statement

    Hi,<BR><BR>I hope someone can help me. I&#039;ve searched the FAQ&#039;s,<BR>previous postings to this forum and the Microsoft website<BR>but to no avail.<BR><BR>When I try and update an Access database field I receive the<BR>following error:<BR><BR>Microsoft OLE DB Provider for ODBC Drivers error &#039;80004005&#039;<BR>[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.<BR><BR>The segment of my code that for some reason is causing the problem is:<BR><BR> &#039; Create a recordset object instance, objRSActReference,<BR> &#039; and retrieve all of the columns and rows from the<BR> &#039; ActReference table.<BR><BR> Set objRSActReference = Server.CreateObject("ADODB.Recordset")<BR> objRSActReference.Open "ActReference", objConn, , adLockPessimistic, adCmdTable<BR><BR> &#039; Find record<BR><BR> Do Until objRSActReference.EOF OR bolFound<BR><BR> If objRSActReference("ActID") = intActID Then<BR> <BR> bolFound = True<BR> Else<BR><BR> &#039; Move to the next record in the recordset<BR><BR> objRSActReference.MoveNext<BR> End If<BR> Loop<BR><BR> If bolFound = True Then<BR><BR> &#039; Store the insertion note in the Note field<BR><BR> objRSActReference("Note") = strInsertionNote<BR><BR> &#039; Update the section title record<BR><BR> * objRSActReference.Update<BR><BR> End If<BR><BR>The error occurs at the line marked with the asterisk.<BR><BR>I have used Response.Write statements to check that objRSActReference("Note")is being filled with the data stored in the variable strInsertionNote and it is. I have also used<BR>Response.Write statements to check if the right record is being found to update and it is as well.<BR><BR>Any suggestions very much appreciated.<BR><BR>Regards,<BR><BR>Matthew

  2. #2
    Join Date
    Dec 1969

    Default I don't see the bug, but...

    ...why would you WANT to make that code work???<BR><BR>It&#039;s going to be horribly slow. And it will get slower, the more data is in the table.<BR><BR>Time to learn to use a SQL UPDATE statement:<BR><BR>&#060;%<BR>...<BR>objConn.Execut e("UPDATE ActReference SET [Note] = &#039;" & strInsertionNote & "&#039;" _<BR>&nbsp; &nbsp; &nbsp; & " WHERE ActID=" & intActID )<BR>...<BR>%&#062;<BR><BR>Yep, that&#039;s it. All in one line. And *at least* an order of magnitude faster. Maybe two orders.<BR><BR>And I may have just seen the bug in your original code. Maybe. See how I put [...] around the field name "Note"? I believe that "Note" is a keyword in Access, and so you can&#039;t use it as a field name unless you put [...] around it. Not sure on that, but it can&#039;t hurt to use the [...] for any field, so why not?<BR><BR><BR>

Posting Permissions

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