Using CommandBuilder to update an Access table from a DataGridView

[Scott H.]

I'm trying to use an OleDbDataAdapter along with the OleDbCommandBuilder to
update a Microsoft Access table from a DataGridView using vb.NET. THe
DataGridView is populated correctly. If I make a change to the DataGridView
and click Button1 to update the table, the following error is generated:
Syntax error (missing operator) in query expression .....

This works fine when I use the SQL version of the dataadapter and
commanbulder when updating a table in SQL Server. Any ideas as to why this
does not work with Access?

Thanks.

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Handles Me.Load
Dim Cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data

Source=c:\AMCO\SNAP Support Files.mdb")
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM
Default_Settings_Table", Cn)
Cn.Open()
myDA = New OleDbDataAdapter(cmd)
Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)
builder.RefreshSchema()
myDataSet = New DataSet()
myDA.Fill(myDataSet, "MyTable")
DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView
Cn.Close()
Cn = Nothing
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Me.Validate()
Me.myDA.Update(Me.myDataSet.Tables("MyTable"))
Me.myDataSet.AcceptChanges()
End Sub

 

[Scott M.]

CommandBuilders are not well known for producing good commandText in all
cases. You are better off writing the command yourself, but in the case of
a DataAdapter, there is a CommandBuilder built-in. If you give it a proper
SELECT command, it can sometimes extrapolate the others. Let's trim your
code down (not everything you have there is necessary) and see what happens:

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Handles Me.Load
Dim Cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\AMCO\SNAP Support Files.mdb")
Dim myDA = New OleDbDataAdapter("SELECT * FROM
Default_Settings_Table", Cn)
Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)
builder.RefreshSchema()
myDataSet = New DataSet()
myDA.Fill(myDataSet, "Default_Settings_Table")
DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView
DataGridView.DataBind()
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Me.Validate()
Me.myDA.Update()
Me.myDataSet.AcceptChanges()
End Sub

There is no need to open or close your connection when using a DataAdapter,
as it will do this for you automatically. Also, there is no need to set
your variables to Nothing in .NET, as this may actually cause your object to
persist longer than it would have normally.

-Scott

 

[Scott H.]

Scott,
Thanks for your reply. I tried pasting your code into a new application. It
did not like the line DataGridView.DataBind(). It indicated that 'DataBind'
is not a member of 'System.Windows.Forms.DataGridView'.

Also, it did not like the line Me.myDA.Update() It claimed there are too few
parameters. I commented out the databind line and added my dataset to the
update line and I received the error: Object reference not set to an instance
of an object. on the
Me.myDA.Update(myDataSet.Tables("Default_Settings_Table")) line when I press
button1.

I know the data in the dataset is correct because I check it before the
update line executes. It seems like the CommandBuilder is not creating the
update command properly. Users will modify the Access tables (add and remove
columns) so I hesitate to create my own specific update command because every
time the table is modified, the update command will have to be modified as
well. The Access table does have a Primary Key set.

Any other advice would be welcome. I have done this with the SQL data
adapter with SQL Server many times without problems.

Thanks.