PC Review


Reply
Thread Tools Rating: Thread Rating: 1 votes, 1.00 average.

Using CommandBuilder to update an Access table from a DataGridView

 
 
Scott H.
Guest
Posts: n/a
 
      24th Sep 2009
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 H.
 
Reply With Quote
 
 
 
 
Scott M.
Guest
Posts: n/a
 
      25th Sep 2009
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." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 H.



 
Reply With Quote
 
 
 
 
Scott H.
Guest
Posts: n/a
 
      25th Sep 2009
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.



--
Scott H.


"Scott M." wrote:

> 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." <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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 H.

>
>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
commandBuilder Update SQL generation for > 1 table? Mark Microsoft ADO .NET 1 12th May 2004 02:33 AM
anyone using CommandBuilder to generate update logic at runtime? Jason Shohet Microsoft C# .NET 1 7th Jan 2004 11:18 PM
CommandBuilder generating flawed command for single table "select *" Alan Baljeu Microsoft ADO .NET 1 26th Nov 2003 05:42 PM
CommandBuilder & Update george baburanos Microsoft ADO .NET 1 19th Oct 2003 06:59 PM
CommandBuilder and Update george baburanos Microsoft ADO .NET 0 19th Oct 2003 05:44 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:52 PM.