TableAdapter won't update

Posted on 2012-09-20
Last Modified: 2012-09-21
I have a VB.Net 4.0 project in VS 2010
I have a data connection using the Database Explorer, connecting to an Access 2003 database
I have a query or "View" I added as a datagrid to my form.  The query contains a join.
I have two problems:

First issue:
Originally the BindingNavigator that also was added to the form, had the "save" feature disabled.  I then enabled it, and added the following code:

Private Sub NoBannersBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
End Sub

This code never triggers when the button is clicked.  The code is inside the Form.vb module.  How do you properly reference this button?

Second issue
To get around the button problem, I just added a button to the form and put the update code in that, but my table adapter update method triggers an error

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)Handles Button3.Click
        'Save grid edits
        Me.TableAdapterManager.UpdateAll(Me.AutomationDataSet) 'Error line
End Sub

TableAdapterManager contains no connection information. Set each TableAdapterManager TableAdapter property to a valid TableAdapter instance.

Another work around would be how to issue a SQL update command like DoCmd.runSQL for 4.0?  Do I need to add a reference, or what is the 4.0 syntax?

Thanks in advance!
Question by:shawnkimble
    LVL 83

    Accepted Solution

    First Issue,
    You need to add Handles clause to the sub.

    Second Issue,
    You need to initialize the manager. Problem is that your Select query has a join so the adapter does not generate update, delete, insert statements. You have to create those manually.

    Check example here

    Author Comment

    Here is the finished code.  I couldn't figure out how to make use of SqlConnection, so I redid it as an OleDb connection.  Thanks again!

     Private Sub NoBannersBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NoBannersBindingNavigatorSaveItem.Click

            Dim connetionString As String
            Dim connection As OleDbConnection
            Dim oledbAdapter As New OleDbDataAdapter
            Dim sql As String
            connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""C:\data.mdb"""
            connection = New OleDbConnection(connetionString)
            sql = "UPDATE NoBanners SET NoBanners.Location = " _
                & Chr(34) & "Updated Field" & Chr(34) & " WHERE (((NoBanners.FileName)=" & Chr(34) & "File.jpg" & _
                Chr(34) & ") AND ((NoBanners.Field)=" & Chr(34) & "Date" & Chr(34) & "));"
                oledbAdapter.UpdateCommand = connection.CreateCommand
                oledbAdapter.UpdateCommand.CommandText = sql
                MsgBox("Row(s) Updated !! ")
            Catch ex As Exception
            End Try
        End Sub

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now