[RESOLVED] Updating two database tables from one DataTable

[MechEng]

I have a DataTable that uses an inner join to two database tables. I did this to overcome the ACCESS limitation of 99 columns. (Oledb).

I have generated two routines that have the Insert/Delete/Update commands (one is shown below) and they both work fine… by themselves. When the either of the routines is executed, the database is Updated/Inserted/Deleted fine, but when I go to update the other table, there is nothing to update. It is like the DataAdapter for the DataTable was cleared of all changes. I have tried the Transaction Class objects on the Connection (they are in the code below), but that does not keep the DataTable from being cleared of changes. I am pretty sure this is the wrong approach.

Question: Is there a way to get an instance of the DataTable and use that to update the Database for one of the database tables and then get another instance of the DataTable to update the other database table? …Or am totally out in the weeds again?

Thanks for your help.

        Using connection As OleDb.OleDbConnection = New OleDb.OleDbConnection(StrCon)
            connection.Open()

            Dim dt As DataTable = Me.ScoreboardDataSet.tbl_MatchData
            Dim dataAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter()
            Dim parameter As OleDb.OleDbParameter

            Dim myTrans As OleDb.OleDbTransaction = connection.BeginTransaction()
            Dim command As OleDb.OleDbCommand = connection.CreateCommand()

            'Generate Insertcommand statement for tbl_GreenMatchData
            command = New OleDb.OleDbCommand("INSERT INTO tbl_GreenMatchData (GameIndex) " & "VALUES (@GameIndex)", connection)
            command.Transaction = myTrans

            command.Parameters.Add("@GameIndex", OleDb.OleDbType.Integer, 3, "GameIndex")
            command.Parameters.Add("@MatchNumber", OleDb.OleDbType.Integer, 3, "MatchNumber")
            dataAdapter.InsertCommand = command

            ' Create the DeleteCommand for tbl_GreenMatchData
            command = New OleDb.OleDbCommand("DELETE * FROM tbl_GreenMatchData WHERE MatchNumber = @MatchNumber", connection)
            command.Transaction = myTrans

            parameter = command.Parameters.Add("MatchNumber", OleDb.OleDbType.Integer, 3, "@MatchNumber")
            parameter.SourceVersion = DataRowVersion.Original
            dataAdapter.DeleteCommand = command

            'Generate the UpdateCommend for tbl_GreenMatchData
            Dim updateSQL As String = "UPDATE tbl_GreenMatchData SET " & _
            "GameIndex = @GameINdex, " & _
            "GTD1 = @GTD1, " & _
            "GTD2 = @GTD2 " & _
            "MatchScoreLocked = @MatchScoreLocked WHERE MatchNumber = @MatchNumber"

            command = New OleDb.OleDbCommand(updateSQL, connection)
            command.Transaction = myTrans

            command.Parameters.Add("@GameIndex", OleDb.OleDbType.Integer, 3, "GameIndex")
            command.Parameters.Add("@GTD1", OleDb.OleDbType.Integer, 3, "GTD1")
            command.Parameters.Add("@GTD2", OleDb.OleDbType.Integer, 3, "GTD2")

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

            'Update Tbl_MatchData in database

            dataAdapter.Update(dt)

            myTrans.Commit()
            connection.Close()

        End Using

[jmcilhinney]

It is like the DataAdapter for the DataTable was cleared of all changes.

Firstly, it's the DataTable that contains the changes, not the DataAdapter. That said, you are completely correct that after the first Update the changes are cleared, so there are no changes for the second Update to save. When you call Update on a DataAdapter it will implicitly call AcceptChanges on the DataTable when the saving is done. To change this behaviour you have to set its AcceptChangesDuringUpdate property to False. You could do this for the first adapter only, but I'd suggest doing it for both and calling AcceptChanges explicitly yourself after both Updates.

[MechEng]

Thank you for the quick reply. I read in the MSDN lib about the AcceptChangesDuringUpdate property and added that property to the adapter instance in my code. The results are the same. The first adapter still resets the DataTable. What you have said matches exactly the MSDN lib description , which means I have totally missed something very easy. Have I miss located the adapter parameter?

I should add… the two have the two adapter update routines in different private subs, calling each one from an another private sub. When I get the logic straight then I will move all the code into functions and common subs. I have tried combining the two adapter update code segments, but that did not seam to make a difference.

            dataAdapter.UpdateCommand = command

            'Update Tbl_MatchData in database
            dataAdapter.AcceptChangesDuringUpdate = False
            dataAdapter.Update(dt)

[jmcilhinney]

Before AND after you call Update the first time add this code:

If dt.GetChanges() Is Nothing Then
    MessageBox.Show("There are no changes to save.")
End If
    MessageBox.Show("The are changes to be saved.")
End If

and let us know the result.

[MechEng]

Interesting… that is a nice debugging tip…

Anyway… I placed statements before and after each adapter update. Both have the “dataAdapter.AcceptChangesDuringUpdate= False” statement.

        If dt.GetChanges() Is Nothing Then
            MessageBox.Show("Before Red UPdate-There are no changes to save.")
        Else
            MessageBox.Show("Before Red UPdate The are changes to be saved.")
        End If

Results:

