What is causing syntax error in UPDATE statement?

I have a web page that is supposed to update a record in an access Database. I am using Dreamweaver MX 2004 to create the page.
When I select the page, the correct item comes up in the form. When I try to make a change in any of the fields and click the update record button
I get the following error.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
/UpdateCAR.asp, line 81

I am not a programmer. Can anyone tell me what is wrong? I have attached the complete code of the web page
so that you can see what line 81 is.

Any help will be greatly appreciated.
Thank you.
Rick
ES-ComponentsAsked:
Who is Participating?
 
_agx_Commented:
Disclaimer - I can't test the code, so this is just an educated guess...

The code builds 2 "|" delimited strings.  Looks like one for values and the other for available columns. Those lists are probably what controls which columns get updated.

     var MM_fieldsStr = "Status|value|CarNum|value|DateIssued|value|.... etc.....
     ....
     var MM_columnsStr = "Status|',none,''|CarNum|none,none,NULL|DateIssued|',none,NULL|.... etc.....

You probably need to remove the "CarNum" elements from BOTH of those two variables.  The part in bold below. Note that includes the trailing "|"

  var MM_fieldsStr = "Status|value|CarNum|value|DateIssued|value|.... etc.....
  var MM_columnsStr = "Status|',none,''|CarNum|none,none,NULL|DateIssued|',none,NULL|.... etc.....


Again, don't forget to backup the file first
0
 
Martin MillerCTOCommented:
Rick,

I don't see the page of code, try again to attach or embed.
0
 
ES-ComponentsAuthor Commented:
CodeUpdateRecord.docx
I uploaded the code again, and also embeded the file.

Thank you
Rick
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Be best if you could output your Update query string and post it here.
0
 
Martin MillerCTOCommented:
Rick,

I feel you issues starts on the line with:

 var MM_editCmd = Server.CreateObject('ADODB.Command');

Open in new window


The Server.CreateObject is returning an error and the code is continuing with regards to the error.

Your database server connection is not properly initialized, it's the best I can see from this code segment. Perhaps another expert will add more color to this.
0
 
_agx_Commented:
My asp is rusty, but my take on the error message was that the connection did try and execute the SQL string, but there's something wrong with the sql. Can you try and print out the SQL string before it's executed, like Scott McDaniel suggested?  

I think adding a Response.Write, just inside the if statement should do it. Make a backup copy of the file first!.

  if (!MM_abortEdit) {
   Response.Write(MM_editQuery)
    // *** rest of code as is... .....
  }


EDIT: Obviously, review the printed SQL string before posting it here and sanitize if needed.
0
 
ES-ComponentsAuthor Commented:
Can you give me exactly what line this is on??? You are telling me to add a Response.Write. I found this:

