none
VB.NET error when inserting data to Ms Database. RRS feed

  • Question

  • I am using the following code to delete data from a table in my MS access database and insert new data.
    My code works until the Delete statement. The Insert statement fails with message "Syntax error in INSERT INTO statement."
    If I run the query direct in MS Access query design, it works fine. However, the .NET code gives the error message for the exact same SQL statement

    Dim sConnAcc As String = "Provider=Microsoft.JET.OLEDB.4.0; Data Source=" & "C:\Catalogue\Catalog.mdb"
    Dim connAccess As New OleDbConnection(sConnAcc)
    Dim cmdAccess As New OleDbCommand
    
    If connAccess.State = ConnectionState.Closed Then
       connAccess.Open()
    End If
    
    sSql = "Delete from [Catalog]"
    cmdAccess = New System.Data.OleDb.OleDbCommand(sSql, connAccess)
    cmdAccess.ExecuteScalar()
    
    sSql = "Insert into Catalog ([Group2]) Values('1')"
    cmdAccess = New System.Data.OleDb.OleDbCommand(sSql, connAccess)
    cmdAccess.ExecuteScalar()



    • Edited by dper77 Thursday, June 15, 2017 6:53 PM
    Thursday, June 15, 2017 6:49 PM

Answers

  • I found the issue. In my insert statement square brackets are missing from the Catalog table

    sSql = "Insert into [Catalog] ([Group2]) Values('1')"
    cmdAccess = New System.Data.OleDb.OleDbCommand(sSql, connAccess)
    cmdAccess.ExecuteScalar()

    • Proposed as answer by Chenchen LiModerator Friday, June 16, 2017 1:28 AM
    • Marked as answer by dper77 Friday, July 7, 2017 3:51 PM
    Thursday, June 15, 2017 7:04 PM

All replies

  • I found the issue. In my insert statement square brackets are missing from the Catalog table

    sSql = "Insert into [Catalog] ([Group2]) Values('1')"
    cmdAccess = New System.Data.OleDb.OleDbCommand(sSql, connAccess)
    cmdAccess.ExecuteScalar()

    • Proposed as answer by Chenchen LiModerator Friday, June 16, 2017 1:28 AM
    • Marked as answer by dper77 Friday, July 7, 2017 3:51 PM
    Thursday, June 15, 2017 7:04 PM
  • Hello,

    We are glad that you have figured it out, I suggest you mark it as answer to close this thread. Thanks for your understanding.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 16, 2017 1:28 AM
    Moderator