[RESOLVED] [2005] TableAdapter with Joined tables- Changes not captured

[MechEng]

Hi all. I have been playing around with a TableAdapter that has two joined tables and one added column with the expression parameter set. A Bindingsource is used to connect this TableAdapter to a DataGridView. Everything displays correctly and as expected.

I have generated an OleDB UPDATE/DELETE/INSERT command that is called (as shown below) to update one of the joined tables. When the code runs, feedback through msgboxes indicate that there are NO changes being made to the Datatable (TableAdapter). I have verified that the data input through the DataGridView is being written to the DataTable.

So my question is: Will a TableAdapter set up like this capture when changes are made or is something special needed to capture changes? If yes, is there something that I am missing in the UPDATE code? The code runs without throwing any errors. My connections is to an ACCESS DB.

    Private Sub btn_SaveWeightData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_SaveWeightData.Click

        Me.Validate()

        TblWeightDataBindingSource.EndEdit()
        Call UpdateWeightTable()

    End Sub

Public Sub UpdateWeightTable()

        Dim StrCon As String = Connectionstring() 'Get connection string
        Dim connection As OleDb.OleDbConnection = New OleDb.OleDbConnection(StrCon)
        Dim dt As DataTable = frmMainScoreBoard.ScoreboardDataSet.tbl_WeightData

        Dim dataAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter()
        dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

        Dim parameter As OleDb.OleDbParameter
        Dim command As OleDb.OleDbCommand


        'Generate Insertcommand statement
        command = New OleDb.OleDbCommand("INSERT INTO tbl_WeightData (StudentID, LastWeighinDate, LastWeighinWeight) " & _
        "VALUES (@StudentID, @LastWeighinDate, @LastWeighinWeight)", connection)

        command.Parameters.Add("@StudentID", OleDb.OleDbType.VarChar, 200, "StudentID")
        command.Parameters.Add("@LastWeighinDate", OleDb.OleDbType.VarChar, 200, "LastWeighinDate")
        command.Parameters.Add("@LastWeighinWeight", OleDb.OleDbType.SmallInt, 5, "LastWeighinWeight")
        dataAdapter.InsertCommand = command

        ' Create the DeleteCommand
        command = New OleDb.OleDbCommand("DELETE * FROM tbl_WeightData WHERE Index = @Index", connection)
        parameter = command.Parameters.Add("@Index", OleDb.OleDbType.Integer, 3, "Index")
        parameter.SourceVersion = DataRowVersion.Original
        dataAdapter.DeleteCommand = command


        'Generate the first UpdateCommand        Dim updateSQL As String = "UPDATE tbl_WeightData SET " & _
        "StudentID = @StudentID, " & _
        "LastWeighinDate = @LastWeighinDate, " & _
        "LastWeighinWeight = @LastWeighinWeight WHERE Index = @Index"

        command = New OleDb.OleDbCommand(updateSQL, connection)

        command.Parameters.Add("@StudentID", OleDb.OleDbType.VarChar, 200, "StudentID")
        command.Parameters.Add("@LastWeighinDate", OleDb.OleDbType.VarChar, 200, "LastWeighinDate")
        command.Parameters.Add("@LastWeighinWeight", OleDb.OleDbType.SmallInt, 5, "LastWeighinWeight")

        parameter = command.Parameters.Add("@Index", OleDb.OleDbType.Integer, 3, "Index")
        parameter.SourceVersion = DataRowVersion.Current
        dataAdapter.UpdateCommand = command

        'Update Tbl_MatchData in database
        dataAdapter.AcceptChangesDuringUpdate = False

        If dt.GetChanges() Is Nothing Then  'Check to see if there are any changes to the dt before update            MessageBox.Show("Before Green UPdate-There are no changes to save.")
        Else
            MessageBox.Show("Before Green UPdate The are changes to be saved.")
        End If

        Dim returnvalue As Integer = dataAdapter.Update(dt)

        If dt.GetChanges() Is Nothing Then   'Check to see if there are any changes to the dt after update            MessageBox.Show("After Green UPdate-There are no changes to save.")
        Else
            MessageBox.Show("After Green UPdate The are " & returnvalue & " changes to be saved.")
        End If

        dt.AcceptChanges()
        connection.Close()
        dt.Dispose()

    End Sub

[jmcilhinney]

So what is the value of 'returnvalue'?

[jmcilhinney]

Also, the code you've posted has absolutely nothing to do with any TableAdapters.

[MechEng]