if (!MM_abortEdit) {

Where do I put:  Response.Write(MM_editQuery)????
0
 
_agx_Commented:
Just add it on the line directly after that if statement.  Everything else remains the same.

Before:

if (!MM_abortEdit) {

After:


if (!MM_abortEdit) {
   Response.Write(MM_editQuery)
0
 
ES-ComponentsAuthor Commented:
Here is what I did and the results. Any other solutions??

if (!MM_abortEdit) {
      Response.Write(MM_editQuery)
    // execute the update
    var MM_editCmd = Server.CreateObject('ADODB.Command');
    MM_editCmd.ActiveConnection = MM_editConnection;
    MM_editCmd.CommandText = MM_editQuery;
    MM_editCmd.Execute();
    MM_editCmd.ActiveConnection.Close();

    if (MM_editRedirectUrl) {
      Response.Redirect(MM_editRedirectUrl);
    }
  }

RESULTS
update CAR set Status = 'Open',CarNum = 438,DateIssued = '12/15/2014',[Issued by] = 'Cheryl Marin',Title = 'QA Supervisor',Company = 'Internal to ESC',Contact = 'Frank Marhefka',Phone = '978-206-6571',Fax = '',Email = 'FMarhefka@escomponents.com',Manufacturer = 'VS SC275H100S5P',[Part Number] = 'NO CUSTOMER SCD',[ESC ID#] = '64006',PO# = '4441',[RMA #] = 'n/a',[Quantities Affected] = '000000',[Description of Nonconformance] = 'Customer requirement on the ESC sales order S1052028, under Shipping Instructions: For traceability, wafer number and diffusion number required on invoice and labels. B. VISHAY''S SIGNED CERTIFICATE OF COMPLIANCE LISTING THE FOLLOWING 1. THE PART NUMBER 2. DIE LOT AND WAFER IDENTIFICATION 4. COUNTRY OF ORIGINCustomer did not receive the correct Vishay C of C. This is a repetitive problem that happens on almost every shipment to Unisem. There are currently open orders on the system for Unisem. ',Containment = 'No material left in stock for that customer to contain. ',[Root Cause] = 'no update at this time.',CandP = 'no update at this time.',[Corrective Action Date] = '10/12/2015',[Due Date] = '1/15/2015',[Reference Number] = 'S1052028.001 ',Address1 = '108 Pratts Ject. Road',Address2 = '',City = 'Sterling',State = 'MA',Zip = '01520',CityStateZip = '',Quan1 = '1818',Lot1 = 'HPNL045.2',Quan2 = '685',Lot2 = 'HPNL045.3 ',Quan3 = '2031',Lot3 = 'HPNF312.2',Branch = '1',ClosedDate = NULL,CompletedBy = 'Cheryl Marin',ToFrom = 'To',ReviewOfEffectiveness = NULL where CarNum = 438

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
/CARUpdate.asp, line 82

Thank you...
Rick
0
 
_agx_Commented:
Thanks.  That wasn't intended to be a solution ;-)  The code is building the SQL query dynamically.  Therefore, it's  impossible for anyone to identify the cause of the error without seeing the generated SQL string. That's what we just had you do - posted the actual SQL query that's causing the error.

Now that we can see it, the problem is likely the "PO#" column. Characters like "#", spaces, etc... aren't valid for column names. It's not recommended, but if you really want use them, you have to escape the column name by enclosing it in square brackets.  Your code does that for some of the columns, but not the PO# column:

...
,[ESC ID#] = '64006'   <!--- Escaped properly
,PO# = '4441'             <---- Not escaped, causes an error
....
0
 
_agx_Commented:
From what I can see, the code builds a list of column names. You need to find the PO# column in that list

  var MM_columnsStr = "Status|',none,''|CarNum|none,none,NULL|DateIssued|',none,NULL|[Issued by]|',none,''|Title|',none,''|Company|',none,''|Contact|',none,''|Phone|',none,''|Fax|',none,''|Email|',none,''|Manufacturer|',none,''|[Part Number]|',none,''|[ESC ID#]|',none,''|PO#|',none,''|[RMA #]|',none,''|[Quantities Affected]|',none,''|[Description of Nonconformance]|',none,''|Containment|',none,''|
..... more values

add square brackets around it Like this:

  var MM_columnsStr = "Status|',none,''|CarNum|none,none,NULL|DateIssued|',none,NULL|[Issued by]|',none,''|Title|',none,''|Company|',none,''|Contact|',none,''|Phone|',none,''|Fax|',none,''|Email|',none,''|Manufacturer|',none,''|[Part Number]|',none,''|[ESC ID#]|',none,''|[PO#]|',none,''|[RMA #]|',none,''|[Quantities Affected]|',none,''|[Description of Nonconformance]|',none,''|Containment|',none,''|
....

That said, using dynamic SQL is NOT recommended.  As you've discovered, it's error prone and difficult to troubleshoot, even when you know what you're doing. Also, it can be unsafe and expose the db to sql injection
0
 
ES-ComponentsAuthor Commented:
Last one....   I made changes to Database column names in Access. Got rid of above problems. BUT, I am not sure how to make a field
Non-Up-dateable. The CARNum is automatically generated in Access when we create a record. This should be non-updateable.
When I go to update the other fields on the web page, see below error in addition to 2 hidden fields on the page. Is there a way in the code to make the CARNumb field non-updateable? I am passing the CARNumb from another page as a UL paramter to the Update page.

Thanks for all your help!!

 update CAR set Status = 'Closed',CarNum = 43,DateIssued = '11/19/2001',[Issued by] = 'David Keeler',Title = 'Operations Manager',Company = 'ES Components - Internal',Contact = 'Lynda Sandoval',Phone = '480-961-6280',Fax = '111',Email = '',Manufacturer = 'CDI IN5314',[Part Number] = 'CDI',[ESC ID] = '.6915',PO = 'A114943',RMA = '',[Quantities Affected] = '185 pcs',[Description of Nonconformance] = 'Fails bondability. The die will not attach. (S1009487.001) material was purchased on P1004910.001 from mintech on 8/20/01',Containment = 'No containment. No additional material in stock from same lot number. All of the material was sent to Signal Tech. Sue Aubrey needs to issue an RGA to get the discrepant parts back to ESC for evaluation',[Root Cause] = 'n/a customer has retracted this CAR',CandP = '',[Corrective Action Date] = NULL,[Due Date] = '12/19/2001',[Reference Number] = '01-123',Address1 = '',Address2 = '',City = '',State = '',Zip = '',CityStateZip = '',Quan1 = '',Lot1 = '',Quan2 = '',Lot2 = '',Quan3 = '',Lot3 = '',Branch = '',ClosedDate = '11/28/2001',CompletedBy = '',ToFrom = 'To',ReviewOfEffectiveness = '12/31/2010' where CarNum = 43

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Cannot update 'CarNum'; field not updateable.
/AllCARSUpdate.asp, line 82

HIDDEN FIELDS ON PAGE

<input type="hidden" name="MM_update" value="form1">
  <input type="hidden" name="MM_recordId" value="<%= Update.Fields.Item("CarNum").Value %>">
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.