locked
Error in Insert SQL statement for VB.net RRS feed

  • 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()

    Tuesday, June 10, 2014 3:45 AM

Answers

  • 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)"......

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 10, 2014 8:19 AM

All replies

  • 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.

    Tuesday, June 10, 2014 3:57 AM
  • 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

    Tuesday, June 10, 2014 4:02 AM
  • 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.

    Tuesday, June 10, 2014 4:09 AM
  • 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)"......

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 10, 2014 8:19 AM
  • 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.....

    Tuesday, June 10, 2014 8:20 AM
  • User365103270 posted

    thanks! problem is solved!

    Friday, June 13, 2014 5:29 AM