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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Be best if you could output your Update query string and post it here.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.