0

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
4
Contributors
10
Replies
56
Views
3 Years
Discussion Span
Last Post by Ancient Dragon
Featured Replies
  • The statement simply states "syntax error in INSERT INTO statement" I got (bought) this [eBook](http://www.homeandlearn.co.uk/NET/vbNet.html) 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() … Read More

  • 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 … Read More

0

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

Edited by Begginnerdev

0

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

0

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.

0

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
0

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

 da.Update(ds, "TableName")
1

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.

Edited by Ancient Dragon

Comments
You've got to love those non-descript error messages.
1

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.

Comments
Awesome!
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.