Member Avatar for Keidi

I am trying to populate an MS Access table using VB.Net through a connection string everything else seems to work except when I try to add a new row.
I get this error:
OleDbException was unhandled
Syntax error in INSERT INTO statement
Here is the code

[Dim MaxRows As Integer
    Dim con As New OleDb.OleDbConnection
    Dim sql As String
    Dim ds As New DataSet
    Dim inc As Integer
    Dim da As OleDb.OleDbDataAdapter
'the add Button code:
Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim NewRow As DataRow

        NewRow = ds.Tables("a").NewRow()

        NewRow.Item(0) = ""
        NewRow.Item(1) = ""
        NewRow.Item(2) = ""
        NewRow.Item(3) = Date.Today
        'Dim contnue As Boolean

        ds.Tables("a").Rows.Add(NewRow)
        da.Update(ds, "a")
  • 2 Contributors
  • 2 Replies
  • 189 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by Keidi

Dani AI

Generated

The OleDbException "Syntax error in INSERT INTO statement" means the SQL sent to Access is malformed. Common root causes in scenarios like this are: the DataAdapter/CommandBuilder generating bad SQL because the SelectCommand or schema is wrong, a field name collides with an Access reserved word or contains spaces/special characters, or an AutoNumber primary key is being included in the INSERT. As noted, a TableAdapter/typed DataSet is the easiest long-term fix, but a few targeted checks will usually resolve the immediate problem.

Checklist (quick diagnostics and fixes)

  • Verify the OleDbDataAdapter is fully initialised and its SelectCommand selects from a single table and includes the primary key. CommandBuilder needs that to generate correct DML.
  • Instantiate the OleDbCommandBuilder only after the DataAdapter is set up and the DataTable schema is available. Inspect the generated SQL with cb.GetInsertCommand().CommandText to see the exact INSERT that Access is receiving.
  • Watch for reserved words or field names with spaces/special characters (examples: Date, Name, User). Either rename fields or ensure they are bracketed with square brackets in SQL.
  • Do not include AutoNumber (identity) columns in the INSERT; let the database generate them or mark the DataColumn as AutoIncrement so CommandBuilder omits it.
  • If CommandBuilder output still fails, assign an explicit parameterised InsertCommand to the DataAdapter (OleDb uses ? placeholders and parameter order matters).

Example of a safe manual InsertCommand (VB.NET)

Dim insertSql As String = "INSERT INTO MyTable ([ColA],[ColB],[TheDate]) VALUES (?,?,?)"
Dim insertCmd As New OleDb.OleDbCommand(insertSql, con)
insertCmd.Parameters.Add(New OleDb.OleDbParameter("ColA", OleDb.OleDbType.VarChar, 100, "ColA"))
insertCmd.Parameters.Add(New OleDb.OleDbParameter("ColB", OleDb.OleDbType.VarChar, 100, "ColB"))
insertCmd.Parameters.Add(New OleDb.OleDbParameter("TheDate", OleDb.OleDbType.Date, 0, "TheDate"))
da.InsertCommand = insertCmd

Inspecting the actual INSERT text produced by the CommandBuilder and testing that SQL in Access usually reveals the offending column or syntax.

Recommended Answers

All 2 Replies

Member Avatar for sknake

Please use code tags when posting code:

[code=vb.net] ...code here

[/code]

You need to specify the Insert, Update, and Delete queries for the dataset. Use the IDE and "Add -- New Item -- DataSet". Right click and add a table adapter. The wizard will guide you through the steps to connection to your database. After that you will have a typed dataset you can work with in code. What you have will not work for anything other than possibly in-memory data editing.

Member Avatar for Keidi

Will try and see thanks.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.