[dioliew]

Could anyone please help me with this... i tried to figure it out few hours but i getting no way to solve it.
I think i have sucessfully bind the datatable from the datagridview but just couldn't get it to update to access mdb table.
It showed "Syntax error in Update statement" whenever i press the Button1.
Thank you in advanced.

Below is the code.

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Windows.Forms

Public Class Form1

	Inherits System.Windows.Forms.Form
	Private bindingSource1 As New BindingSource()
	Private dataAdapter As New OleDbDataAdapter()

	Public Shared Sub Main()
		Application.Run(New Form1())
	End Sub

	Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

		Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=LabView.mdb;Persist Security Info=False;Jet OLEDB:Database Password=catherine"
		Dim selectCommand As String
		Dim connection As New OleDbConnection(connectionString)
		selectCommand = "select * from [Account Table] ORDER BY [AutoID]"
		Me.dataAdapter = New OleDbDataAdapter(selectCommand, connection)
		With DataGridView1
			.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
			.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader
			.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader
		End With
		Dim commandBuilder As New OleDbCommandBuilder(Me.dataAdapter)
		Dim table As New DataTable()
		table.Locale = System.Globalization.CultureInfo.InvariantCulture
		Me.dataAdapter.Fill(table)
		Me.bindingSource1.DataSource = table
		Dim data As New DataSet()
		data.Locale = System.Globalization.CultureInfo.InvariantCulture
		DataGridView1.DataSource = Me.bindingSource1
		Me.DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Aqua
		Me.DataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
	End Sub

	Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
		Dim table As New DataTable()
		Me.bindingSource1 = Me.DataGridView1.DataSource
		table = Me.bindingSource1.DataSource
		Me.dataAdapter.Update(table)
	End Sub

	Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
		Me.Close()
	End Sub

End Class

[nmgod]

you make a command builder but you dont change the update command of the dataadapter

get rid of this line
Dim commandBuilder As New OleDbCommandBuilder(Me.dataAdapter)

and on the line before
Me.dataAdapter.Update(table)
put
Dim commandBuilder As New OleDb.OleDbCommandBuilder(dataAdapter)
dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand

This post has been edited by nmgod: 07 November 2009 - 06:03 AM

[dioliew]

nmgod, on 7 Nov, 2009 - 04:52 AM, said:

you make a command builder but you dont change the update command of the dataadapter

get rid of this line
Dim commandBuilder As New OleDbCommandBuilder(Me.dataAdapter)

and on the line before
Me.dataAdapter.Update(table)
put
Dim commandBuilder As New OleDb.OleDbCommandBuilder(dataAdapter)
dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand

Thanks nmgod. You have pointed out i actually didn't change the update command of the dataadapter and building the commandbuilder at the wrong place.
But after i correct these it still prompt me the "Syntax error in UPDATE statement" on the OLEDbException was unhandled on the "dataAdapter.Update(table)" event...

Corrected Code

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Windows.Forms

Public Class Form1

	Inherits System.Windows.Forms.Form
	Private bindingSource1 As New BindingSource()
	Private dataAdapter As New OleDbDataAdapter()

	Public Shared Sub Main()
		Application.Run(New Form1())
	End Sub

	Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

		Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=LabViewDB.mdb;Persist Security Info=False;Jet OLEDB:Database Password=catherine"
		Dim selectCommand As String
		Dim connection As New OleDbConnection(connectionString)
		selectCommand = "select * from [Account Table] ORDER BY [AutoID]"
		Me.dataAdapter = New OleDbDataAdapter(selectCommand, connection)
		With DataGridView1
			.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
			.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader
			.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader
		End With
		'Dim commandBuilder As New OleDbCommandBuilder(Me.dataAdapter)
		Dim table As New DataTable()
		table.Locale = System.Globalization.CultureInfo.InvariantCulture
		Me.dataAdapter.Fill(table)
		Me.bindingSource1.DataSource = table
		Dim data As New DataSet()
		data.Locale = System.Globalization.CultureInfo.InvariantCulture
		DataGridView1.DataSource = Me.bindingSource1
		Me.DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Aqua
		Me.DataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
	End Sub

	Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
		Dim table As New DataTable()
		Me.bindingSource1 = Me.DataGridView1.DataSource
		table = Me.bindingSource1.DataSource
		Dim commandBuilder As New OleDb.OleDbCommandBuilder(dataAdapter)
		dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand
		dataAdapter.Update(table)
	End Sub

	Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
		Me.Close()
	End Sub

End Class

[dioliew]

nmgod, on 7 Nov, 2009 - 04:52 AM, said:

you make a command builder but you dont change the update command of the dataadapter

get rid of this line
Dim commandBuilder As New OleDbCommandBuilder(Me.dataAdapter)

and on the line before
Me.dataAdapter.Update(table)
put
Dim commandBuilder As New OleDb.OleDbCommandBuilder(dataAdapter)
dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand

Gosh.... I mislook something else. Thatz are the Table Names and columns names with space....
Problem solved!

Anyone facing this problem may refer to this msdn post.
http://social.msdn.m...b-c3c2211953e1/