locked
Syntax Error in INSERT statement VB>NET, Access RRS feed

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

    • Moved by Just Karl Friday, May 27, 2016 3:50 PM Looking for the correct forum.
    Friday, May 27, 2016 2:00 PM

Answers

All replies

  • Hello,

    The 'Academic Initiatives - General Discussions' forum is for any posts not covered under any of the other forums as related to Microsoft's Academic Initiatives.

    As it's off-topic here, I am moving the question to the Where is the forum for... forum.

    Karl


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer.
    My Blog: Unlock PowerShell
    My Book: Windows PowerShell 2.0 Bible
    My E-mail: -join('6D73646E5F6B61726C406F75746C6F6F6B2E636F6D'-split'(?<=\G.{2})'|%{if($_){[char][int]"0x$_"}})

    Friday, May 27, 2016 3:49 PM
  • Hello,

    You might ask in the Visual Basic .Net Language forum or the Access for Developers forum on MSDN.

    Karl


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer.
    My Blog: Unlock PowerShell
    My Book: Windows PowerShell 2.0 Bible
    My E-mail: -join('6D73646E5F6B61726C406F75746C6F6F6B2E636F6D'-split'(?<=\G.{2})'|%{if($_){[char][int]"0x$_"}})

    Friday, May 27, 2016 3:50 PM
  • I found the problem. I had to put brackets around the table name: [Transaction], and it worked!!!

    Thank you.

    Friday, May 27, 2016 4:53 PM