Click here to Skip to main content
15,893,588 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Run time error '- 2147217900(80040e14)': Syntax error (missing operator) in query expression I am getting this error

What I have tried:

This is what I have Tried and I am getting error in INSERT Query

VB
Dim fd As FileDialog
Dim SelectFile As Variant
Dim cnn As ADODB.Connection
       Set cnn = New ADODB.Connection
       Dim rst As ADODB.Recordset
       Set rst = New ADODB.Recordset
   Set fd = Application.FileDialog(msoFileDialogOpen)
   With fd
       .AllowMultiSelect = False
       .Title = "Please select file to attach"
       If .Show = True Then
           SelectFile = .SelectedItems(1)
       Else
           Exit Sub
       End If
   End With
   Set fd = Nothing

       cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\SaveFile.accdb;Persist Security Info=False;"
       cnn.Open
       Set rst = cnn.Execute("INSERT INTO Table1 (AddFiles) VALUES (" & SelectFile & ")")
      ' rst.AddNew
       rst.Fields(1).Value = "AddFiles"

       rst.Update
       rst.Close
       cnn.Close
Posted
Updated 21-Apr-18 0:17am
v3
Comments

This is an SQL injection: your filename is promoted to SQL code and the SQL server try to understand the filename as some SQL code.
First step is to change to:
VB
Set rst = cnn.Execute("INSERT INTO Table1 (AddFiles) VALUES ('" & SelectFile & "')")

it will handle filenames without apostrophe. if a filename can have an apostrophe, the problem is the same, real solution you need to read the following articles.
----
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
 
Share this answer
 
Comments
Member 13788626 20-Apr-18 10:18am    
This didn't solved my problem It shows another error cannot contain multi-valued fields
Patrice T 20-Apr-18 10:20am    
What is the value of SelectFile ?
What is the type of field ?
Member 13788626 20-Apr-18 10:25am    
The value of selected file is the path of file that I have open using file dialog box.
The type of field in Access database is Attachment. and the file type in Excel is an Object
Patrice T 20-Apr-18 10:31am    
What is the value of variable 'SelectFile' when you get the error ? Its exact contain, not its meaning. ex: 'c;\myfile.txt'
Member 13788626 20-Apr-18 10:37am    
When I get the error the contain of SelectFile is something like D:\user\Documents\db.accdb
Try using a CreateParameter Method (ADO) | Microsoft Docs[^] to create a new parameter for your insert statement. Also use Execute Method (ADO Command) | Microsoft Docs[^] to run the INSERT statement.

For a more detailed example, have a look at Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects - Access World Forums[^]
 
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