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}