OledbDataAdapter insert error

[pmeloy]

I'm calling dataadapter.update() but it says "System.Data.OleDb.OleDbException: 'Syntax error in INSERT INTO statement.'" but the insert statement was generated with an OleDbCommandBuilder so I can't figure out where the error actually is. This is where the dataadapter is built

   Public Function Gettransaction_lines(Optional id As Long = -200) As DataTable
        Dim query As String
        Dim dt As New DataTable
        query = "SELECT * FROM transaction_lines WHERE id=@id"
        Dim conndb = New OleDbConnection(connStr)
        Dim cmd = New OleDbCommand(query, conndb)
        With cmd.Parameters
            .Add("@id", OleDbType.BigInt).Value = id
        End With
        transaction_linesAdapter = New OleDbDataAdapter(query, conndb)
        transaction_linesAdapter.SelectCommand = cmd
        Dim cmdB = New OleDbCommandBuilder(transaction_linesAdapter)
        transaction_linesAdapter.Fill(dt)

        Return dt
    End Function

this is where I get the table

        dtNewtransaction_lines = Gettransaction_lines(-200)

-200 so I end up with the table structure but no actual rows

Then I create newrows with

        transaction_linesNewRow = dtNewtransaction_lines.NewRow

Fill the row with data from use selections (no manual entry so no SQL injection possible) then add back to the table

        dtNewtransaction_lines.Rows.Add(transaction_linesNewRow)

Then call the update that generates the error

transaction_linesAdapter.Update(dtNewtransaction_lines)

I don't see anything that could be wrong so it is probably a misleading error message and I am clueless. Any idea what I should try?

[jmcilhinney]

The issue will be that one or more of your column names are reserved words or else contain spaces or other special characters. Those column names need to be escaped in the SQL code, which won't happen automatically. You have two options. You can write out your column list in your query and escape those columns that need it, then the command builder will follow your lead. Alternatively, you can set the QuotePrefix and QuoteSuffix properties of the command builder and it will escape all the column names. For Access, the values would be an opening bracket and closing bracket respectively.

[jmcilhinney]

By the way, it's generally best to avoid such column names in the first place. Then there can't be an issue.

[pmeloy]

AH that was it! It didn't like the column name "number". I thought it was a syntax error in the INSERT command like "INSERTINTO" or something like that. I remember running into a similar reserved word problem like that before now. From now on if I use a real word on a column I'll add something like in this case I changed "number" to "numitems".

[jmcilhinney]

I wouldn't use "numitems" as a column name. "Count" is generally a good name for a column or the like that contains the count of items. If appropriate, you can qualify that with what's being counted, e.g. "ItemCount".

[pmeloy]

In this case it isn't counting. Its the number of items supplied by the user.

[jmcilhinney]

In this case it isn't counting. Its the number of items supplied by the user.

That's a count. Why do you think that collections have a Count property? It's the number of items in the collection. If the user supplied some items to you, how would you determine how many there were? You'd count them, right? The result of that count is the value you're storing in your database column.