Syntax error in INSERT INTO

[Ancient Dragon]

I have the code below in VS 2012. It dynamically allocates a DataGridView and an Update button, then it binds the grid to MS Access database table. That all works well. When I click the Update button btnUpdate_Click() is called, which calls the DataAdapter's Update() method. That produces the syntax error and then displays the sql string for debugging only. Anyone know what I'm missing?

'*********************************************************
'The following are globals at the top of the program
'*********************************************************
Dim con As New OleDb.OleDbConnection
Dim nTries As Integer = 0
Dim binding_source As BindingSource
Dim data_table As DataTable
Dim data_adapter As OleDb.OleDbDataAdapter
Dim cmd_builder As OleDb.OleDbCommandBuilder
'*********************************************************
   Private Sub UsersToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles UsersToolStripMenuItem.Click
        Dim btnUpdate As System.Windows.Forms.Button
        Dim sql As String
        'Dim ds As New DataSet
        Dim dg As System.Windows.Forms.DataGridView = AddControl(New System.Windows.Forms.DataGridView, 40, 40, 345)
        binding_source = New BindingSource()
        data_table = New DataTable()
        sql = "SELECT [LoginName],[Password] FROM [Users]"
        dg.DataSource = binding_source
        data_adapter = New OleDb.OleDbDataAdapter(sql, con)
        cmd_builder = New OleDb.OleDbCommandBuilder(data_adapter)
        data_adapter.Fill(data_table)
        binding_source.DataSource = data_table

        btnUpdate = AddControl(New System.Windows.Forms.Button, 75, 220, Nothing)
        btnUpdate.Text = "Update"
        'btnAdd.AutoSize = True
        AddHandler btnUpdate.Click, AddressOf Me.btnUpdate_Click

    End Sub

'*********************************************************
' Problem is here
'*********************************************************

    Private Sub btnUpdate_Click(sender As Object, e As EventArgs)
        Try
            data_adapter.Update(CType(binding_source.DataSource, DataTable))
        Catch ex As OleDbException
            MsgBox("ERROR:" & ex.Source & " " & ex.Message, MsgBoxStyle.OkOnly)
            MsgBox(cmd_builder.GetInsertCommand().CommandText, MsgBoxStyle.OkOnly)
        End Try
    End Sub

[Begginnerdev]

Have you tried dynamicly creating the update statement?

For example:

   Try         
        'Insert
        data_adapter.UpdateCommand = New OleDbCommandBuilder(data_adapter).GetInsertCommand

        'Update
        data_adapter.UpdateCommand = New OleDbCommandBuilder(data_adapter).GetUpdateCommand

        'Delete
        data_adapter.UpdateCommand = New OleDbCommandBuilder(data_adapter).GetDeleteCommand

        data_adapter.Update(CType(binding_source.DataSource, DataTable))   
   Catch ex As OleDbException
       MsgBox("ERROR:" & ex.Source & " " & ex.Message, MsgBoxStyle.OkOnly)
       MsgBox(cmd_builder.GetInsertCommand().CommandText, MsgBoxStyle.OkOnly)
   End Try

[Reverend Jim]

Judging by Values(?,?) it looks like you created a query with parameters but didn't actually replace the parameters with values.

[Ancient Dragon]

Thanks for the suggestion, but that didn't solve the problem.

[Reverend Jim]

It was a long shot. I'm not well versed in data adapters and such. I prefer direct access.

[Ancient Dragon]

I started to do it manually, but it's just getting much too complicated because of various column types which are set automatically when the DataGridView is bound to database table.

[Begginnerdev]

Im sorry, I would like to edit my previous post.

You set the command type as well:

For example:

'Insert
data_adapter.InsertCommand = New OleDbCommandBuilder(data_adapter).GetInsertCommand

'Update
data_adapter.UpdateCommand = New OleDbCommandBuilder(data_adapter).GetUpdateCommand

'Delete
data_adapter.DeleteCommand = New OleDbCommandBuilder(data_adapter).GetDeleteCommand

[kRod]

When I use a DataAdapter and the CommandBuilder with a BindingSource I call the Update on the DataAdapter as follows.

 da.Update(ds, "TableName")

[Begginnerdev]

What exactly does the exception that is thrown state?

[Ancient Dragon]

The statement simply states "syntax error in INSERT INTO statement"

I got (bought) this eBook which gives a complete example, and the example ALMOST works, it retrieves all the data into the Grid, and attempts to update the database. After adding a row to th Grid, the program calls DataAdapter.Update() and it's feturn value (1) seems to be correct, there are no exceptions thrown, but when I check the table it has not been changed.

[Ancient Dragon]

Solved the problem. The tutorial added the database file as a resource. When the program starts vb.net copies the database file from the resource into the destination director, in my case it is \Documents folder. I created a new project but did not add the file as a resource. Everything works as expected now.