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_Cos t=material _trans.uni t_cost From Material_Location INNER JOIN Material_Req ON Material_Location.Material =Material_ Req.Materi al INNER JOIN Material_Trans ON Material_Trans.Material_Re q=Material _Req.Mater ial_Req AND Material_Trans.Location_ID =Material_ Location.L ocation_ID AND Material_Trans.Lot=Materia l_Location .Lot WHERE Material_Location.Unit_Cos t=0"
Dim cmd As Odbc.OdbcCommand = New Odbc.OdbcCommand(sSQL)
cmd.Connection = dcMatLocation
cmd.ExecuteNonQuery()
Thanks!
Jon
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_Cos
Dim cmd As Odbc.OdbcCommand = New Odbc.OdbcCommand(sSQL)
cmd.Connection = dcMatLocation
cmd.ExecuteNonQuery()
Thanks!
Jon
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
I've seen a few examples with & and ' and "" used but can't tell how they work.
Thanks
ASKER
One other note, this is an Access database, not a SQL database I'm trying to update.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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