Click here to Skip to main content
15,878,048 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:
VB
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
Updated 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

the space in Application Logging is your problem - enclose it in quotes, or square brackets (not sure of jet's requirements)
 
Share this answer
 
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)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900