Click here to Skip to main content
12,295,662 members (54,057 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: VB 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 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
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 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 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.160525.2 | Last Updated 13 Aug 2012
Copyright © CodeProject, 1999-2016
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