How do I correct a syntax error in command builder?

Question

I am trying to get this routine to update my autolog table in access. Every time I run it, I get a syntax error in the INSERT command. Now when I look at the INSERT command from the command builder, I can see that there are values missing that are replaced by question marks, but I do not understand why. Here is my code:

Function Autolog(ByRef LogEntry As String, ByVal Userid As Integer)
    Dim Table_ As String = "Table1"
    Dim sql As String = "Select * from autolog"
    Dim MDBConnString_ As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Freightmasterbe\Freightmaster.accdb;"
    Dim cnn As OleDbConnection = New OleDbConnection(MDBConnString_)
    Dim DS As New DataSet
    Dim Da As OleDbDataAdapter
    Dim StrHOstName As String


    Da = New OleDb.OleDbDataAdapter(sql, cnn)

    Da.Fill(DS, "Rst")
    ' cnn.Close()
    Dim cb As New OleDb.OleDbCommandBuilder(Da)
    Dim DSNewRow As DataRow
    DSNewRow = DS.Tables("Rst").NewRow()

    DSNewRow.Item("UserID") = Userid
    DSNewRow.Item("Action") = LogEntry

    DSNewRow.Item("RemoteComputerName") = My.Computer.Name
    StrHOstName = System.Net.Dns.GetHostName()
    DSNewRow.Item("RemoteIP") = System.Net.Dns.GetHostEntry(StrHOstName).AddressList(0).ToString()

    DS.Tables("RST").Rows.Add(DSNewRow)
    '   Da.ContinueUpdateOnError = True

    Da.Update(DS, "RST")
    Beep()
End Function

And here is what I get when I query the command builder insert command

Immediate window

CanRaiseEventsInternal: True
CommandText: "INSERT INTO autolog (UserID, Action, TimeStamp, RemoteComputerName, RemoteIP) VALUES (?, ?, ?, ?, ?)"
CommandTimeout: 30
CommandType: Text {1}
Connection: {System.Data.OleDb.OleDbConnection}
Connection (DbCommand): {System.Data.OleDb.OleDbConnection}
Container: Nothing
DbConnection: {System.Data.OleDb.OleDbConnection}
DbParameterCollection: {System.Data.OleDb.OleDbParameterCollection}
DbTransaction: Nothing
DesignMode: False
DesignTimeVisible: True
Events: {System.ComponentModel.EventHandlerList}
ObjectID: 4
Parameters: {System.Data.OleDb.OleDbParameterCollection}
Parameters (DbCommand): {System.Data.OleDb.OleDbParameterCollection}
Site: Nothing
Transaction: Nothing
Transaction (DbCommand): Nothing
UpdatedRowSource: None {0}

Answer 1

According to this post, Action is an Access database engine reserved word. Therefore, you need to use QuotePrefix and QuoteSuffix as shown in the code below. Additionally, any objects with a Dispose method should either use a using statement or call Dispose.

If you wish to use OleDbCommandBuilder to insert a row into your Access database:

Create table (name: Autolog)

CREATE TABLE Autolog(ID AUTOINCREMENT not null primary key,
                    [UserID] varchar(25),
                    [Action] varchar(125),
                    [RemoteComputerName] varchar(50),
                    [RemoteIP] varchar(50));

Then, try the following (which has been tested):

Private _connectionStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Freightmasterbe\Freightmaster.accdb;"

Public Function TblAutologInsert(ByVal LogEntry As String, ByVal UserId As Integer) As Integer
    Dim dt As DataTable = New DataTable()

    Dim remoteComputerName As String = My.Computer.Name
    Dim strHostName As String = System.Net.Dns.GetHostName()
    Dim remoteIP = System.Net.Dns.GetHostEntry(strHostName).AddressList(0).ToString()

    Using cnn As OleDbConnection = New OleDbConnection(_connectionStr)
        'open
        cnn.Open()

        Using da As OleDbDataAdapter = New OleDbDataAdapter("SELECT * from Autolog order by Id", cnn)
            'get data from database
            da.Fill(dt)

            Using cb As OleDbCommandBuilder = New OleDbCommandBuilder(da)
                'needed if database column name contains either spaces or is a reserved word
                cb.QuotePrefix = "["
                cb.QuoteSuffix = "]"

                'new row
                Dim row As DataRow = dt.NewRow()

                'set values
                row.Item("UserID") = UserId
                row.Item("Action") = LogEntry
                row.Item("RemoteComputerName") = remoteComputerName
                row.Item("RemoteIP") = remoteIP

                'add row to DataTable
                dt.Rows.Add(row)

                'get insert command
                da.InsertCommand = cb.GetInsertCommand(True)
                'Debug.WriteLine("da.InsertCommand.CommandText: " & da.InsertCommand.CommandText)

                'update database and return number of rows affected
                Return da.Update(dt)
            End Using
        End Using
    End Using