Before and after both adapter updates, the message indicated that there are changes to be saved, however, the second adapter did not update the database table. I reversed the order of the adapter updates with the same result. The first adapter updated the database and the second did not.

[jmcilhinney]

You aren't calling AcceptChanges yourself before the second Update are you?

[MechEng]

No. Both sets of adapter update are identical and I am not calling for an AcceptChanges.

I did add the AcceptChanges to the second adapter update (after your question) with no change in the way the adapter update is working.

Here is the last section of the second adapter.

        'Update Tbl_RedMatchData in database
        dataAdapter.AcceptChangesDuringUpdate = False

        If dt.GetChanges() Is Nothing Then
            MessageBox.Show("Before Red UPdate-There are no changes to save.")
        Else
            MessageBox.Show("Before Red UPdate The are changes to be saved.")
        End If


        dataAdapter.Update(dt)

        If dt.GetChanges() Is Nothing Then
            MessageBox.Show("After Red UPdate-There are no changes to save.")
        Else
            MessageBox.Show("After Red UPdate The are changes to be saved.")
        End If

        dt.AcceptChanges() 'This added after your question-  made no change.

    End Sub

More information: When running the adapter updates for the second time (both adapters twice) I get a concurrency error when the last (second) adapter is run for the second time. I assume that is because the second table was not updated with the first pass.

I have tried adding the AcceptChanges to directly to the DataTable and to the instance of the DataTable (dt). No difference.

[jmcilhinney]

I was asking to make sure that you were NOT calling AcceptChanges. The idea is that you set all AcceptChangesDuringUpdate to False, you call Update twice, then you explicitly call AcceptChanges, after all changes have been saved. What values does Update return each time you call it? Note that, as Intellisense and the documentation state, Update is a function, not a procedure, so it will tell you if and how many records it saved.

[MechEng]

Using your idea, I captured the update function for both the first and second adapter update statements. I may have led you astray… The UpdateCommand works fine. The problem is when using the InsertCommand (adding rows). I assumed (out of ignorance I guess) that the AcceptChangesDuringUpdate command stopped the DataTable from updating with Insert/Update/Delete commands.

So, what I have learned is that the problem is with my InsertCommand code. It looks similar to the guidelines from the MSDN lib, but I must have missed something. Both of the adapters InsertCommands look the same and it does not matter which adapter update sub I call first… The first one called Inserts rows to its respective Database Table and there is nothing for the Second adapter to Insert into its database table.

Any suggestions with this new information… (Sorry it took me so long to get it all out… I am learning from this lesson though). Thanks.

Here is my INSERT code (both adapter InsertCommands are similar)

        Dim dataAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter()
        Dim command As OleDb.OleDbCommand
        Dim parameter As OleDb.OleDbParameter
        Dim dt As DataTable = Me.ScoreboardDataSet.tbl_MatchData


        'Generate Insertcommand statement for tbl_RedMatchData
        command = New OleDb.OleDbCommand("INSERT INTO tbl_RedMatchData (GameIndex) " & "VALUES (@GameIndex)", connection)
        command.Parameters.Add("@GameIndex", OleDb.OleDbType.Integer, 3, "GameIndex")
        dataAdapter.InsertCommand = command

Here is the end of the update sub- both adapter update subs are the same

        'Update Tbl_RedMatchData in database
        dataAdapter.AcceptChangesDuringUpdate = False

        If dt.GetChanges() Is Nothing Then
            MessageBox.Show("Before Red UPdate-There are no changes to save.")
        Else
            MessageBox.Show("Before Red UPdate There are changes to be saved.")
        End If


        Dim returnvalue As Integer = dataAdapter.Update(dt)


        If dt.GetChanges() Is Nothing Then
            MessageBox.Show("After Red UPdate-There are no changes to save.")
        Else
            MessageBox.Show("After Red UPdate There are " & returnvalue & " changes to be saved.")
        End If

     End Sub

[jmcilhinney]

Are these InsertCommands inserting data into different tables or the same table?

[MechEng]

I would like for each of the two InsertCommands to insert the new rows into two different database tables. My Dataset has ~160 columns. To overcome the 99 column limitation of ACCESS I have to write to two different tables.... Wait ...Oh Man. Ok, your question may have given me another approach.

Could I execute one adapter UpdateCommand that creates the rows with half the data and then execute a second UpdateCommand to the same database table with the other half of the data? (I really did not want to have two database tables anyway.) Of course now I would have to generate a SelectCommand and fill manually… with similar AcceptChangesDuringFill issues, but this should be manageable.

Regardless- If I wanted to add rows to two different database tables from the same DataSet is there a way to suppress the DataSet AcceptChanges behavior?

By the way… thanks for your help, with patients, while I get a clue.

[MechEng]

Ok… I answered my own questions (I think).

Yes, I can successfully add the rows and add some data with one adapter Update/Insert command and then execute a second adapter Update for the other half of the data. .. All into one ACCESS database datatable - so my problem is now solved. YEA!

Another bonus: (to me)- That while I can not Update or Insert with more than 99 columns of data to an ACCESS datatable, the CommandBuilders GET and FILL work fine with 170 column of data so I did not have to manually generate the SelectCommand code. YEA again!

Jmcilhinney- Thanks for your help... Really! I am very thankful for the support from the pros on this forum.