Last post Jun 13, 2014 05:29 AM by jingjie28
Jun 10, 2014 03:45 AM|jingjie28|LINK
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?
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)
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')"
Jun 10, 2014 03:57 AM|vikineese|LINK
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.
sql = "Insert into [Sheet1$] (id,name) values('5','e')"
Hope this helps.
Jun 10, 2014 04:02 AM|jingjie28|LINK
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
Jun 10, 2014 04:09 AM|vikineese|LINK
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.
Jun 10, 2014 08:19 AM|hans_v|LINK
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)"......
Jun 10, 2014 08:20 AM|hans_v|LINK
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.....
Jun 13, 2014 05:29 AM|jingjie28|LINK
thanks! problem is solved!