End Function

Usage:

Dim rowsAffected As Integer = 0

'insert test data
rowsAffected += HelperAccess.TblAutologInsert("test 1", 1)
rowsAffected += HelperAccess.TblAutologInsert("test 2", 2)
rowsAffected += HelperAccess.TblAutologInsert("test 3", 3)

Debug.WriteLine("rowsAffected: " & rowsAffected.ToString())

---

Here's some code for updating a record by "Id".

Public Function TblAutologUpdate(LogEntry As String, UserId As Integer, id As Integer) As Integer
    Dim dt As DataTable = New DataTable()

    Dim remoteComputerName As String = My.Computer.Name
    Dim strHostName As String = System.Net.Dns.GetHostName()
    Dim remoteIP = System.Net.Dns.GetHostEntry(strHostName).AddressList(0).ToString()

    Using cnn As OleDbConnection = New OleDbConnection(_connectionStr)
        'open
        cnn.Open()

        Using cmd As OleDbCommand = New OleDbCommand("SELECT * from Autolog where Id = ?", cnn)
            'OLEDB doesn't use named parameters in SQL it uses positional parameters.
            'However, specifying names in the parameter 'Add' statement can be useful for debugging
            'Since OLEDB uses anonymous names, the order which the parameters are added is important
            'if a column is referenced more than once in the SQL, then it must be added as a parameter more than once
            'parameters must be added in the order that they are specified in the SQL
            'if a value is null, the value must be assigned as: DBNull.Value

            'add parameters
            With cmd.Parameters
                .Add("!id", OleDbType.Integer).Value = id
            End With

            Using da As OleDbDataAdapter = New OleDbDataAdapter(cmd)
                'get data from database
                da.Fill(dt)

                Using cb As OleDbCommandBuilder = New OleDbCommandBuilder(da)
                    'needed if database column name contains either spaces or is a reserved word
                    cb.QuotePrefix = "["
                    cb.QuoteSuffix = "]"

                    If dt.Rows.Count >= 0 Then
                        'set value
                        Dim row As DataRow = dt.Rows(0)

                        'set values
                        row.Item("UserID") = UserId
                        row.Item("Action") = LogEntry
                        row.Item("RemoteComputerName") = remoteComputerName
                        row.Item("RemoteIP") = remoteIP

                        'get update command
                        da.UpdateCommand = cb.GetUpdateCommand()
                       
                        'update database and return number of rows affected
                        Return da.Update(dt)
                    End If
                End Using
            End Using
        End Using
    End Using
End Function

Usage:

Dim rowsAffected As Integer = 0

'update
rowsAffected += HelperAccess.TblAutologUpdateById("test 2b", 2, 2)

Debug.WriteLine("rowsAffected: " & rowsAffected.ToString())

Resources

• OleDbConnection Class
• OleDbDataAdapter Class
• OleDbCommandBuilder Class
• OleDbCommand Class
• DbCommandBuilder.QuotePrefix Property
• DbCommandBuilder.QuoteSuffix Property
• Learn about Access reserved words and symbols
• Syntax error in INSERT INTO statement generated by OleDbCommandBuilder
• using statement (C# Reference)

Answer 2

Thank you so much for your comments. Adding the prefix and suffix had a major effect, and using the originally posted code in the first answer works perfectly. The other responses have too explained an awful lot. (Oh how I yearn for the good old days of simply using sql statements to insert data!