Click here to Skip to main content
13,048,805 members (81,877 online)
Rate this:
Please Sign up or sign in to vote.
See more:
Hi All,
I am using (2010) and I am trying to update a Accedss 2010 DB.
I am getting the following erroe:
Syntax error in UPDATE statement at the cmd.ExecuteNonQuery() statemant.
My code is as follows:
 Dim Username As String
    Dim ComputerName As String
    Dim AccessTime As Date
    Dim AppName As String
    Dim DomainName As String
    Dim ProjectRef As String
    Dim Update As String = _
   "Update Application Logging set (AccessTime,UserName, ComputerName, DomainName, AppName, ProjectRef) Values(?,?,?,?,?,?)"
    Dim cnnString As String = _
    "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = \\appserver\database.accdb; "

    Public Sub DBUpdate()
        Username = System.Security.Principal.WindowsIdentity.GetCurrent.Name
        DomainName = Environment.UserDomainName
        ComputerName = Environment.MachineName
        AccessTime = Date.Now
        AppName = My.Application.Info.AssemblyName

        Using cnn As New OleDbConnection(cnnString)
            Using cmd As New OleDbCommand(Update, cnn)
                cmd.Parameters.AddWithValue("AccessDate", AccessTime)
                cmd.Parameters.AddWithValue("Username", Username)
                cmd.Parameters.AddWithValue("ComputerName", ComputerName)
                cmd.Parameters.AddWithValue("DomainName", DomainName)
                cmd.Parameters.AddWithValue("AppName", AppName)
                cmd.Parameters.AddWithValue("ProjectRef", ProjectRef)
            End Using
        End Using

I'm new to this so I'm sure its a simple mistake on my part.

Thanks for any help.
Posted 13-Aug-12 15:16pm
Updated 13-Aug-12 15:20pm
Abdul Quader Mamun 13-Aug-12 21:21pm
edited for code block!
Abdul Quader Mamun 13-Aug-12 21:22pm
what was the error?
Martok867 14-Aug-12 0:10am
OK using the INSERT INTO statement I am now getting:
Data type mismatch in criteria expression
All are strings except the date var, the first entry in the DB is the ID field (Auto). Is this the issue? Do I need to tell the app to start in the next field (AccessDate)?
Martok867 14-Aug-12 2:48am
Regarding my last post, the date was being passed to the DB with # before and after (?), changed the DB field to text and it works.

Thanks for your help

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

the space in Application Logging is your problem - enclose it in quotes, or square brackets (not sure of jet's requirements)
Martok867 13-Aug-12 21:41pm
I have tried your suggestion with no change (Removed the space and enclosed in sqr brackets)
barneyman 13-Aug-12 21:46pm
OOPs - sorry sped read it the first time ...

that whole update statement is broken

I think you mean it to be an insert?

INSERT [Application Logging] (AccessTime,UserName, ComputerName, DomainName, AppName, ProjectRef) Values(?,?,?,?,?,?)

The update would be ..

UPDATE [Application Logging] AccessTime=?, UserName=?, ComputerName=?, DomainName=?, AppName=?, ProjectRef=? WHERE ...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170713.1 | Last Updated 13 Aug 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100