ReturnValue is 0.

My code is for a DataAdapter. The TableAdapter is what I used to join the two tables... Is not not correctly worded?

[jmcilhinney]

And dt.GetChanges is Nothing or not, before and after you call Update?

If you've got a TableAdapter then I don't understand why you're using DataAdapters. You should be configuring your TableAdapter to do all of this.

[MechEng]

Yes, dt.GetChanges is NOTHING before and after the UPDATE call.

I have been trying to modify the INSERT/UPDATE/DELETE commands without much success (still trying).

[jmcilhinney]

Well if the GetChanges method of your DataTable returns Nothing then it's got nothing to do with your DataAdapter at all. If the DataTable doesn't contain any changes then no DataAdapter or TableAdapter can possibly save any changes. You have to be updating the same DataTable that you have bound to your controls and you have to have made some changes to that table. You obviously haven't.

Also, why are you disposing the DataTable after trying to save the changes? You haven't finished with that DataTable. It's still part of your DataSet. You have finished with your DataAdapter and Connection but I notice you're not disposing them.

[MechEng]

You are right... I found the problem. I still do not understand the creation of DataSets and how they are “attached to forms”, but the error in the UPDATE code where the DataTable is dimensioned… it was pointing to the wrong form.

        Dim dt As DataTable = frmMainScoreBoard.ScoreboardDataSet.tbl_WeightData

Should have been 

        Dim dt As DataTable = frmWeightData.ScoreboardDataSet.tbl_WeightData

Regarding the disposing of the DataTable… I did this because I thought I was through with it, being that it was created for the purpose of updating the database- does it not get created every time this sub is run? For this situation, what should be disposed and would be the correct order of disposing of DataTables, DataAdapters, Connections, etc?

[jmcilhinney]

There's not much to understand. For each control and component you add to your form at design time, the IDE generates code to create the object at run time. When the form is created at run time all that code is executed, thus creating all the controls and components.

If you add a DataSet to your form at design time then that DataSet is created along with the form at run time. You have one form containing one DataSet containing all the DataTables. If you then dispose one of the DataTables in that DataSet then it is destroyed. Where in your code are you creating it again? You're not. You're simply getting a reference to the same DataTable object each time. Once it's destroyed it's destroyed.

The connection and the adapter are a different story though. Notice how your code creates a New OleDbConnection and a New OleDbDataAdapter each time you execute that method? Where are you creating a New DataTable?

[MechEng]

Is it not correct to say the the DataTable (dt) which I believe is a copy of the my table "tbl_WeightData" is not created until the UPDATE code is run... Or does that object go away automatically when the subroutine completes?

Dim dt As DataTable = frmMainScoreBoard.ScoreboardDataSet.tbl_WeightData

[jmcilhinney]

The DataTable already exists. You're just getting a reference to it and assigning it to the 'dt' variable.

Think about this. Let's say I have a bunch of furniture and I move in with a new room mate. One item of furniture I have is a table. When I move in I put my table in the dining room. My room mate points to the table and says "that is my dining table". Did my room mate just create a new table? Of course not. They just referred to an existing table. That's exactly what you're doing in code. Now, would I be happy if my room mate decided to destroy their dining table because they didn't want to use it any more? I think not.

[MechEng]

Ok... I get it. I thought that I was disposing of the reference variable (dt) to the DataTable, not the DataTable itself.

I have revised the UPDATE code with :

        dt.AcceptChanges()
        connection.Close()
        connection.Dispose()
        dataAdapter.Dispose()

Is that a little closer to correct?


Thanks once again for the Explaination.

[jmcilhinney]

That would do it. That said, it still doesn't explain why your DataTable has no changes in it. Is this table supposedly bound to some control(s)? Are you sure it is the very same DataTable object? Are you making any changes via the UI?

[MechEng]

The UPDATE code is working (about 4/5 post earlier). The problem with the update command was the reference to a different (wrong) Form Name. My DataGridView was connected to a DataTable (through a BindingSource) with the correct DataTable name and it was working… I could see the data and write/read from the DataTable. But when it comes to updating the Database, reference to the DataTable is Form specific. I do not understand exactly why this is, but I know that it works now.

Thanks for your help with this.

        Dim dt As DataTable = frmMainScoreBoard.ScoreboardDataSet.tbl_WeightData

Should have been: 

        Dim dt As DataTable = frmWeightData.ScoreboardDataSet.tbl_WeightData

[jmcilhinney]

Oh yeah... I remember now.