Cannot execute SQL query

I have the following code to execute a SQL query in vb.net :

  opdragaliaens.CommandText = "UPDATE UBH " &
                              "SET ton = wh.total_net " &
                              "FROM UniekeBlokkeHistories UBH " &
                              "INNER JOIN ( " &
                              " SELECT grower, block, section, oesjaar, SUM(net) AS total_net " &
                              "   FROM wbridge_history " &
                              "   GROUP BY grower, block, section, oesjaar " &
                              ") AS wh ON " &
                              "  wh.grower = UBH.Lid_No " &
                              "  AND wh.[block] = UBH.Plaas_no " &
                              "  AND wh.section = UBH.Blok_no " &
                              "  AND wh.oesjaar = UBH.oesjaar " &
                              "Update()  t1 " &
                              "set " &
                              "t1.alias = t2.alias, " &
                              "t1.Plaasnaam = t2.blkname, " &
                              "t1.Kultivar = t2.variety, " &
                              "t1.Klas = t2.yearclass " &
                              "   FROM " &
                              "   UniekeBlokkeHistories t1 " &
                              "Inner Join wbridge_history t2 " &
                              "On t1.Lid_No = t2.grower " &
                              "And t1.Plaas_no = t2.[block] " &
                              "And t1.Blok_no = t2.section " &
                              "And t1.Oesjaar = t2.oesjaar"

Try
     opdragaliaens.ExecuteNonQuery()
Catch ex As Exception
     MsgBox(ex.Message)
End Try

​x
 
  opdragaliaens.CommandText = "UPDATE UBH " &                              "SET ton = wh.total_net " &                              "FROM UniekeBlokkeHistories UBH " &                              "INNER JOIN ( " &                              " SELECT grower, block, section, oesjaar, SUM(net) AS total_net " &                              "   FROM wbridge_history " &                              "   GROUP BY grower, block, section, oesjaar " &                              ") AS wh ON " &                              "  wh.grower = UBH.Lid_No " &                              "  AND wh.[block] = UBH.Plaas_no " &                              "  AND wh.section = UBH.Blok_no " &                              "  AND wh.oesjaar = UBH.oesjaar " &                              "Update()  t1 " &                              "set " &                              "t1.alias = t2.alias, " &                              "t1.Plaasnaam = t2.blkname, " &                              "t1.Kultivar = t2.variety, " &                              "t1.Klas = t2.yearclass " &                              "   FROM " &                              "   UniekeBlokkeHistories t1 " &                              "Inner Join wbridge_history t2 " &                              "On t1.Lid_No = t2.grower " &                              "And t1.Plaas_no = t2.[block] " &                              "And t1.Blok_no = t2.section " &                              "And t1.Oesjaar = t2.oesjaar"​Try     opdragaliaens.ExecuteNonQuery()Catch ex As Exception     MsgBox(ex.Message)End Try​

When I run the query I get a message :

Incorrect syntax near ‘(‘

I cannot see on which line the error occurs.

The query works in SQL Server Management Studio.

What am I missing in VB.net?

Regards

Answer

You have open and close parentheses "Update() t1 " & in between your update scripts. I suggest to have a separate call on your 2nd udpate.

opdragaliaens.CommandText = "UPDATE UBH " &
    "SET ton = wh.total_net " &
    "FROM UniekeBlokkeHistories UBH " &
    "INNER JOIN ( " &
    " SELECT grower, block, section, oesjaar, SUM(net) AS total_net " &
    "   FROM wbridge_history " &
    "   GROUP BY grower, block, section, oesjaar " &
    ") AS wh ON " &
    "  wh.grower = UBH.Lid_No " &
    "  And wh.[block] = UBH.Plaas_no " &
    "  and wh.section = UBH.Blok_no " &
    "  and wh.oesjaar = UBH.oesjaar; " &
    "Update t1 " &
    "set " &
    "t1.alias = t2.alias, " &
    "t1.Plaasnaam = t2.blkname, " &
    "t1.Kultivar = t2.variety, " &
    "t1.Klas = t2.yearclass " &
    "   FROM " &
    "   UniekeBlokkeHistories t1 " &
    "Inner Join wbridge_history t2 " &
    "On t1.Lid_No = t2.grower " &
    "And t1.Plaas_no = t2.[block] " &
    "And t1.Blok_no = t2.section " &
    "And t1.Oesjaar = t2.oesjaar"

Try
opdragaliaens.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
End Try

 
opdragaliaens.CommandText = "UPDATE UBH " &    "SET ton = wh.total_net " &    "FROM UniekeBlokkeHistories UBH " &    "INNER JOIN ( " &    " SELECT grower, block, section, oesjaar, SUM(net) AS total_net " &    "   FROM wbridge_history " &    "   GROUP BY grower, block, section, oesjaar " &    ") AS wh ON " &    "  wh.grower = UBH.Lid_No " &    "  And wh.[block] = UBH.Plaas_no " &    "  and wh.section = UBH.Blok_no " &    "  and wh.oesjaar = UBH.oesjaar; " &    "Update t1 " &    "set " &    "t1.alias = t2.alias, " &    "t1.Plaasnaam = t2.blkname, " &    "t1.Kultivar = t2.variety, " &    "t1.Klas = t2.yearclass " &    "   FROM " &    "   UniekeBlokkeHistories t1 " &    "Inner Join wbridge_history t2 " &    "On t1.Lid_No = t2.grower " &    "And t1.Plaas_no = t2.[block] " &    "And t1.Blok_no = t2.section " &    "And t1.Oesjaar = t2.oesjaar"​Tryopdragaliaens.ExecuteNonQuery()Catch ex As ExceptionMsgBox(ex.Message)End Try​

Advertisement

Answer