Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Access VB.NET
Hi All,
I am using VB.net (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)
            cnn.Open()
            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)
 
                cmd.ExecuteNonQuery()
 
            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
Edited 13-Aug-12 15:20pm
v2
Comments
Abdul Quader Mamun at 13-Aug-12 21:21pm
   
edited for code block!
Abdul Quader Mamun at 13-Aug-12 21:22pm
   
what was the error?
Martok867 at 14-Aug-12 0:10am
   
OK using the INSERT INTO statement I am now getting:
Data type mismatch in criteria expression
PROGRESS!!
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 at 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
good
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)
  Permalink  
Comments
Martok867 at 13-Aug-12 21:41pm
   
I have tried your suggestion with no change (Removed the space and enclosed in sqr brackets)
??
barneyman at 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
0 Sergey Alexandrovich Kryukov 6,775
1 OriginalGriff 6,696
2 CPallini 5,345
3 George Jonsson 3,599
4 Gihan Liyanage 2,751


Advertise | Privacy | Mobile
Web03 | 2.8.140922.1 | Last Updated 13 Aug 2012
Copyright © CodeProject, 1999-2014
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