update datasource from dataset

[paulhudson]

Using the script below I create an editable datagrid. In edit mode all changes are successfully made to the dataset and are displayed on reloading page.

The problem occurs when I attempt to write changes back to the datasource using the update method on the last line of the subroutine Update_team:

oDataAdapter.Update(tpDataSet, "tbTeamPlayer")

Error:

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

Now as I understand I can use the Fill method in order to refresh the dataset before using the update. When I put this as first line in the sub update_team the changes are lost. This is the line I added:

oDataAdapter.Fill(tpDataSet, "tbTeamPlayer")

I am doing something wrong but I cannot figure it out. The msdn pages have made it no clearer for me!

    Public oConnect As New OleDbConnection(ConfigurationSettings.AppSettings("connString"))
      Public oDataAdapter As New OleDbDataAdapter("spTeamByMatchDataID", oConnect)
    Public UpdateCMD As OleDbCommand = New OleDbCommand("UpdateTeamPlayer", oConnect)
    Public InsertCMD As OleDbCommand = New OleDbCommand("InsertTeamPlayer", oConnect)
    Public DeleteCMD As OleDbCommand = New OleDbCommand("DeleteTeamPlayer", oConnect)
    Public PrimaryKeyColumns(0) As DataColumn

SUB Page_load
    oDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
    oDataAdapter.SelectCommand.Parameters.Add("@ParamID", MatchDataID)
    oDataAdapter.UpdateCommand = UpdateCMD
    oDataAdapter.InsertCommand = InsertCMD
    oDataAdapter.DeleteCommand = DeleteCMD    

    If Not Page.IsPostback Then
        BindData
    Else
        tpDataSet = Session("tpDataSet")
        dtTeamPlayers = Session("dtTeamPlayers")
        PrimaryKeyColumns(0)= dtTeamPlayers.Columns("TeamPlayerID")
        dtTeamPlayers.PrimaryKey = PrimaryKeyColumns
    End If
End Sub

Public sub BindData
    tpDataSet = New Dataset() 
    oDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    Dim dtTeamPlayers As DataTable = new DataTable("tbTeamPlayer")
    oDataAdapter.Fill(tpDataSet, "tbTeamPlayer")
    Dim PrimaryKeyColumns(0) As DataColumn
    PrimaryKeyColumns(0)= dtTeamPlayers.Columns("TeamPlayerID")
    dtTeamPlayers.PrimaryKey = PrimaryKeyColumns
    dgr1.DataSource = tpDataSet.Tables("tbTeamPlayer")
    dgr1.DataBind()
    Session("tpDataSet") = tpDataSet
    Session("dtTeamPlayers") = tpDataSet.Tables("tbTeamPlayer")
End Sub


Sub Update_team
    Dim CurrModRow As DataRow
    Dim ModifiedRow() As DataRow = dtTeamPlayers.Select(Nothing,Nothing,DataViewRowState.ModifiedCurrent)
    If Not (ModifiedRow.Length < 1 ) Then
    
    oDataAdapter.UpdateCommand.Parameters.Add("@TeamPlayerID", "TeamPlayerID")
    oDataAdapter.UpdateCommand.Parameters.Add("@PlayerID", "PlayerID")
    oDataAdapter.UpdateCommand.Parameters.Add("@TPositionID", "TPositionID")
    oDataAdapter.UpdateCommand.Parameters.Add("@PNumber", "PNumber")
    oDataAdapter.UpdateCommand.Parameters.Add("@MatchDataID", "MatchDataID")
    oDataAdapter.UpdateCommand.Parameters.Add("@Goals", "Goals")

    End If
    Dim CurrNewRow As DataRow
    Dim NewRow() As DataRow = dtTeamPlayers.Select(Nothing,Nothing,DataViewRowState.Added)
    If Not (NewRow.Length < 1 ) Then
    
    oDataAdapter.InsertCommand.Parameters.Add("@TeamPlayerID", OleDbType.Integer, "TeamPlayerID")
    oDataAdapter.InsertCommand.Parameters.Add("@PlayerID", OleDbType.Integer, "PlayerID")
    oDataAdapter.InsertCommand.Parameters.Add("@TPositionID", OleDbType.Integer, "TPositionID")
    oDataAdapter.InsertCommand.Parameters.Add("@PNumber", OleDbType.Integer, "PNumber")
    oDataAdapter.InsertCommand.Parameters.Add("@MatchDataID", OleDbType.Integer, "MatchDataID")
    oDataAdapter.InsertCommand.Parameters.Add("@Goals", OleDbType.Integer, "Goals")
    
    End If
    Dim CurrDelRow As DataRow
    Dim DeletedRow() As DataRow = dtTeamPlayers.Select(Nothing,Nothing,DataViewRowState.Deleted)
    If Not (DeletedRow.Length < 1 ) Then

    Dim oParam As OleDbParameter = oDataAdapter.DeleteCommand.Parameters.Add("@TeamPlayerId", "TeamPlayerID")
    oParam.SourceVersion = DataRowVersion.Original

    End If
    
    oDataAdapter.Update(tpDataSet, "tbTeamPlayer")
End Sub

Any help would be appreciated. Thanks