Link to home
Start Free TrialLog in
Avatar of JMO9966
JMO9966

asked on

SQL Update statement syntax

Hello,
 
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
        cmd.ExecuteNonQuery()

Thanks!

Jon
SOLUTION
Avatar of iHadi
iHadi
Flag of Syrian Arab Republic image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JMO9966
JMO9966

ASKER

Yes,

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


Thanks,
Jon
Avatar of JMO9966

ASKER

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.

Thanks
Avatar of JMO9966

ASKER

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

ASKER

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.
 
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial