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.

Answer 1

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

 

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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

Answer 6

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.