none
Visual Basic Express 2008 & Access 2007 - Source Database not updating from the Local DataSet via Data Adapter/DataConnection

    Question

  • Only my local dataset updates when adding, deleting or editing records.

    The Source Database is supposed to update on issuing DataAdapter. Update(dataset )

    However, when I check my source database after running the operations it remains the same as the original.

    Could someone please advise why... Thanks.

    Sunday, August 22, 2010 7:47 AM

Answers

All replies

  • Paddy

    read through this thread - there is a link in my post that might help explain:

    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/d5210ae2-62e1-4548-8e62-3517a6cd3230

     

    Sunday, August 22, 2010 8:03 AM
  • Dear jwavila:

    Thank you. I have read through your thread and taken the advise not to bring a copy of the database into the application. I re-wrote the simmple application and kept the original database where it is and everything works fine except a new problem occurs. When trying the add a new record an unhandled exception occurs [Duplicate output destination 'L_Name'] at the highlighted position in the code below. (I have commented out the Try/Catch block in order to pinpoint the error - otherwise the catch statement operates and the add operation is cancelled.)

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
            Dim newrow As System.Data.DataRow
            'Add new row
            newrow = DsPhone1.Phonebook.NewRow

            'Enter data in each field
            'Try
            newrow.Item("L_Name") = InputBox("Enter Last Name")
            newrow.Item("F_Name") = InputBox("Enter First Name")
            newrow.Item("Ext") = InputBox("Enter Ext")
            newrow.Item("Office") = InputBox("Enter Office Code")
            DsPhone1.Phonebook.Rows.Add(newrow)
            daPhonebook.Update(DsPhone1)

            'move to display added record which is now at the end
            currmanager.Position = currmanager.Count - 1
            currmanager.Refresh()
            showposition()

            'Catch ex As Exception

            'If cancel is clicked during input box or or any other errors occur
            'MessageBox.Show("Cancelling add operation")
            'End Try

    What could possibly be the cause of this error?

    Thanks once again and Best Regards - pd

    Tuesday, August 24, 2010 5:19 PM
  • Hi paddy,

     

    Welcome to MSDN forums!

     

    Here are four approaches (Take MS Access database file for example) to make a basic Data Access application (Next, Previous, First, Last, Update, Delete, Insert, Save) in VB.Net for you to check. The approach 4 matches with your scenario.

     

    1) Via DataGridView: Update (Insert/Update/Delete) the data back into a MS Access database from DataGridView.

    Code sample: http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/5980181e-f666-4f0a-ab50-c4ebecf96f02/

     

    Imports System.Data.OleDb
    Public Class Form1
      Dim myDA As OleDbDataAdapter
      Dim myDataSet As DataSet
      Private Sub Form1_Load(ByVal sender As System.Object, _
               ByVal e As System.EventArgs) Handles MyBase.Load
        Dim con As OleDbConnection = New OleDbConnection( _
               "Provider=Microsoft.jet.oledb.4.0;data source=|DataDirectory|\myDB.mdb")
        Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Table1", con)
        con.Open()
        myDA = New OleDbDataAdapter(cmd)
        'Here one CommandBuilder object is required.
        'It will automatically generate DeleteCommand, 
        'UpdateCommand and InsertCommand for DataAdapter object  
        Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)
        myDataSet = New DataSet()
        myDA.Fill(myDataSet, "MyTable")
        DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView
        con.Close()
        con = Nothing
      End Sub
       ' Save data back into database  
      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
    End Class

     

         Additionally, select/click your database file in Solution Explorer -> Properties Pane -> change the "copy to ouput directory" to "copy if newer"

     

    2) Use the Data Wizard with a BindingNavigator control.

    Please check the 11th post in this thread for a detailed walkthrough:

    http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/ff3f953b-da66-4f03-b4e4-981bab7d783b/

     

           Additionally, select/click your database file in Solution Explorer -> Properties Pane -> change the "copy to ouput directory" to "copy if newer" 

     

     

    3) Operate DataSet/DataTable in code

    Please check the 12th and 13th posts in this thread for a detailed code sample:

    http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/ff3f953b-da66-4f03-b4e4-981bab7d783b/

     

     

         4) Execute Select/Insert/Delete/Update T-SQL commands in code

    Code samples for MS Access databases

    http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/87913f28-992d-4705-963b-cb0ffa53d8dd/

     

    Code samples for SQL Server databases

                 http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/89e1067d-16e7-44e8-b12d-d78845bf255f/

     

     

    Best regards,

    Martin Xie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, August 30, 2010 5:58 AM
    Moderator
  • Dear Martin:

    Thank you very much for taking the time and trouble to help out. I shall certainly try out what I could gather from all the four approaches given by you.

    However, this is going to be a slow and painful process of learning... and it will take some days before I could get back to you with a success story...

    Meanwhile, please bear with me... there should be a light at the end of this tunnel...

    Thank you very much once again,

    My best regards,

    Priyantha Dias

    Tuesday, August 31, 2010 3:18 PM
  • Dear Martin:

    I have gone through the solutions pointed out by you an noticed a fundamental difference between my code and the coding in the examples including the one you have given in your reply.

    I do not have the following in my code: Dim OlDbDataAdaptor, DataSet, OleDbCommand, con.open(), con.close() etc.

    Instead the textboxes in my interface are bound to the fields in the database directly from the each textbox's properties window as Properties ---> Databinding ---> Text and then by selecting the required field from the frmName List Instances ---> Data Source created by the data adaptor wizard. [I can see that there is a data connection, a data set and a data adaptor below the form in the design pane]

    The data binding works fine and I can browse through records from the first to last when I run the program.

    The only problem encountered is when adding a new record (and as I have mentioned) at the "dataadaptor.Update(datasetName )" command, where an unhandled exception occurs with the error message "Duplicate Data Output Destination 'Field_Name ".

    I have tried both 'Copy always' and 'Copy if newer' in properties for the dataset.xsd.

    Best Regards - Priyantha Dias

    Wednesday, September 08, 2010 1:04 AM
  • Hi paddy,

    Welcome back.

     

    unhandled exception occurs [Duplicate output destination 'Field_Name']

     

    è  Please check this article for detailed instruction about this error.

    http://office.microsoft.com/en-gb/access-help/HV080760370.aspx

    You tried to execute a query that contains more than one destination field with the same name.

    Possible cause:

    ·     You created an SQL statement that includes an INSERT INTO, SELECT...INTO, or UPDATE statement that lists the specified field name more than once.

    Remove the duplicate fields or alias the destination field names, and try the operation again.

     

    Also check this similar case: 

    http://www.eggheadcafe.com/software/aspnet/29167435/append-query-problem--duplicate-output-destination.aspx


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, September 16, 2010 3:43 PM
    Moderator