Error in Insert SQL statement for VB.net

Question

User365103270 posted

Hi all, i encounter an "Insert Into" syntax error when i run this code... anyone can help to tell me how the problem can be solved?

thanks!

Dim sConnectionString As String
sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=H:\Users\Desktop\EmployeeAway2.xlsx;Extended Properties=""Excel 12.0;HDR=YES;"""
Dim objConn As New System.Data.OleDb.OleDbConnection(sConnectionString)
objConn.Open()
Dim objCmd As New System.Data.OleDb.OleDbCommand()
objCmd.Connection = objConn
objCmd.CommandText = "Insert into [Sheet1$] (EmployeeID, DateFiled, Name, Department, Reason, From, To, NumberofDays, LeaveApplied, ApprovedBy)" & _
" Values ('John', 'John', 'John', 'John', 'John', 'John', 'John', 'John', 'John', 'Thomas')"
objCmd.ExecuteNonQuery()
objConn.Close()

Answer 1

User-1941247845 posted

Jing,

Could you please share the exact error to have a look at it. 

Also when you insert ensure that the data type matches the values that you pass. Try passing an ID value for the first parameter also ensure that you have spaced the Insert statement appropriately.

Ex : 

sql = "Insert into [Sheet1$] (id,name) values('5','e')"

Hope this helps.

Answer 2

User365103270 posted

Hi, i have try and there is still error... the error message is 

Syntax error in INSERT INTO statement.

when it wa executing objCmd.ExecuteNonQuery() command...

please assist me!

thank you so much

Answer 3

User-1941247845 posted

Can you sahre me the CommandText after it has been set. That is debug your code and once you cross the line where you are defining your insert query get the value from the command text and paste it here .. WIll have a look and let you know.

Answer 4

User-1199946673 posted

From and To are Reserved Words in jet. When using Reserved Words as Field name, you need to surround them with brackets:

objCmd.CommandText = "Insert into [Sheet1$] (EmployeeID, DateFiled, Name, Department, Reason, [From], [To], NumberofDays, LeaveApplied, ApprovedBy)"......

Answer 5

User-1199946673 posted

Also when you insert ensure that the data type matches the values that you pass

That is correct, but when the error is  a "Syntax error", this isn't causing the problem.....

Answer 6

User365103270 posted

thanks! problem is solved!