SQL Update statement syntax

Posted on 2006-06-01
Last Modified: 2008-03-06
I have this query executing correctly via SQL Query Analyzer but can't figure out the Syntax for VB>net to run this statement through the ODBC Data connection.

Here's the Query I'm attempting to run:

Private sSQL As String

sSQL = "Update Material_Location Set Material_Location.Unit_Cost=material_trans.unit_cost From Material_Location INNER JOIN Material_Req ON Material_Location.Material=Material_Req.Material INNER JOIN Material_Trans ON Material_Trans.Material_Req=Material_Req.Material_Req AND Material_Trans.Location_ID=Material_Location.Location_ID AND Material_Trans.Lot=Material_Location.Lot WHERE Material_Location.Unit_Cost=0"

Dim cmd As Odbc.OdbcCommand = New Odbc.OdbcCommand(sSQL)
        cmd.Connection = dcMatLocation


Question by:JMO9966
    LVL 13

    Assisted Solution

    Is it giving you an exeption? what is it saying? try to give alias names to each block you are joining with others.

    Author Comment


    unhandled exception has occurred.

    Error [42000] Microsoft ODBC error
    Syntax error (missing operator) in query expression 'material_trans.unit_cost From Material_location INNER JOIN.............................................................................................


    Author Comment

    I should mention I tried entering this as one continuous update command as one line with no _ needed for wrapping to next line and it still didn't work.

    I've seen a few examples with & and ' and "" used but can't tell how they work.


    Author Comment

    One other note, this is an Access database, not a SQL database I'm trying to update.

    Author Comment

    Okay, this same statement ran fine against a SQL database, I'm going to try using ADODB connection instead unless someone know how to get the syntax for a MSAccess database.
    LVL 6

    Assisted Solution


    Access has a funny syntax when it comes to joining tables with an SQL statement as everything has to be enclosed in ( ) .

    The way I always do it it to go into the Access database itself, build the query and then check the SQL that has been created. You can then copy and paste this into your vb.Net code and with a few tidy up's you should be able to get it to work.

    HTH :)
    LVL 34

    Accepted Solution

    I agree that the problem is with Access.  Trying to run your query directly in Access produced an error for me.  It doesn't seem to like either the length of the query string or the order in which its elements appear.  I redesigned it as follows

    UPDATE (Material_Location INNER JOIN Material_Req ON Material_Location.Material = Material_Req.Material) INNER JOIN Material_Trans ON (Material_Req.Material_Req = Material_Trans.Material_Req) AND (Material_Location.Location_ID = Material_Trans.Location_ID) AND (Material_Location.Lot = Material_Trans.Lot) SET Material_Location.Unit_Cost = Material_Trans.unit_cost
    WHERE Material_Location.Unit_Cost=0;

    and it ran OK in Access itself.  I've also tested it OK over both OleDb and ODBC connections from VB.NET.


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now