[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

TableAdapter won't update

Posted on 2012-09-20
2
Medium Priority
?
1,218 Views
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)
        Me.Validate()
        Me.NoBannersBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.AutomationDataSet)
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.Validate()
        Me.NoBannersBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.AutomationDataSet) 'Error line
End Sub

Error:
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 VB.net 4.0?  Do I need to add a reference, or what is the 4.0 syntax?

Thanks in advance!
0
Comment
Question by:shawnkimble
2 Comments
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 38420969
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

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.updatecommand.aspx
0
 

Author Comment

by:shawnkimble
ID: 38423873
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) & "));"
        Try
            connection.Open()
            oledbAdapter.UpdateCommand = connection.CreateCommand
            oledbAdapter.UpdateCommand.CommandText = sql
            oledbAdapter.UpdateCommand.ExecuteNonQuery()
            MsgBox("Row(s) Updated !! ")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 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