Syntax Error In Update Statement After Updating To Access 2007 : Once I Click I Got This Message

Question

0.00/5 (No votes)

See more:

Updating AdjustmentHead & AdjustmentDetails

VB

'-------------------------------------------
    
    'update AdjustmentHead
    strSql = "UPDATE AdjustmentHead SET AdjustmentHead.[Adjustment+V] = " & Forms![AdjustmentHead]![Adjustment+V] & ", AdjustmentHead.[Adjustment-V] = " & [Forms]![AdjustmentHead]![Adjustment-V2] & ", AdjustmentHead.AdjustmentStatus = -1, AdjustmentHead.AdjTransactionID = " & intTransactionID & ", AdjustmentHead.AdjOrderID = '" & strOrderID & "'"
    strSql = strSql & " WHERE (((AdjustmentHead.AdjustmentHeadID)=[Forms]![AdjustmentHead]![AdjustmentHeadID]));"
    DoCmd.RunSQL strSql
    
    'update Adjustment
    strSql = "UPDATE AdjustmentDetailsQ INNER JOIN Items ON AdjustmentDetailsQ.ItemID = Items.ItemID SET Items.[In] = [Items]![In]+[AdjustmentDetailsQ]![OrderQty], Items.Out = [Items]![Out]+[AdjustmentDetailsQ]![IssueForLocal]+[AdjustmentDetailsQ]![IssueForForign], Items.CIN = [Items]![CIN]+[AdjustmentDetailsQ]![OrderCQty], Items.COut = [Items]![COut]+[AdjustmentDetailsQ]![IssueForLocal]"
    strSql = strSql & " WHERE (((AdjustmentDetailsQ.AdjustmentHeadID)=[Forms]![AdjustmentHead]![AdjustmentHeadID]) AND ((AdjustmentDetailsQ.ActQOnH) Is Not Null));"
    DoCmd.RunSQL strSql
    
    strSql = "UPDATE Orders SET Orders.Adj = -1"
    strSql = strSql & " WHERE (((Orders.OrderID) Like 'adj*'));"
    DoCmd.RunSQL strSql
    
    
    strSql = "UPDATE Transactions SET Transactions.AdjTrans = -1"
    strSql = strSql & " WHERE (((Transactions.TransactionNo) Like '*Adj*'));"
    DoCmd.RunSQL strSql
    
    
'Closing Form

    DoCmd.Close
    

Exit_CmdConfirm_Click:
    Exit Sub

Err_CmdConfirm_Click:
    MsgBox Err.Description
    Resume Exit_CmdConfirm_Click
    
End Sub

Posted 10-Nov-15 3:20am

MOUTAZ877

Updated 10-Nov-15 3:36am

phil.o

v2

Add a Solution

Comments

Richard Deeming 10-Nov-15 10:21am

Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

MOUTAZ877 10-Nov-15 10:51am

Sorry I didnot understand i am not an expert with sql query can you explain to me in more details or write for me the correct code please please!

Richard Deeming 10-Nov-15 10:57am

Any user of your system can type certain characters into your input controls which will change the way your query functions. They might accidentally type in a single quote and get a syntax error; or they might deliberately type in queries which will alter your database in unexpected ways.

The following links have a lot of good information about this security vulnerability:
Bobby Tables: A guide to preventing SQL Injection[^]
Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]
SQL injection attack mechanics | Pluralsight [^]

MOUTAZ877 10-Nov-15 11:05am

Ok then how shall i right it ? I have no experience! Or give me an example

Richard Deeming 10-Nov-15 11:07am

Wow, you read all of the information on those sites I linked to really fast. How did you manage to watch an hour-long video in under 8 minutes?
</SARCASM>

MOUTAZ877 10-Nov-15 11:16am

Thank u but i still cannot get which sql queries are vulnerable

Richard Deeming 10-Nov-15 11:23am

It's the first one - the one that uses & to concatenate the values from the form controls into the query.

The RunSQL method is quite limited. It can resolve references to Access forms, so the first two concatenations can be removed:

strSql = "UPDATE AdjustmentHead SET AdjustmentHead.[Adjustment+V] = Forms![AdjustmentHead]![Adjustment+V], AdjustmentHead.[Adjustment-V] = [Forms]![AdjustmentHead]![Adjustment-V2], AdjustmentHead.AdjustmentStatus = -1, AdjustmentHead.AdjTransactionID = " ...

But then you run into problems. The method has no way to accept parameters that aren't fields on a form.

The simplistic approach would be to put both intTransactionID and strOrderID in hidden fields on your form. If you can't do that, then you'd have to re-write your code to use ADODB instead.

MOUTAZ877 10-Nov-15 12:15pm

It worked then stopped at inttransaction id , please help in this code i did not know how to do it

ZurdoDev 10-Nov-15 10:38am

Which of the 4 update statements has the error? Please only post relevant code.

MOUTAZ877 10-Nov-15 10:52am

Am not sure but there are 4 , the first one is ok but the rest 3 update sql queries i dont know , may you help me in writing the code in another way that would work ?

ZurdoDev 10-Nov-15 11:04am

You have to know which one is causing the error because the error will tell you.

MOUTAZ877 10-Nov-15 11:08am

The error just says syntax error in update statement

ZurdoDev 10-Nov-15 11:10am

And should point to a line of code. If you're running through Visual Studio it will stop at a line of code.

MOUTAZ877 10-Nov-15 11:15am

No it is not stopping anyware just go this pop up massage and it was working fine on old access versions so what happened now ?

ZurdoDev 10-Nov-15 11:24am

Then debug the code. Don't expect us to go through all your code and guess which one is wrong. Put a breakpoint at the top and then step through and not only will you see which one causes the error but you'll be able to see why. This is something you can fix very quickly on your own if you debug it.

ZurdoDev 10-Nov-15 11:25am

In fact, this looks like the same exact question from a couple of months ago.

http://www.codeproject.com/Questions/998769/I-got-error-syntax-error-in-update-statement-what?arn=0

MOUTAZ877 10-Nov-15 10:56am

Please anybody can write for me the code in correct way as i need it urgently

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)