Hi all.

I have a question about the DbDataAdapter Update Method. I am using the code below to updata a ACCESS 2003 database. I am doing this because of the number of columns in the database. The INSERT command works fine, but the UPDATE command will only update the current bindingsource row position (current). The effect is, I can INSERT a row and change some data then INSERT another row and change some data there. At this point, I run the code below to UPDATE the database and only the second row's data is passed to the MS database. None of the first rows data is saved.

Do I need to loop through the datatable somehow to get all the changes, or do I have a bigger issue to deal with?

Thanks for your help.


Code:
  Dim StrCon As String = Connectionstring() 'Get connection string
        ' Console.WriteLine(StrCon)

        Dim connection As OleDb.OleDbConnection = New OleDb.OleDbConnection(StrCon)

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

        'Generate Insertcommand statement for tbl_GreenMatchData
        command = New OleDb.OleDbCommand("INSERT INTO tbl_MatchData (GameID) " & "VALUES (@GameID)", connection)
        command.Parameters.Add("@GameID", OleDb.OleDbType.VarChar, 200, "GameID")
        dataAdapter.InsertCommand = command

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

        'Generate the first UpdateCommand for tbl_MatchData
        Dim updateSQL As String = "UPDATE tbl_MatchData SET " & _
        "GameID = @GameID, " & _
        "GWrestlerID = @GWrestlerID, " & _
        "GWeight = @GWeight, " & _
        "GTD1 = @GTD1, " & _
        .
        .
        .
       "GreenStallIndex = @GreenStallIndex, " & _
        "MatchScoreLocked = @MatchScoreLocked WHERE MatchNumber = @MatchNumber"

        command = New OleDb.OleDbCommand(updateSQL, connection)

        command.Parameters.Add("@GameID", OleDb.OleDbType.VarChar, 200, "GameID")
        command.Parameters.Add("@GWrestlerID", OleDb.OleDbType.VarChar, 200, "GWrestlerID")
        command.Parameters.Add("@GWeight", OleDb.OleDbType.SmallInt, 5, "GWeight")
        command.Parameters.Add("@GTD1", OleDb.OleDbType.Integer, 3, "GTD1")
       .
       .
       .
        command.Parameters.Add("@GreenStallIndex", OleDb.OleDbType.Integer, 3, "GreenStallIndex")
        command.Parameters.Add("@MatchScoreLocked", OleDb.OleDbType.Boolean, 1, "MatchScoreLocked")

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

        'Update Tbl_MatchData in database
        dataAdapter.AcceptChangesDuringUpdate = False

        Dim returnvalue As Integer = dataAdapter.Update(dt)


        ‘****************************************
        'Generate the second UpdateCommand for the balance of data to tbl_MatchData
        updateSQL = "UPDATE tbl_MatchData SET " & _
        "RWrestlerID = @RWrestlerID, " & _
        "RWeight = @RWeight, " & _
        "RTD1 = @RTD1, " & _
        .
        .
        .
        "GRideoutpts = @GRideoutpts, " & _
        "RRideoutpts = @RRideoutpts WHERE MatchNumber = @MatchNumber"

        command = New OleDb.OleDbCommand(updateSQL, connection)

        command.Parameters.Add("@RWrestlerID", OleDb.OleDbType.VarChar, 200, "RWrestlerID")
        command.Parameters.Add("@RWeight", OleDb.OleDbType.SmallInt, 5, "RWeight")
        command.Parameters.Add("@RTD1", OleDb.OleDbType.Integer, 3, "RTD1")
        .
        .
        .  
        command.Parameters.Add("@GRideoutpts", OleDb.OleDbType.VarChar, 10, "GRideoutpts")
        command.Parameters.Add("@RRideoutpts", OleDb.OleDbType.VarChar, 10, "RRideoutpts")

        parameter = command.Parameters.Add("@MatchNumber", OleDb.OleDbType.Integer, 3, "MatchNumber")

        parameter.SourceVersion = DataRowVersion.Current
        dataAdapter.UpdateCommand = command

        'Update Tbl_RedMatchData in database
        dataAdapter.AcceptChangesDuringUpdate = False

        returnvalue = dataAdapter.Update(dt)

        dt.AcceptChanges()
        connection.Close()
        dt.Dispose()
        Console.WriteLine("Update command exected")