VB.Net update database table that has auto-increment column

Question

I have vb.net application that loads data from a table in access database. This table has a primary key ID which is an auto-increment column. I update the data table with the following code:

daAdapter.Update(dsOptions.Tables("notes"))

But the following error appears: "Syntax Error in INSERT INTO statement" How can I solve that?

Answer 1

When your table contains Reserved Keywords like DESC and TYPE is necessary to inform the OleDbDataAdapter to use special characters to isolate the column names.

For example. Supposing that you build the adapter in this way

 Using con = new OleDbConnection(.....)
 Using cmd = new OleDbCommand("SELECT * FROM NOTES", con
   con.Open
   daAdapter = new OleDbDataAdapter(cmd)
   Dim builder = new OleDbCommandBuilder(daAdapter)
   builder.QuotePrefix = "["
   builder.QuoteSuffic = "]"
   daAdapter.InsertCommand = builder.GetInsertCommand()
   .... rest of the code that fills the grid

Now when you try to call the method Update for the OleDbDataAdapter called daAdapter the InserCommand text will be something like this

 INSERT INTO [NOTES] ([soccerID], [Type], [Desc]) VALUES (?,?,?)

The presence of the square brackets prevents the syntax error. Notice also that if you update your rows in a different method then you need to have the variable daAdapter declare at the global level