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?

a37b1576d9a85fb810f68ff57d1ee340

'*********************************************************
'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

Recommended Answers

All 10 Replies

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

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

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

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

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.

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

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

 da.Update(ds, "TableName")

What exactly does the exception that is thrown state?

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.

commented: You've got to love those non-descript error messages. +8

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.

commented: Awesome! +8
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.