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
JMO9966Asked:
Who is Participating?
 
SanclerConnect With a Mentor Commented:
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.

Roger
0
 
iHadiConnect With a Mentor Commented:
Is it giving you an exeption? what is it saying? try to give alias names to each block you are joining with others.
0
 
JMO9966Author Commented:
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
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
JMO9966Author Commented:
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
0
 
JMO9966Author Commented:
One other note, this is an Access database, not a SQL database I'm trying to update.
0
 
JMO9966Author Commented:
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.
 
0
 
cubixSoftwareConnect With a Mentor Commented:
Hi

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 :)
0
All Courses

From novice to tech pro — start learning today.