Click here to Skip to main content
15,669,580 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

I trying to update column in excel file.
At the moment I have code which update only one at the time.
I would like to update many column at one time.

When I try to run this I have Syntax error in UPDATE Statement

Could anyone help me ?

What I have tried:

'Create the connection string to connect to the Microsoft Excel Workbook
Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=FORM\FORM.xlsx;Extended Properties = ""Excel 12.0 Xml;HDR=YES"""
Dim updateStatement As String = "UPDATE [DATA$] SET LINE = '" + NAMEASSOCIATEINPUT.Text + "',TIME = '" + NAMEASSOCIATEINPUT.Text + "'"

'Create a connection object to connect to the Excel Workbook
Dim connection As New OleDbConnection(connectionString)
'Create a command object that will execute the update statement
Dim command As New OleDbCommand(updateStatement, connection)
'Open the connection, execute the statement and close the connection
'Dispose of the connection and command objects
'Call the RefreshData routine so that you can see that data was indeed updated.
Updated 4-Feb-18 0:28am
Richard MacCutchan 3-Feb-18 7:27am    
Look at the actual content of the UPDATE statement when you try to send it to the database.
CHill60 3-Feb-18 7:33am    
Do you have a sheet called "DATA". What is in NAMEASSOCIATEINPUT.Text?
Do you have columns entitled LINE and TIME.
Try surrounding reserved words with square brackets e.g [TIME]
Member 13659167 3-Feb-18 7:34am    
I expect that there is an error. So if I want update more then one how I should write update statement ?
Member 13659167 3-Feb-18 7:37am    
Yes i have sheet called DATA.
In this text box is text.
Yes I have columns Line and TIME.
I try to add square brackets but still not working.

1 solution

Your code is SQL injection vulnerable. Please read my past answer[^] to get more details.

Your connection string is wrong:
Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=FORM\FORM.xlsx;Extended Properties = ""Excel 12.0 Xml;HDR=YES"""

because the path to the file is incomplete! You have to provide full file name including full path.

Please, read notes provided here: Microsoft ACE OLEDB 12.0 Connection Strings -[^]
Share this answer

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