none
Syntax Error in INSERT statement VB>NET, Access

    Question

  • Hello,
    I have been trying to do an Insert into an Access database but I am
    getting the error:

    SYNTAX ERROR in INSERT INTO statement.

    I have
    tried everything that I can think of to overcome this error, to no avail. I have
    run the Insert statements that I have tried in Access itself and everything
    works just fine. I also have an INSERT/SELECT statement that also fails
    with

    SYNTAX ERROR in From clause.

    but runs fine in Access. The table has an AutoNumber field. I ran this against an Access 2007 database, then
    recreated the table in a new database in Access 2013, neither worked and got the same error.

    strSql = "INSERT INTO TRANSACTION ([Location], [CoilNo], [HeatNo], [Matsize], [Net], [Gross], [TransDate], [BOL], [PackSlip], [TransType], [Flag], [NewLocation], [CreateDate], [ShipName], [Cost]) VALUES (
    @Location, @CoilNo, @HeatNo, @Matsize, @Net, @Gross,
    @TransDate, @BOL, @PackSlip, @TransType, @Flag, @NewLocation, @CreateDate, @ShipName, @Cost)"
    
    Try
    Using conn As New OleDbConnection(ConnStr)
    conn.Open()
    Using cmd As New OleDbCommand(strSql, conn)
    With cmd
    .Parameters.Clear()
    .Parameters.AddWithValue("@Location", txtLocation.Text)
    .Parameters.AddWithValue("@CoilNo", txtCoilNumber.Text)
    .Parameters.AddWithValue("@HeatNo", txtHeatNumber.Text)
    ........
    OR
    'cmd.Parameters.Add(New OleDbParameter("@Location", Trim(txtLocation.Text)))
    'cmd.Parameters.Add(New OleDbParameter("@CoilNo", Trim(txtCoilNumber.Text)))
    'cmd.Parameters.Add(New OleDbParameter("@HeatNo", Trim(txtHeatNumber.Text)))
    ........
    OR
    '.Parameters.Add(New OleDb.OleDbParameter("@Location", OleDb.OleDbType.VarChar))
    '.Parameters("@Location").Value = txtLocation.Text
    '.Parameters.Add(New OleDb.OleDbParameter("@CoilNo", OleDb.OleDbType.VarChar))
    '.Parameters("@CoilNo").Value = txtCoilNumber.Text
    '.Parameters.Add(New OleDb.OleDbParameter("@HeatNo", OleDb.OleDbType.VarChar))
    '.Parameters("@HeatNo").Value = txtHeatNumber.Text
    ......
    End With
    
    Dim recordsAffected As Integer = cmd.ExecuteNonQuery()
    
    End Using
    End Using
    Catch ex As Exception
    MessageBox.Show(ex.Message, "SaveConsignedData", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try 

    I have also tried a straight INSERT statement without the parameters and that did not work either.

    Thank you and Best Regards!

    Friday, May 27, 2016 2:00 PM

Answers

All replies