Why does VB.Net throw an error when running an Access update query while it runs properly in Access-CodePudding

I am running an Access update query in VB.Net.

dbCustSpec_ADO.Execute("table_upt")

Ir runs fine except for the following "Update to" statement

[table].[field1] & [table].[field2]

The following is working properly

[table].[field1]

So does the following

[table].[field2]

It is only when I concatenate both fields when VB.Net throws an error:

System.Runtime.InteropServices.COMException: 'Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.'

Btw: The concatenation works properly when calling the query in Access.

My question is: How can I concatenate both fields in order to make it run while calling it from VB.net

CodePudding user response：

It not clear, are you using the .net oleDB provider here?

Or are you creating a instance of the Access database engine?

You better off to use oleDB such as this:

Imports System.Data.OleDb

And then your code to update can look like this:

    Using conn As New OleDbConnection(My.Settings.TESTAce)
        Dim strSQL As String = "UPDATE tblHotels SET FullName = FirstName   ', '   LastName"
        Using cmdSQL As New OleDbCommand(strSQL, conn)
            conn.Open()
            cmdSQL.ExecuteNonQuery()
        End Using
    End Using

And if you wanted to ran a "existing" update query in Access? They are considered store procedures. Say we have upate query saved in Access called

qryFirstLast

Then the above code to run that query would be:

    Using conn As New OleDbConnection(My.Settings.TESTAce)
        Dim strSQL As String = "qryFirstLast"
        Using cmdSQL As New OleDbCommand(strSQL, conn)
            conn.Open()
            cmdSQL.CommandType = CommandType.StoredProcedure
            cmdSQL.ExecuteNonQuery()
        End Using
    End Using

Note how we set the command type = StoredProcedure.