Using CommandBuilder to update an Access table from a DataGridView

Discussion in 'Microsoft ADO .NET' started by Scott H., Sep 24, 2009.

  1. Scott H.

    Scott H. Guest

    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.
     
    Scott H., Sep 24, 2009
    #1
    1. Advertisements

  2. Scott H.

    Scott M. Guest

    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." <> wrote in message
    news:...
    > 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.
     
    Scott M., Sep 25, 2009
    #2
    1. Advertisements

  3. Scott H.

    Scott H. Guest

    Re: Using CommandBuilder to update an Access table from a DataGrid

    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." <> wrote in message
    > news:...
    > > 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.

    >
    >
    >
     
    Scott H., Sep 25, 2009
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. george baburanos

    CommandBuilder & Update

    george baburanos, Oct 19, 2003, in forum: Microsoft ADO .NET
    Replies:
    1
    Views:
    626
    William Ryan
    Oct 19, 2003
  2. george baburanos

    CommandBuilder and Update

    george baburanos, Oct 19, 2003, in forum: Microsoft ADO .NET
    Replies:
    0
    Views:
    307
    george baburanos
    Oct 19, 2003
  3. Alan Baljeu
    Replies:
    1
    Views:
    232
    David Browne
    Nov 26, 2003
  4. Mark
    Replies:
    1
    Views:
    451
    William Ryan eMVP
    May 12, 2004
  5. Guest

    CommandBuilder InsertCommand Update

    Guest, Dec 29, 2004, in forum: Microsoft ADO .NET
    Replies:
    4
    Views:
    846
    W.G. Ryan eMVP
    Dec 29, 2004
Loading...

Share This Page